Results 1 to 3 of 3
  1. #1
    mlrucci is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Apr 2018
    Posts
    202

    Transferspreadsheet for specific spreadsheet in workbook

    Good afternoon, I am attempting to import data via excel from a program's backend. The backend automatically creates 2 spreadsheets in a workbook. Each time the excel spreadsheet is created, the name of the spreadsheet is different. I always want to import the second spreadsheet no matter what it has been named. I am using



    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblImport_PS_PRR", vrtSelectedItem, True

    for the transfer. Problem, I cannot give the specific name of the spreadsheet in vba as it is always changing. The location of the spreadsheet does not change. Is there a way to specify importing the second spreadsheet in the workbook without specifying the name of the spreadsheet?

    Any help would be appreciated. Thank you in advance

  2. #2
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You can open the spreadsheet from Access and get the second worksheet's name.
    Use some of the code from here; https://pulseinfomatics.com/listing-excel-workbooks-and-sheets-in-access-vba/
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Link to a specific sheet in Excel workbook
    By Buns1976 in forum Programming
    Replies: 4
    Last Post: 10-15-2021, 03:45 PM
  2. Replies: 1
    Last Post: 05-09-2017, 07:03 PM
  3. ms access vba code to close specific excel workbook
    By jzellman in forum Programming
    Replies: 1
    Last Post: 02-01-2016, 08:13 AM
  4. New workbook/worksheets using Transfer Spreadsheet
    By nyneave in forum Import/Export Data
    Replies: 3
    Last Post: 09-05-2012, 06:44 PM
  5. Replies: 4
    Last Post: 11-09-2011, 08:40 AM

Posting Permissions

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