?
Solved

Auto Import Excel Files into Access Table

Posted on 2010-08-12
7
Medium Priority
?
1,068 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

801 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