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.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
    '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)
scbdpmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try this

Dim i As Integer, intNumSheets As Integer, intSheet As Integer
Dim oXL as object
Set oXL = CreateObject("Excel.Application")
     oXL.Workbooks.Open(strFileName)

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
0
scbdpmAuthor Commented:
I want to set the worksheete to 'sum'

this isnt' working:
Set oWst = oXL.Worksheets(2)
0
Rey Obrero (Capricorn1)Commented:
did you copy and paste the codes i posted above?
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

scbdpmAuthor Commented:
it's pretty much what I posted.

isn't it?
0
NorieAnalyst Assistant Commented:
Try this.

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

Open in new window

0
Rey Obrero (Capricorn1)Commented:
<it's pretty much what I posted.> definitely NOT.
0
scbdpmAuthor Commented:
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...
0
NorieAnalyst Assistant Commented:
Why don't you just use this line from the original code removing the ' amd adding Set?
Set  oWst = oWbk.Worksheets(intSheet)

Open in new window

0
scbdpmAuthor Commented:
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)
0
Rey Obrero (Capricorn1)Commented:
so you don't want to try the codes i posted...
0
scbdpmAuthor Commented:
that is your code but assining intSheet from i in the for loop....
0
NorieAnalyst Assistant Commented:
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?
0
scbdpmAuthor Commented:
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
0
Rey Obrero (Capricorn1)Commented:
<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
0
Rey Obrero (Capricorn1)Commented:
copy this codes SEPARATELY



Dim i As Integer, intNumSheets As Integer, intSheet As Integer
Dim oXL as object
Set oXL = CreateObject("Excel.Application")
     oXL.Workbooks.Open(strFileName)
     oXL.visible=true
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

             end if
      next
end with
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NorieAnalyst Assistant Commented:
How do you know the code isn't working?

PS I've checked them all and they all work.
0
scbdpmAuthor Commented:
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'.
0
Rey Obrero (Capricorn1)Commented:
did you see my post at http:#a37716701 ?
0
scbdpmAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.