Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: howto For Each Worksheet in an XLS

    i have an access tool that harvests data from any number of user defined cell references in any number of .XLS in a user specified path. this is quite handy for sending our questionnaires in XLS and consolidating the returns in A.

    the original tool only considered one worksheet per XLS. i would like to extend this to all sheets.

    the current code goes something like this:

    Code:
    Dim sourceXL As Object
    Set sourceXL = CreateObject("Excel.Application")
    for each .XLS file in the directory
      for each row/col definition
        myData = sourceXL.Application.Cells(wotRow, wotCol).Value
      next row/col
    next .XLS
    which i need to upgrade to:

    Code:
    Dim sourceXL As Object
    Set sourceXL = CreateObject("Excel.Application")
    for each .XLS file in the directory
      for each worksheet in the .XLS    'how to achieve this
        for each row/col definition
          myData = sourceXL.Application.Cells(wotRow, wotCol).Value
        next row/col
      next worksheet
    next .XLS
    any suggestions?

    izy
    currently using SS 2008R2

  2. #2
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    Not sure what objects are available to you when using Access, but in Excel VBA it would be something like:

    for each sht in workbookobject.sheets ' workbookobject = your reference to .xls file
    do your thing
    next sht

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Izy,

    If you're using A2000, you should have Excel object 9 or 10 lib avail, right?

    I have Excel 10, and it allows for the creation of an Excel object and the New keyword to set it up. I find it eaiser to use than just declaring an object and using CreateObject() to define it.

    The following code printed out each worksheet name:

    Code:
    Function EnumerateSheets()
        Dim xlObject As Excel.Application
        Dim xlWB As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
    
        Set xlObject = New Excel.Application
        Set xlWB = xlObject.Workbooks.Open(PathToYourFile)
    
        For Each xlSheet In xlWB.Worksheets
            Debug.Print xlSheet.Name
        Next xlSheet
            
        xlWB.Close (0) ' No Save
        Set xlSheet = Nothing
        Set xlWB = Nothing
        Set xlObject = Nothing
    End Function
    HTH.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    thanks HomerBoo,

    in the end, i settled for a messy hybrid that got the job done this morning in time for the deadline - i'll try to tidy the code up along your lines over the next couple of days.

    dim sourceXL as object 'that was in my original code from 2yrs ago and i just dont recall why!!!!!
    dim sourceWS as excel.workbook
    set sourceXL = CreateObject("Excel.Application")
    for each sourceWS in sourceXL.worksheets
    wsCounter = wsCounter+1
    next

    for aPointer = 1 to wsCounter 'is handy so i can store the ws# in the destination table
    set sourceWS = sourceXL.Worksheets(aPointer)
    cellData = sourceWS.Cells(aRow, aColumn).value
    'etc

    i couldn't get any variations on actuary's theme to work.

    izy
    currently using SS 2008R2

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I found I ran into some problems when using the CreateObject("Excel.Application") (that was the only thing available in A97) when I wanted to do things like save and close or use the object to further define other objects (worksheets, for ex) -?- Maybe I'm dumb . Using the New Excel.Application seemed to make everything easier.

    You store the ordinal number of the WS in a table? Wouldn't this change if someone moved a sheet around, or hid one? Not questioning your methods, you know way more about your data and requirements, just more of a curiousity.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    You store the ordinal number of the WS in a table? Wouldn't this change if someone moved a sheet around, or hid one? Not questioning your methods, you know way more about your data and requirements, just more of a curiousity.
    this is a one-time one-way xl stripper: stuff as many XLS files as you like in a directory, press GO, and out come the access reports.

    the "ordinal number" in the table helps in case some imported data is bad ...makes it simpler to find which sheet was poorly completed (and in which XLS too cos i store the filename).
    if the user monkeys around with the sheet order (or the file name), he can either import the whole lot again or look for the sheet the hard way: his problem; his choice.

    good hint re: a97 ...i don't remember when i did my first xl stripper, maybe it was back in the dark days of a95 or a97 and the CreateObject() is a hangover from there.

    izy
    currently using SS 2008R2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •