Solved

Access VBA- trying to get data from Excel worksheet, multiple worksheets isse

Posted on 2012-03-13
19
392 Views
Last Modified: 2012-06-22
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)
0
Comment
Question by:scbdpm
  • 8
  • 7
  • 4
19 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37716320
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
 

Author Comment

by:scbdpm
ID: 37716373
I want to set the worksheete to 'sum'

this isnt' working:
Set oWst = oXL.Worksheets(2)
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37716411
did you copy and paste the codes i posted above?
0
 

Author Comment

by:scbdpm
ID: 37716461
it's pretty much what I posted.

isn't it?
0
 
LVL 33

Expert Comment

by:Norie
ID: 37716483
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37716509
<it's pretty much what I posted.> definitely NOT.
0
 

Author Comment

by:scbdpm
ID: 37716514
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
 
LVL 33

Expert Comment

by:Norie
ID: 37716549
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
 

Author Comment

by:scbdpm
ID: 37716578
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37716618
so you don't want to try the codes i posted...
0
 

Author Comment

by:scbdpm
ID: 37716642
that is your code but assining intSheet from i in the for loop....
0
 
LVL 33

Expert Comment

by:Norie
ID: 37716661
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
 

Author Comment

by:scbdpm
ID: 37716680
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37716681
<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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 37716701
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
 
LVL 33

Expert Comment

by:Norie
ID: 37716717
How do you know the code isn't working?

PS I've checked them all and they all work.
0
 

Author Comment

by:scbdpm
ID: 37716728
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 37716742
did you see my post at http:#a37716701 ?
0
 

Author Comment

by:scbdpm
ID: 37716753
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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now