Excel Import - the second worksheet can't be found

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • OfficeDummy
    New Member
    • Jan 2008
    • 18
    #1

    Excel Import - the second worksheet can't be found

    Hello, everyone!

    I need to import an Excel table consisting of two worksheets named 'ISKosten' and 'ITKosten'. The first worksheet is imported flawlessly.

    However, Access doesn't seem to recognise the second sheet. After this piece of code:

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                            "IT", vrtSelectedItem, False, "ITKosten!A1:I80000"
    I get error '3011': the Microsoft-Jet-Database modul couldn't find the object 'ITKosten$A1:I8 0000'.

    WHY not? Everything works with the first worksheet!

    Any help would be very much appreciated!

    OfficeDummy
    Tags: None
    • Jim Doherty
      Recognized Expert Contributor
      • Aug 2007
      • 897
      #2
      Originally posted by OfficeDummy
      Hello, everyone!

      I need to import an Excel table consisting of two worksheets named 'ISKosten' and 'ITKosten'. The first worksheet is imported flawlessly.

      However, Access doesn't seem to recognise the second sheet. After this piece of code:

      Code:
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten!A1:I80000"
      I get error '3011': the Microsoft-Jet-Database modul couldn't find the object 'ITKosten$A1:I8 0000'.

      WHY not? Everything works with the first worksheet!

      Any help would be very much appreciated!

      OfficeDummy

      leave the first sheet syntax as it is but put place a dollar sign in your syntax when you wish to import the second sheet you need two transferspreads heet commands here remember one for the first sheet and any extra sheets need the dollar denotation for a sheet without the exclamation separator prior to the range

      Code:
      DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten[b]$[/b]A1:I80000"
      Jim :)

        Comment

        • OfficeDummy
          New Member
          • Jan 2008
          • 18
          #3
          Originally posted by Jim Doherty
          leave the first sheet syntax as it is but put place a dollar sign in your syntax when you wish to import the second sheet you need two transferspreads heet commands here remember one for the first sheet and any extra sheets need the dollar denotation for a sheet without the exclamation separator prior to the range

          Code:
          DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten[b]$[/b]A1:I80000"
          Jim :)
          Hi, Jim!

          It still doesn't work. :( I get the same error. I tried changing the range to A1:I2000, but no success.

          Any suggestions?

            Comment

            • Jim Doherty
              Recognized Expert Contributor
              • Aug 2007
              • 897
              #4
              Originally posted by OfficeDummy
              Hi, Jim!

              It still doesn't work. :( I get the same error. I tried changing the range to A1:I2000, but no success.

              Any suggestions?
              ok this works 'flawlessly' on my machine amend the path to the xls file accordingly


              Code:
               
vrtSelectedItem = "D:\ITKosten.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten!A1:I10"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ISKosten!A1:I10"

              Jim :)

                Comment

                • OfficeDummy
                  New Member
                  • Jan 2008
                  • 18
                  #5
                  Originally posted by Jim Doherty
                  ok this works 'flawlessly' on my machine amend the path to the xls file accordingly


                  Code:
                   
vrtSelectedItem = "D:\ITKosten.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ITKosten!A1:I10"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"IT", vrtSelectedItem, False, "ISKosten!A1:I10"

                  Jim :)
                  But I thought vrtSelectedItem is already path direction, because I choose the Excel file from the FileDialog window. And I can't give a fixed path in the code because those tables are created weekly, and have therefore to be imported on a weekly basis...

                  Anyway, everything works miraculously now, even though I haven't changed a single thing neither in the code nor in the Excel map. (????)

                  It'd be still interesting to find out, what the problem was.

                  Best,
                  OfficeDummy

                    Comment

                    • Jim Doherty
                      Recognized Expert Contributor
                      • Aug 2007
                      • 897
                      #6
                      Originally posted by OfficeDummy
                      But I thought vrtSelectedItem is already path direction, because I choose the Excel file from the FileDialog window. And I can't give a fixed path in the code because those tables are created weekly, and have therefore to be imported on a weekly basis...

                      Anyway, everything works miraculously now, even though I haven't changed a single thing neither in the code nor in the Excel map. (????)

                      It'd be still interesting to find out, what the problem was.

                      Best,
                      OfficeDummy
                      Ask Bill Gates :)).. fact is vrtSelected item is a variable having an assigned value (and logically I agree with you) should contain the path to the relevant excel file whether this be hard coded or passed by the filedialog method, provided it identifies the file, this should be sufficient the only variances here are the actual targetted sheet within the file itself as required by the transferspreads heet method

                      Regards

                      Jim :)

                        Comment

                        Previous template Next
                        Working...