open & read .xls file

[mwilson]

I need some syntax help (vb.net) in looking at a multiple worksheet excel file. If I dont know how many sheets the file has or what there names are, how can I go into each. What I want to add is "for each sheet in workbook, populate these variables from the cells"

This is what I have so far (for a single-sheet workbook):


Sub ReadReport(ByVal path, ByVal filename)
Dim metercode, metername, testdate, analyst As String

Dim xlApp As New Excel.Application()
Dim books As Excel.Workbooks = xlApp.Workbooks
Dim book As Excel.Workbook = books.Open(path & filename)
Dim sheets As Excel.Sheets = book.Worksheets
Dim sheet As Excel.Worksheet = CType(sheets(1), Excel.Worksheet)

metercode = sheet.Range("BL5").Value
metername = sheet.Range("K7").Value
testdate = sheet.Range("AT5").Value
analyst = sheet.Range("AI9").Value


loaddata(metercode, metername, testdate, analyst)


book.Close(False)
xlApp.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets)
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
System.Runtime.InteropServices.Marshal.ReleaseComObject(books)
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
xlApp = Nothing

End Sub

 

[JABE]

[VB]
Dim j As Integer, currentSheet As Worksheet
For j = 1 To book.Worksheets.Count
currentSheet = book.Worksheets(j)
-Manipulate currentSheet here...
Next j
[/VB]

 

[mwilson]

Thanks for the help, JABE. It worked well.