Skip to main content

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