Excel to Access

[Al]

I have a major migration job coming. The data that I want to migrate is in
several Excel files. I was wondering if there is a routine that could help me
go through the folder where I have all excel files and open each file go to
the first sheet, import the data into a table in access then go to the next
sheet and import the data into another table in access and so on. Can someone
help? I have over 220 sheets in about 10 workbooks
thanks

 

[Jeff Boyce]

Based on your description, you'd end up with "over 220" tables. The problem
with trying to just import Excel 'sheets into Access tables is that Access
is NOT a spreadsheet.

What works for you in Excel (and what you are fairly limited to) will not
work well in Access, which expects to work with "well-normalized" data.

A more common approach would be to analyze the "entities" and
"relationships" contained in the data currently stored in Excel
spreadsheets, create Access tables to reflect these, then work out an
importing routine that parses the raw data into your final Access tables.

Good Luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Al]

Jeff, I understand that. The purpose of this process is to clean these excel
spread sheets in access first. I can do many things in access that I can not
with excel. I will not have 220 tables in access they will all be reduced to
on table. so please if you know a way let me know. I am using access as a
tool to analyse the data in excel.
Al

 

[Mike Painter]

I have a major migration job coming. The data that I want to migrate
is in several Excel files. I was wondering if there is a routine that
could help me go through the folder where I have all excel files and
open each file go to the first sheet, import the data into a table in
access then go to the next sheet and import the data into another
table in access and so on. Can someone help? I have over 220 sheets
in about 10 workbooks
thanks

If they are all the same you can import one and use that criteria, along
with the DIR() command to loop through the files.

MyPath = "C:\SomeFolderName\*.xls"
' file exists, the first file found is returned.
MyFile = Dir(MyPath)

MyName = Dir(MyPath) ' Retrieve the first entry.
Do While MyName <> "" ' Start the loop.
' Ignore the current directory and the encompassing directory.
If MyName <> "." And MyName <> ".." Then
CurrentFile= "C:\SomeFolderName\" & MyName

'Here you would run DoCmd TransferSpreadsheet

MyName = Dir ' Get next entry.
Loop

 

[Ken Snell [MVP]]

Import Data from All Worksheets in All EXCEL Files in a single Folder into
Separate Tables via TransferSpreadsheet (VBA)
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#ImpAllWkshtsFilesSepTbls

This will get your data into separate tables, and then you'll need to run
queries to move the data to combined tables.

 

[Al]

Ken, this works very nicely, Thank you so much for very helpful site. That is
exactly what I needed.