Link Access Database to Multiple Undefined excel worksheets in A FOLDER

[jwb1012]

Hell, I have a folder at the following link where we keep monthly summary reports of all orders placed - each month a new report for that month's orders is added. Is it possible to create an access database and link it to ALL EXCEL FILES within A FOLDER. As new reports are added to this folder, I would like it to be automatically added to the database without having to go into access and import a new table (this is what I am referring to in the title when I say "Undefined" - i.e. not yet created).

I am new to Access so any support would be very much appreciated.

File Location:
C:\Users\XXXXX\Desktop\Orders

Current files:

1. Orders - Jan 2016
2. Orders - Feb 2016
3. Orders - Mar 2016
4. Orders - Apr 2016
5. Orders - May 2016
6. Orders - June 2016
7. Orders - July 2016
8. Orders - Aug 2016
9. Orders - Sep 2016
10. Orders - Oct 2016
11. Orders - Nov 2016
12. Orders - Dec 2016

Future files:

1. Orders - Jan 2017
2. Orders - Feb 2017
3. Orders - Mar 2017
4. Orders - Apr 2017
5. Orders - May 2017
6. Orders - June 2017
7. Orders - July 2017
8. Orders - Aug 2017
9. Orders - Sep 2017
10. Orders - Oct 2017
11. Orders - Nov 2017
12. Orders - Dec 2017

 

[jwb1012]

Here is what I have so far, but when I run the code nothing happens.

Any thoughts, insight, suggestions?? I would be forever grateful!

Option Explicit
 Function DoImport()
 Dim strPathFile As String, strFile As String, strPath As String
 Dim strTable As String
Dim blnHasFieldNames As BooleanblnHasFieldNames = True
 blnHasFieldNames = True

'Path to the folder that contains the EXCEL files
 strPath = "C:\Users\XXXXX\Desktop\Orders"

 ' Table which the data is imported into
strTable = "Table1"

 Do While Len(strFile) > 0
strPathFile = strPath & strFile
 strTable = Left(strFile, Len(strFile) - 4)
 DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames
 strFile = Dir()
 Loop

 End Function

 

[Micron]

You need to be clear on what it is you need. First you twice say link, then you say import a new table. Whichever it is you need, I'd say the author of those file names does not know that it's just bad form to use special characters or spaces (other than the underscore) anywhere. The likely reason that your code doesn't affect anything is that you don't pass anything to this function as a value for strFile and don't assign it within the function. If you were to step through it, I'd wager that when it executes Do While Len, it jumps to the end. This function also doesn't return anything to anywhere, so it might as well be a sub. I'm guessing you copied this from somewhere, intending to adapt it to your needs, but it's not going to work as is.

First, as noted: link or transfer data? Either way, you're going to need to iterate through all the files in a folder and perform the necessary action, and know what you want to do if this action has been done before with a particular file. Or you do so manually on a monthly basis with Get External Data from the Access ribbon.

 

[xenou]

Personally I'd think it preferable to import (not link) the data. And not have lots of tables - one table.