scbdpm
asked on
Access VBA- trying to get data from Excel worksheet, multiple worksheets isse
All,
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
HELP!
Set oXL = CreateObject("Excel.Applic ation")
Set oWbk = oXL.Workbooks.Open(strFile Name)
Dim i As Integer, intNumSheets As Integer, intSheet As Integer
'For i = 1 To Sheets.Count
' .Cells(i, 1).Value = Sheets(i).Name
'Next i
intNumSheets = oWbk.Sheets.Count
For i = 1 To intNumSheets
If oWbk.Sheets(i).Name = "sum" Then
intSheet = i
Exit For
End If
' MsgBox (i & " - " & oWbk.Sheets(i).Name)
Next i
'oWst = oWbk.Worksheets(intSheet)
Set oWst = oXL.Worksheets(2)
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
HELP!
Set oXL = CreateObject("Excel.Applic
Set oWbk = oXL.Workbooks.Open(strFile
Dim i As Integer, intNumSheets As Integer, intSheet As Integer
'For i = 1 To Sheets.Count
' .Cells(i, 1).Value = Sheets(i).Name
'Next i
intNumSheets = oWbk.Sheets.Count
For i = 1 To intNumSheets
If oWbk.Sheets(i).Name = "sum" Then
intSheet = i
Exit For
End If
' MsgBox (i & " - " & oWbk.Sheets(i).Name)
Next i
'oWst = oWbk.Worksheets(intSheet)
Set oWst = oXL.Worksheets(2)
ASKER
I want to set the worksheete to 'sum'
this isnt' working:
Set oWst = oXL.Worksheets(2)
this isnt' working:
Set oWst = oXL.Worksheets(2)
did you copy and paste the codes i posted above?
ASKER
it's pretty much what I posted.
isn't it?
isn't it?
Try this.
It will set oWst to the 'sum' worksheet if it's found in the workbook.
It will set oWst to the 'sum' worksheet if it's found in the workbook.
Set oXL = CreateObject("Excel.Application")
Set oWbk = oXL.Workbooks.Open(strFileName)
For Each oWSt In oWbk.Worksheets
If oWSt.Name = "sum" Then Exit For
Next oWSt
' check worksheet has been found
If Not oWSt Is Nothing Then
Msgbox "sum worksheet found"
' code to do stuff with worksheet
Msgbox oWSt.Range("A1").Value
Else
Msgbox "sum worksheet not found"
End If
' close workbook
oXLWbk.Close False
oXL.Quit
Set oXL = Nothing
<it's pretty much what I posted.> definitely NOT.
ASKER
nope, still grabbing the sheet that was saved as in focus (turns out its the third).
I can't imagine this makes a difference but this is a password protected workbook...
I can't imagine this makes a difference but this is a password protected workbook...
Why don't you just use this line from the original code removing the ' amd adding Set?
Set oWst = oWbk.Worksheets(intSheet)
ASKER
I used the following and it didn't work:
i = 1
For Each oWst In oWbk.Worksheets
If oWst.Name = "sum" Then
intSheet = i
Exit For
End If
i = i + 1
Next oWst
' check worksheet has been found
If Not oWst Is Nothing Then
MsgBox "sum worksheet found"
' code to do stuff with worksheet
Set oWst = oWbk.Worksheets(intSheet)
i = 1
For Each oWst In oWbk.Worksheets
If oWst.Name = "sum" Then
intSheet = i
Exit For
End If
i = i + 1
Next oWst
' check worksheet has been found
If Not oWst Is Nothing Then
MsgBox "sum worksheet found"
' code to do stuff with worksheet
Set oWst = oWbk.Worksheets(intSheet)
so you don't want to try the codes i posted...
ASKER
that is your code but assining intSheet from i in the for loop....
capricorn's code will work.
The orginal code will work, if you make the change I suggested.
The code I posted should work too.
If none of the code is working can you post explaining how it isn't working?
The orginal code will work, if you make the change I suggested.
The code I posted should work too.
If none of the code is working can you post explaining how it isn't working?
ASKER
perhaps I'm missing somethign but this isn't working for me:
<nope, still grabbing the sheet that was saved as "in focus" (turns out its the third).>
code is not grabbing the 'sum' worksheet
<nope, still grabbing the sheet that was saved as "in focus" (turns out its the third).>
code is not grabbing the 'sum' worksheet
<that is your code but assining intSheet from i in the for loop....> that is NOT my codes .please do NOT insists and mixing your codes with what I posted..
just let us know what you want to do
just let us know what you want to do
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How do you know the code isn't working?
PS I've checked them all and they all work.
PS I've checked them all and they all work.
ASKER
sory guys... not sure who to make this more clear......
I need to take data in a worksheet that is called 'sum'
the code I posted in the original question will take the information not from the 'sum' worksheet but whatever worksheet was 'out front' (sorry dont know the technical term) when the workbook was last saved.
I need to get the worksheet 'sum'.
I need to take data in a worksheet that is called 'sum'
the code I posted in the original question will take the information not from the 'sum' worksheet but whatever worksheet was 'out front' (sorry dont know the technical term) when the workbook was last saved.
I need to get the worksheet 'sum'.
did you see my post at http:#a37716701 ?
ASKER
capricorn1:
yes but I had to add the 'Set' line.
see below:
With oXL
For i = 1 To .Worksheets.Count
If .Worksheets(i).Name = "sum" Then
MsgBox "Found worksheet Sum"
' what do you want to do ?
'place codes here
.Worksheets(i).Select
Set oWst = oWbk.Worksheets(i)
End If
Next
End With
yes but I had to add the 'Set' line.
see below:
With oXL
For i = 1 To .Worksheets.Count
If .Worksheets(i).Name = "sum" Then
MsgBox "Found worksheet Sum"
' what do you want to do ?
'place codes here
.Worksheets(i).Select
Set oWst = oWbk.Worksheets(i)
End If
Next
End With
Dim i As Integer, intNumSheets As Integer, intSheet As Integer
Dim oXL as object
Set oXL = CreateObject("Excel.Applic
oXL.Workbooks.Open(strFile
with oXL
For i= 1 to .worksheets.count
if .worksheets(i).name="sum" then
msgbox "Found worksheet Sum"
' what do you want to do ?
'place codes here
end if
next
end with
oXL.quit