Access VBA- trying to get data from Excel worksheet, multiple worksheets isse
Posted on 2012-03-13
I am trying to grab data from an Excel file. The Excel file has three worksheets in the workbook.
I've been successful in doing so except in indicating which of the worksheets to pull the data from.
I am interested in the worksheet called 'sum' (see code below).
My code below only wants to select the data from the worksheet that was the 'selected' one when the workbook was last saved!
I'm trying to loop through all worksheets and just grab 'sum' with the for next loop below.
Using Access 2010
Set oXL = CreateObject("Excel.Application")
Set oWbk = oXL.Workbooks.Open(strFileName)
Dim i As Integer, intNumSheets As Integer, intSheet As Integer
'For i = 1 To Sheets.Count
' .Cells(i, 1).Value = Sheets(i).Name
intNumSheets = oWbk.Sheets.Count
For i = 1 To intNumSheets
If oWbk.Sheets(i).Name = "sum" Then
intSheet = i
' MsgBox (i & " - " & oWbk.Sheets(i).Name)
'oWst = oWbk.Worksheets(intSheet)
Set oWst = oXL.Worksheets(2)