Results 1 to 5 of 5
  1. #1
    Pagey is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4

    External Data source Excel multiple worksheets

    Currently I am using an external data source and linking an excel data source by creating a linked table. I have about 30 worksheets in the excel workbook. Is there a way to link multiple worksheets in one go? I am currently doing this one by one.



    Thank you

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    what is the purpose of linking - why not just import the data?

    And what do you do with the data once linked? Normally it would be a one time exercise to create the linked tables

    Only way to do this 'in one go' would be to use vba code. You could look at using the transferspreadsheet function multiple times to create the linked tables - see this link https://docs.microsoft.com/en-us/off...ferspreadsheet. you would just need to change the range.

    Another method is to use a query - but depends on what you are doing. This sql compares data in two worksheets and returns those that are in the first worksheet, but not the second

    Code:
    SELECT XL1.* 
    FROM [sheet1$A:D] AS XL1 LEFT JOIN  [sheet2$A:D] AS XL2 ON XL1.firstname=XL2.firstname IN 'C:\pathtofilename\myxlfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes] WHERE XL2.firstname is null
    as you can see, the sql is much the same as a normal query but with the addition of the IN clause providing the location of the file, the file type etc.

  3. #3
    Pagey is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    Thank you so much for your response I will look over your suggestions.

    The linked workbook is used as a template for files that come from CMS and are imported into a folder. These linked worksheets are then used to pull the new CMS data into access tables where they are then ran against queries, and finally imported into sql server tables/views. Every now and again CMS changes the specs and the linked tables need to be updated.

  4. #4
    Pagey is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    4
    Quote Originally Posted by Ajax View Post
    what is the purpose of linking - why not just import the data?

    And what do you do with the data once linked? Normally it would be a one time exercise to create the linked tables

    Only way to do this 'in one go' would be to use vba code. You could look at using the transferspreadsheet function multiple times to create the linked tables - see this link https://docs.microsoft.com/en-us/off...ferspreadsheet. you would just need to change the range.

    Another method is to use a query - but depends on what you are doing. This sql compares data in two worksheets and returns those that are in the first worksheet, but not the second

    Code:
    SELECT XL1.* 
    FROM [sheet1$A:D] AS XL1 LEFT JOIN  [sheet2$A:D] AS XL2 ON XL1.firstname=XL2.firstname IN 'C:\pathtofilename\myxlfilename.xlsx'[Excel 12.0;HDR=yes;IMEX=1;ACCDB=Yes] WHERE XL2.firstname is null
    as you can see, the sql is much the same as a normal query but with the addition of the IN clause providing the location of the file, the file type etc.

    The vba code you suggested worked great. Thanks!

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    happy to help

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

Similar Threads

  1. Replies: 3
    Last Post: 02-21-2018, 07:32 PM
  2. Replies: 3
    Last Post: 05-27-2016, 12:46 PM
  3. Importing Excel Workbook with multiple Worksheets
    By MTSPEER in forum Programming
    Replies: 4
    Last Post: 04-21-2015, 01:50 PM
  4. Replies: 17
    Last Post: 06-25-2013, 05:22 PM
  5. Replies: 3
    Last Post: 11-02-2009, 04:33 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