Auto Import Excel Files into Access Table

I am working on consolidating several Excel sheets into one table in Microsoft Access automatically. All of the the excel sheets exist in a folder located at C:\My Documents\Test_Files\. The file name for all of the excel sheets begin with "Document_Test" proceeded by the date the data was run. For example "Document_Test - 1.1.2010", "Document_Test - 1.15.20", "Document_Test - 2.9.2010" and so forth...

I want to be able to embedd a Visual Basic module in access that will be executed via a macro do pull ALL files beginning with "Document_Test" into one table in Access called "tblDocumentTest". However, the data for each sheet begins on row 2, column A and can go down as far as row 30,000 to Column BA. So the default range to pull from each sheet would be A2:BA30000.

If possible, as an extra, after pulling in the data from each excel sheet, could a new field called "FileName" be populated at the end of the data in the newly created "tblDoumentTest" that indicates the name of the file the data was pulled from. For example, when pulling in information from "Document_test - 1.1.2020", could a field be populated at the end of each row displayed "C:\My Documents\Test_files\Document_Test - 1.1.2010".

I have attached a sample excel sheet to display the data for which I want to pull in from each sheet.


Any takers?
Document-Test-1.1.2010.xls
ctownsen80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
0
ctownsen80Author Commented:
thanks ... with the one below, how would it be edited to point to one specific range in the excel files? Specifically A2:BA30000?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
sub importXl()
dim xlPath as string, xlFile as string

xlPath="C:\Excel Documents\"
xlFile=dir(xlpath & "test*.xls")
while xlfile<>""
 docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel12, "TableX", xlPath & xlFile, True, "SheetName!A2:BA30000"

'name xlpath & xlFile as "C:\Excel Document\Process Request\" & xlFile

xlFile=dir

wend
end sub
0
ctownsen80Author Commented:
Thanks ... thats working fine..last question...if I add a field to the table called "Fieldname", is there a way to update each record to display the file name it came from, as theyre appending? So for eaxample, when file Document_Test-1.1.2010 is imported, the cell in field "Filename" displays the file location its caming from?
0
Rey Obrero (Capricorn1)Commented:
you can add this line  after importing the excel file

currentdb.execute "update tableName set [Fieldname]='" & xlFile & "' where [FieldName] is null"
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ctownsen80Author Commented:
PERFECT! THANKS
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.