Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Auto Import Excel Files into Access Table

Posted on 2010-08-12
7
Medium Priority
?
1,070 Views
Last Modified: 2013-11-27
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
0
Comment
Question by:ctownsen80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33423653
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33423702
0
 

Author Comment

by:ctownsen80
ID: 33423730
thanks ... with the one below, how would it be edited to point to one specific range in the excel files? Specifically A2:BA30000?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33423790
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
 

Author Comment

by:ctownsen80
ID: 33424041
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 33424349
you can add this line  after importing the excel file

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

Author Closing Comment

by:ctownsen80
ID: 33425395
PERFECT! THANKS
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question