Read Excel from Vbscript

Excel - VbScript

In order to open the excel from vbscript, you first need to create COM object of Excel then you need to provide the location of your excel file and then finally the name or index of the worksheet.

Once the above details are provided, you can read the content of your Excel.

Here is the code to open the excel from vbscript:

Set ObjExcel = CreateObject("Excel.Application")   'Create COM of excel

Set objWorkbook = ObjExcel.Workbooks.Open("D:\Pulkit.xls")    'Provide the location of your file

objExcel.Application.DisplayAlerts = False   'to suppress any run time error

objExcel.Application.Visible = True      'To display the Excel, if this field is mark as 'false',then user                                                       is unable to see excel file by default objExcel.Application.Visible = 'False'

Set ObjWorkSheet=objWorkbook.Worksheets("Sheet1") 'Name of the worksheet which user want to open

Read content of excel

So for reading the content of excel, we first need to know how many rows(Containing data) and columns(Containing data) present in the excel.

To Get the No of row we used:

RowCount = ObjWorkSheet.UsedRange.rows.count

To Calculate No of columns, We used:

ColCount = ObjWorkSheet.UsedRange.Columns.count

Now to get the values, we need to loop the script from cell (1,1) to cell(RowCount,ColCount)

For i=1 to RowCount  step 1

 For j = 1 to ColCount step 1

  CellValue = objExcel.Cells(i,j).Value

MsgBox "The Cell " & i & "," & j & " having value " &  CellValue

 Next

Next

So for each cell, value is displayed in a msgbox.

Now the last step, free the memory allocated to excel Workbook, Worksheets.

Set ObjExcel =Nothing
Set ObjWorkBk = Nothing
Set ObjWorkSheet = Nothing