Loading ...
May 1st, 2008 by 
Yaron Assa
Usually, we only want to read some data from an Excel worksheet, and we’ll be more than happy to get that data in a simple 2-dimensional array.
Later we can loop through the array, and access the data in a quick and easy fashion.
Here’s a function which receives an Excel file, the workbook name and returns its contents as a two dimensional array.
1: Function ReadExcel(sFileName,sSheetName)
2: Dim oExcel
3: Dim oRange
4: Dim arrRange
5:
6: 'Open the file and set the sheet
7: On Error Resume Next
8: Set oExcel = CreateObject("Excel.Application")
9: oExcel.Workbooks.Open(1)
10: Set oRange = oExcel.Worksheets(sSheetName).UsedRange
11:
12: If Err.Number <> 0 Then
13: ReadExcel = Array("Error")
14: Exit Function
15: End If
16: On Error Goto 0
17:
18: 'Cast excel data into a two-dimentional array
19: arrRange = oRange.Value
20:
21: oExcel.WorkBooks.Item(sFileName).Close
22: Set oRange = Nothing
23: oExcel.Quit
24: Set oExcel = Nothing
25:
26: 'return the arrRange and then return to the function
27: ReadExcel = arrRange
28:
29: End Function
Posted in MS-Excel
May 6th, 2008 at 7:09 pm
I’ve applied this function to my library and it works rather well. However, I keep getting a subscript out of range error on line 21 above. Why would trying to close the file result in that error? Anyone have any ideas?
May 6th, 2008 at 7:14 pm
Should line 21 in the original post be this instead?
oExcel.WorkBooks.Close
insetad of
oExcel.WorkBooks.Item(sFileName).Close
May 6th, 2008 at 7:16 pm
Thanks ferdman,
I’ve fixed the function.
July 11th, 2008 at 7:47 pm
I’m not sure if this is the same thing but you don’t need to define an array when getting certain data out of Excel. I did this code to create lots of data in a mainframe GUI application through it’s macro functions, but the code is VBs with a few components of VBA added. The results of the data entry (a client ID for example) is then written back to the Excel file. Similar logic can be used for automated testing. I included an error handling function within the sub. Apologies for putting it in here but I haven’t been able to post a tip. The actual code is many pages long and I have just included the basic components here:
__________________________________________________
[PCOMM SCRIPT HEADER]
LANGUAGE=VBSCRIPT
DESCRIPTION=
[PCOMM SCRIPT SOURCE]
OPTION EXPLICIT
REM This line calls the macro subroutine
GrantVisa
sub GrantVisa()
‘Declaration of Variables needed for Excel
Dim objExcel, ExcelPath, objSheet, intRow, currentWorkSheet , RetVal, MyDate, MyTime
Dim usedRowsCount, row, cells, MyRow, EntryDate, MyVal
‘Declaration of variables from Data Record Sheet
Dim EntryType, VisaClassCode, GrantNumber
‘Declaration of variables required in code for screen column and row position
Dim CurPosCol, CurPosRow
‘Declaration of Error determination
Dim SearchErr, ErrRow, ErrNum
‘Declaration of other variables required
Dim GrantNumChar, MyOverRide
‘Hardcoded Test Data workbook location
ExcelPath = “H:\TestSheet v 5.2.xls”
‘Create Excel Object - Open session of Excel in the background
Set objExcel = CreateObject(”Excel.Application”)
‘Ignore Alerts in Excel
objExcel.DisplayAlerts = 0
‘Open Excel File and set the worksheet to be used
objExcel.Workbooks.open excelPath, true, False
Set currentWorkSheet = objExcel.ActiveWorkbook.Worksheets(”PERSON + TD + VISA”)
‘Establishes the area of data
Set Cells = currentWorksheet.Cells
‘Creates Session
autECLSession.SetConnectionByName(ThisSessionName)
‘Beginning of data entry loop. Note that Do Until Statement is used to better capture the end of the available
‘data.
‘Loop to bypass previously loaded data and to find the first new record in the dataset
row = 5
EntryDate = Cells(row, 3).Value
Do Until EntryDate = “”
row = row + 1
EntryDate = Cells(row, 3).Value
Loop
FamilyName = Cells(row, 8).Value
Do Until FamilyName = “”
EntryType = Left(Cells(row, 4).Value,1)
VisaClassCode = Cells(row, 5).Value
***Additonal Excel extraction code done here****
‘Entry of Client Details
autECLSession.autECLPS.SendKeys (EntryType & VisaClassCode)
***Additional writing to mainframe done here***
‘This determines if there has been an error occur in the processing of the Grant Number
If (autECLSession.autECLPS.WaitForString(”Grant”,3,3,100)) Then
‘Record PID into Excel spreadsheet
RetVal = autECLSession.autECLPS.GetText(3, 65, 6)
Cells(row,60).Value = RetVal
‘Record Visa Grant Number into Excel Spreadsheet
‘Include obtaining Grant Check Character to the Grant Number
RetVal = autECLSession.autECLPS.GetText(3, 9, 13)
Cells(row,59).Value = RetVal & GrantNumChar
‘Record System Date and Time into Excel Spreadsheet
Cells(row,3).Value = MyDate & ” ” & MyTime
‘Save the Spreadsheet after each entry to counter for any unexpected system errors
ObjExcel.ActiveWorkbook.Save
‘This contains the ELSE statement if the word Grant is not found on the screen.
Else
Cells(row,61).Value = “Error(s) Found”
Cells(row,3).Value = MyDate & ” ” & MyTime
ObjExcel.ActiveWorkbook.Save
autECLSession.autECLPS.SendKeys “[pA1]”
End If
row = row + 1
FamilyName = Cells(row, 8).Value
Loop
ObjExcel.ActiveWorkbook.close
set objexcel = nothing
end sub