Share via

Import Specific Worksheet from Multiple Workbooks into one Access table

Anonymous
2014-04-09T20:07:36+00:00

I was wondering if anyone knows the VBA code I could use for the following situation:

I have a folder with multiple Excel workbooks all with a similar naming convention (WorkbookApril1.xls, WorkbookApril2.xls, WorkbookApril3.xls etc...). 

Each workbook has many worksheets all with the same name, formatting and column headers.  I need to import one of those sheets (WorksheetMain) from each workbook and drop it in one Access table. 

Any help would be appreciated!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom van Stiphout 40,211 Reputation points MVP Volunteer Moderator
    2014-04-10T03:49:53+00:00

    To import a specific named worksheet, use code like this:

    http://blogs.technet.com/b/heyscriptingguy/archive/2008/01/21/how-can-i-import-multiple-worksheets-into-an-access-database.aspx

    To loop over all files in a folder, use the Dir function. See help file for details.

    Extra points if you check each file for the expected format, and throw an error if not.

    Was this answer helpful?

    0 comments