Working with Excel

If I ask you to tell about Microsoft Excel. What can you think of? It is an excel workbook which contains sheets, cells, table like structure  where we write data , read data etc.

Same operations, we can perform through coding. We need to create sheet,delete sheet, read data , write data,compare data etc.

As a first point, we need to create an excel object instance using method ‘createobject’. Let’s learn how to perform all these operations.

Creating an Excel workbook and save it at any location :

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Add 	' Add a workbook to the excel instance
oWorkbook.SaveAs "H:\DemoExcel.xls"	'Save it at any location 
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance

Open an existing excel and rename its first sheet:

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Open("H:\DemoExcel.xls")	' open the workbook 
Set oSheet = oWorkbook.Sheets("Sheet1") ' get the first sheet access
oSheet.name = "Demo"
oWorkbook.Save
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance
Set oWorkbook= nothing ' release the objects
Set oExcel = nothing

Open an existing excel and write some data in the renamed sheet:

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Open("H:\DemoExcel.xls")	' open the workbook 
Set oSheet = oWorkbook.Sheets("Demo") ' get the first sheet access
oSheet.Cells(1,1) = "Name"		'cells syntax : cells(row,column)
oSheet.Cells(1,2) = "Shekhar"
oSheet.Cells(2,1) = "City"
oSheet.Cells(2,2) = "Bangalore"
oSheet.Cells(3,1) = "Country"
oSheet.Cells(3,2) = "India"
oWorkbook.Save
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance
Set oWorkbook= nothing ' release the objects
Set oExcel = nothing

Count the no. of rows and columns in excel sheet:

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Open("H:\DemoExcel.xls")  ' open the workbook 
Set oSheet = oWorkbook.Sheets("Demo") ' get the first sheet access
rows = oSheet.usedrange.rows.count
cols = oSheet.usedrange.columns.count
Print "Total rows are "&rows
Print "Total columns  are "&cols
oWorkbook.Save
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance
Set oWorkbook= nothing ' release the objects
Set oExcel = nothing

Read data from excel sheet:

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Open("H:\DemoExcel.xls")	' open the workbook 
Set oSheet = oWorkbook.Sheets("Demo") ' get the first sheet access
rows = oSheet.usedrange.rows.count
cols = oSheet.usedrange.columns.count
For i = 1 to rows	'create a nested loop with rows and columns to read the data
	For j = 1 to cols
	Print oSheet.Cells(i,j)		
	Next	
Next
oWorkbook.Save
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance
Set oWorkbook= nothing ' release the objects
Set oExcel = nothing

Delete the sheet:

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Open("H:\DemoExcel.xls")	' open the workbook 
Set oSheet = oWorkbook.Sheets("Sheet2") ' get the first sheet access
oSheet.Delete
oWorkbook.Save
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance
Set oWorkbook= nothing ' release the objects
Set oExcel = nothing

Compare data between 2 excel sheets:

Set oExcel = CreateObject("Excel.Application")	' create object instance
oExcel.Visible = true  ' making it visible
Set oWorkbook  = oExcel.Workbooks.Add	' Add the workbook 
oWorkbook.SaveAs("H:\DemoExcel1.xls")
Set oSheet1 = oWorkbook.Sheets("Sheet1") ' get the first sheet access
Set oSheet2 = oWorkbook.Sheets("Sheet2") ' get the second sheet access
oSheet1.Cells(1,1) = "Name"		'Write data in first sheet
oSheet1.Cells(1,2) = "Shekhar"
oSheet1.Cells(2,1) = "City"
oSheet1.Cells(2,2) = "Bangalore"
oSheet1.Cells(3,1) = "Country"
oSheet1.Cells(3,2) = "India"
oSheet2.Cells(1,1) = "Name"		'Write data in second sheet
oSheet2.Cells(1,2) = "Madhur"
oSheet2.Cells(2,1) = "City"
oSheet2.Cells(2,2) = "Delhi"
oSheet2.Cells(3,1) = "Country"
oSheet2.Cells(3,2) = "India"
For each cell in oSheet1.usedrange
	'compare the cell valuein second sheet .If not match highlight it
	If cell.value<> oSheet2.Range(cell.Address).value Then
		cell.Interior.ColorIndex = 3 
	End If
Next
oWorkbook.Save
oWorkbook.Close	'Close the workbook
oExcel.Quit 	'Quit the excel instance
Set oWorkbook= nothing ' release the objects
Set oExcel = nothing 

Note:

we should close the workbook and excel instance after use. Otherwise, instance will not be close and on next run program will throw an error that sheet already opened.