Results 1 to 3 of 3

Importing a specific Excel worksheet into Access

  1. #1
    Join Date
    May 2019
    Posts
    43

    Importing a specific Excel worksheet into Access

    Hi,
    I am using this subroutine.

    Sub ImportExcelSpreadsheet()


    Dim Sheetname As String


    Sheetname = "'Contacts1'"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NewImport", "T:/Copy of Yuba County - Master9.xlsm", True, Sheetname


    End Sub

    It can't find a specific worksheet "Contacts1". If I eliminate SheetName from the end of the the DoCmd, it works but it only imports the first worksheet in the workbook.

    I have tried naming a range and tacking that on to the DoCmd and it does work. But what can I do to make it use the worksheet name.

    Thanks

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,240
    Don't use apostrophes but do use $ sign.

    Sheetname = "Contacts1$"

    or

    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "NewImport", "T:/Copy of Yuba County - Master9.xlsm", True, Sheetname & "$"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    May 2019
    Posts
    43
    Hi,
    Sorry about the delay in getting back to you, but that worked great.
    Thanks again for your help.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 09-24-2013, 04:53 AM
  2. Replies: 9
    Last Post: 06-21-2013, 08:22 PM
  3. Replies: 3
    Last Post: 08-22-2012, 06:28 AM
  4. Replies: 6
    Last Post: 11-05-2011, 09:01 AM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums