Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Importing Named Excel worksheets to Access (2000 (9.0.6926 SP-3))

    Hi!

    I am going to be receiving a workbook with a number of worksheet tabs on a weekly basis from a new client. The tab I am interested in will be titled "ADD TO JNL DDMMYY" (Where DDMMYY represent a weekly date). If the tab is arranged at the front of the workbook then the "DoCmd.TransferSpreadsheet " function will import the tab without having to add a range at the end. I have no control as to the order of how the worksheets will be arranged and as such need to be able to select the Tab in question using VB. The command I am using is:-

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ADD TO JNL 070606", "J:HAssocBroomleighDanny Hall" & strFileName, True, "'ADD TO JNL 070606'!A1:R1000"

    If I omit the single quotes around the range then I get a run time error '3129' with an Invalid SQL statement etc message, but with the quotes I get a run time error '3011' with the message "The Microsoft Jet Database Engine could not find the object "ADD TO JNL 070606'$A1:R1000'. Make sure the object exists and that you spelt its name and the path correctly." I don't have a problem with tabs which don't have any spaces between the words.

    Can anyone help here, please?

    Cheers,

    Niven

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Named Excel worksheets to Access (2000 (9.0.6926 SP-3))

    Try
    <code>
    ..., "'ADD TO JNL 070606$'"
    </code>
    i.e. add a $ at the end of the sheet name, and omit the range.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Importing Named Excel worksheets to Access (2000 (9.0.6926 SP-3))

    Top Stuff! Probably me, but I couldn't seem to find anything anywhere that resolved the spaces in Tab names situation.

    Cheers again Hans and are you interested in the world cup?

    Niven <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Importing Named Excel worksheets to Access (2000 (9.0.6926 SP-3))

    > are you interested in the world cup?

    No <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20>

Posting Permissions

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