importing data from excel

[darrenrd]

is there any way to import several excel files into access without doing it 1 by 1. i am looking at over 1500 excel files i would like to pull into 1 data base? thanks

 

[aaahhhrrggghhhh]

Hi.

You will have to code this one!

Use

Docmd.TransferDatabase

There is plenty of information in the Access help on this subject. If you need more information, please tell me if you are trying to import these 1,500 files into a single table, or separate tables, also, make sure that they are all of the same format.

Let me know if you need further assistance.

Mr Big Dont be small. be BIG

 

[aaahhhrrggghhhh]

Hi.

Actually you are probably better off using docmd.transferspreadsheet

Mr Big Dont be small. be BIG

 

[darrenrd]

they will be pulled into seperate tables big. but i have no clue on how to code. each excel file use the same temp. sheet they are just updated and saved with a name. so were could i find out more on coding? thanks for your reply

 

[NBAbueg]

Try doing this by running macros. First, create one macro with action "transferspreadsheet" per excel spreadsheet.

Second, create another macro with action "RunMacro", and make this one run all the "transferspreadsheet" macros you've created.

 

[cew657]

I came upon this post and thought I would throw my two cents in.

The following code is what I use to import several hundred .CSV files into my database. It will loop through each file in the directory specified and pull in the file according to the extension.

You will need to modify the InputDir to reflect your directory, ImportFile to reflect your file type, and DoCmd.TransferText portion to reflect DoCmd.TransferSpreadsheet.

DoCmd.SetWarnings False

Dim InputDir, ImportFile As String, tblName As String, FinalName As String
Dim InputMsg As String

InputDir = "c:\my documents\ddr backup\"

ImportFile = Dir(InputDir & "\*.csv")

Do While Len(ImportFile) > 0
 tblName = Left(ImportFile, (InStr(1, ImportFile, ".") - 1))
 DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True
    ImportFile = Dir
Loop
MsgBox "Process Complete", vbInformation

DoCmd.SetWarnings True

HTH
cew657

 

[GDGarth]

I've got a database that imports hundreds of small files per day into a large (500,000 line) table. The database stores and analyzes data from several automatic testers. Here's how I do it:

Create a linked table, linking it to a sample file (this assumes your files are all the same format).

Write code that loops through the directory, renames each file to the sample name, run an append query to add it to the database table, then delete the file (or rename it, as you wish).

This works quite well for me - I've had it running for a couple of years, updating once per minute.