How to read Excel worksheets through IDEAScript
This script will cycle through all the worksheets in an excel spreadsheet and output the sheets name. This could be the starting point for obtaining the information in any excel worksheet.
Sub Main
Dim excel As Object
Dim oBook As Object
Dim oSheet As Object
Dim i As Integer
Dim count As Integer
Set excel = CreateObject("Excel.Application")
excel.Visible = False
Set oBook = excel.Workbooks.Open("C:\Users\XXXX\Documents\My IDEA Documents\IDEA Projects\XXXXXX\Source Files.ILB\XXXXXX.xlsx")
count = oBook.Sheets.Count
MsgBox count
For i = 1 To count
Set oSheet = oBook.Worksheets.Item(i)
MsgBox oSheet.Name
Set oSheet = Nothing
Next i
oBook.Close (True)
excel.quit
Set oBook = Nothing
Set excel = Nothing
End Sub