Solved

Auto Import Excel Files into Access Table

Posted on 2010-08-12
7
1,063 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
  • 4
  • 3
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 33423653
0
 
LVL 119

Expert Comment

by:Rey Obrero
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero earned 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now