Solved

Auto Import Excel Files into Access Table

Posted on 2010-08-12
7
1,066 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

705 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