Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    40

    Question Unanswered: importing excel tabs ?

    Hi,

    is it possible to import different excel tabs into access ?
    I was trying the docmd.transfe... code but this one only imports the first tab.

    It is not possible for me to modify the excel file.


    thanks in advance.
    Bye Erwin

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    sure it's possible. here's one way:
    Code:
        Dim sourceWS As Excel.Worksheet
        Dim countWS As Integer
        Dim loopWS as integer
    
            .....
          
            countWS = 0
            For Each sourceWS In sourceXL.Worksheets
                countWS = countWS + 1
            Next
                
            For loopWS = 1 To countWS                  'for each worksheet
                Set sourceWS = sourceXL.Worksheets(loopWS)
                     .....
    
                     CellData = sourceWS.Cells(wotRow, wotCol).Value
    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Jun 2004
    Posts
    96
    hi, adding on to Izy's method..
    you can specify the range for transferspreadsheet.

    so you do:

    for each ws in wb.worksheets
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tableName", "C:\temp.xls", True, "A1:Z25"
    next ws

    of coz, you can modify to use 'usedrange' to specify the range to import in each worksheet.

    hope this is what you wanted.

    Cyherus

  4. #4
    Join Date
    Feb 2004
    Posts
    40
    Thanks guys,

    i was trying the two examples but i got the same error :

    The WITH varaiable is not set ( this is a own translation, because i use the dutch version ) , error 91.

    The last code i tried was :

    Dim ws As Excel.Worksheet
    Dim wb As Excel.Workbook


    For Each ws In wb.Worksheets
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, ws, "C:\Documents and Settings\data\afas\Controle ISP14.xls", True

    Next ws

    In this code i also tried to use the excel tab name as table name. Will this work ??

    Thanks in advance.
    Erwin
    Bye Erwin

  5. #5
    Join Date
    Jun 2004
    Posts
    96
    you have to specify the range in order to perform selective import of worksheets. with range omitted, it will default to import only everything from the first worksheet.

    Cyherus

Posting Permissions

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