Results 1 to 7 of 7

Get External Data - Creating Multiple Linked Tables Simultaneously

  1. #1
    Bambino86 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Posts
    3

    Get External Data - Creating Multiple Linked Tables Simultaneously

    Hello,



    Is there a code that would create multiple linked tables in access that would loop to all files from a selected directories? I have 50 excel files to link and it is very tedious to do this one by one. All workbooks have the same worksheet name. Sorry newbie here.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,523
    Yes, can certainly use VBA to set links with Excel worksheets using TransferSpreadsheet method and looping through files in a folder.

    Have you done any search?

    Bing: Access VBA link Excel

    Here is one: https://stackoverflow.com/questions/...eets-to-access
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    Bambino86 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Posts
    3
    Thanks. I tried it. Does this code only works for csv? Can it also work for .xlsx or .xlsm as well?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,523
    I have not tested that exact procedure code.

    TransferSpreadsheet certainly works for xlsx. Should also for .xlsm. Give it a try.
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  5. #5
    Bambino86 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2018
    Posts
    3
    I did try it and Im getting an error cannot import files. It seems the code only applicable txt or csv

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    45,523
    I just tested the code. It linked .xlsx files. Maybe the issue is with Access 2007?

    I reduced the code to:

    Code:
    Sub LinkExcel()
    Dim strFile As String
    Dim strPath As String
    strPath = "C:\your path here\"
    'Loop through the folder and link workbooks
    strFile = Dir(strPath & "*.xlsx")
    While strFile <> ""
        DoCmd.TransferSpreadsheet acLink, , strFile, strPath & strFile, True
        strFile = Dir()
    Wend
    End Sub
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  7. #7
    Peter M is offline Advanced Beginner
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2017
    Posts
    60
    Importing Multiple Files - Excel
    Excel files can be imported use the following syntax:
    DoCmd.TransferSpreadsheet [TransferType], [SpreadsheetType], [TableName], [FileName], [HasFieldNames], [Range]

    • Transfer Type: acImport, acExport, or acLink
    • Spreadsheet Type: the version of Excel. Using acSpreadsheetTypeExcel12 seems to work for xlsx files.
    • Table Name: the MS Access table that the data is going into
    • Filename: where the data is coming from
    • HasFieldNames: TRUE if the first row contains field names for each column
    • Range: usually the sheetname e.g. “Sheet1!” with the exclamation (bang) at the end, can also use Named Ranges



    (What error exactly are you getting?)

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

Similar Threads

  1. Replies: 6
    Last Post: 08-17-2014, 05:14 PM
  2. Adding data to an external linked table
    By Toasty in forum Import/Export Data
    Replies: 2
    Last Post: 06-12-2014, 06:10 AM
  3. Replies: 6
    Last Post: 02-21-2014, 03:58 PM
  4. Replies: 3
    Last Post: 02-11-2014, 02:26 PM
  5. Replies: 10
    Last Post: 07-31-2012, 10:02 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
  •  
Tech Forums: Microsoft Office Forums