How can I import 671 excel workbooks into access? 1

[cashe]

HI

I have created a payment an invoice database to take over from excel spreadsheets. The problem is each contractor has a "curent balance" excel workbook. There are 671 work books to import into a temporary table. I only need Sheet1 of each workbook. All the workbooks are named after the contractor and are all stored in the same file.

Is there a way to import them in bulk or do I have to do each one manualy?

Any help or if anyone knows of other resources that I can look at it would be much appreciated.

Thanks

 

[psemianonymous]

There are ways to import them in bulk, but one thing you must do: ensure that each of the 671 follow the *exact* same format.


You can use Automation with Excel to grab very specific data, or you can use DoCmd.TransferSpreadsheet command if the data is in some sort of tabular format (you can select specific ranges too).

You can use repititions of the Dir() function to loop through all files in a specific directory.



I think that's enough to get you started. For each Excel file, import (specific range) of the spreadsheet into the same table, or into a temp table of some sort if you need to do some data manipulation before "officially" importing the data.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[cashe]

Thanks for the reply foolio12,

One problem the files are in the same format with the field names on row 2, however they do not all have the same range and have no unique identifier. I would need to import each into a separate table before manipulating them. The things a mess.

Also this part is new to me is there an example somewhere of this code. I'll look in the help file.

cheers

 

[psemianonymous]

Not too bad. Dir() is pretty well documented, and here's an example you can try (only file extension modified to .XLS):

Private Sub ImportFiles(directory As String)
    Dim currentFile As String
    
    'Make sure we are in the right directory to look for the import files.
    FileSystem.ChDrive Left(directory, 2)
    FileSystem.ChDir directory
    
    
    currentFile = Dir("*.XLS")
    
    Do While currentFile <> &quot;&quot;
        ImportOneFile directory & currentFile
        
        currentFile = Dir()
    Loop
End Sub

As long as the data begins on line 3, I think you'll be fine--just import from 3 to 16000 and you'll be fine (I think? Some large number anyway). Then you can run DELETE queries to remove any whitespace/empty rows.

 

[cashe]

HI Foolio12,

I could be way of the mark here. I'm in new water now. Thanks for the code but I can't seem to get it working (lack of experience) This is what I have:

Private Sub ImportFiles_Click(directory As String)
Dim currentFile As String

'Make sure we are in the right directory to look for the import files.
FileSystem.ChDrive Left(directory, 2)
FileSystem.ChDir directory


currentFile = Dir("C:\P4_Project\spreadsheets\Current Balances\Arron Bott.XLS&quot

Do While currentFile <> &quot;&quot;
ImportOneFile directory & currentFile

currentFile = Dir()
Loop
End Sub


Thanks for ur help

 

[psemianonymous]

That was just an example. ImportOneFile() was another subroutine I wrote--replace that with TransferSpreadsheet or some such. Or write your own ImportOneFile()

 

[psemianonymous]

That was just an example. ImportOneFile() was another subroutine I wrote--replace that with TransferSpreadsheet or some such. Or write your own ImportOneFile()

Also, ImportFiles_Click() looks like an event. You can't pass in the directory via an event, you'll have to pull the directory information from somewhere else. Maybe just hardcode it in, i.e. Dim directory as string = &quot;C:\etc\&quot;

 

[cashe]

Hi foolio12,

I'm getting there. I can now import one workbook to an existing table &quot;tblTest&quot;. How do I get the program to loop through all the *.xls and create a new table for each of the 671 files. The table name should be the name of the workbook.

I can then delete blanks and combime the data etc

Public Function ImportFiles(Directory As String)

Dim currentFile As String

'Make sure we are in the right directory to look for the import files.
FileSystem.ChDrive Left(Directory, 2)
'FileSystem.ChDir Directory

currentFile = Dir(&quot;C:\P4_Project\spreadsheets _
18-7-03\Current Balances\Aaron Bott.XLS&quot

Do While currentFile <> &quot;&quot;
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
&quot;tblTest&quot;, &quot;C:\P4_Project\spreadsheets _
18-7-03\Current Balances\Aaron Bott.XLS&quot;, True, &quot;Balance!&quot;

currentFile = Dir()
Loop
End Function

Thanks

 

[psemianonymous]

Look at the original code I posted - it loops through each XLS file in the directory. It originally calls the subroutine &quot;ImportOneFile()&quot;, which you can basically replace with anything you want. Which you have done. But you have to preserve the original loop, which you have not done.

 

[smitan]

As you have the name of the xls in currentFile, you can use the instrrev function to find the last \. Then use strSprName=mid(...), to find the name of the spreadsheet.
Name the tabel like &quot;tblImport&quot; & strSprName

The TransferSpreadsheet statement is changed accordingly.
Good luck, have a nice summer.
Hans

 

[cashe]

Thanks Smitan,

Is there an example I can look at. I'm not a programmer and am feeling out of depth here but I need to get this done to day. I'm having fun trying but starting to go blind now

Thanks

 

[smitan]

Public Function ImportFiles(Directory As String)

Dim currentFile As String

'Make sure we are in the right directory to look for the import files.
FileSystem.ChDrive Left(Directory, 2)
'FileSystem.ChDir Directory

currentFile = Dir(&quot;C:\P4_Project\spreadsheets _
18-7-03\Current Balances\*.XLS&quot 'CHANGED

Do While currentFile <> &quot;&quot;
i=instrrev(currentfile,&quot;\&quot 'Position of last strSprName=mid(currentfile,i+1,len(currentfile)-i-4) 'Part of filename without .xls
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
&quot;tblImport&quot; & strSprName, currentfile, True, &quot;Balance!&quot;
currentFile = Dir()
Loop
End Function

 

[cashe]

Hi Smitan,

Thanks very much for the help. Well worth a star. I'm getting an error message:

Run-time error 3011

The MS Jet database engine could not find
the object 'C:\Documents and Settings\Mark\My
Documents\Aaron Bott.xls. Make sure the object
exists and that you spell its name and the path name
correctly.

The Path &quot;C:\P4_Project\spreadsheets 18-7-03\Current Balances\*.XLS&quot; is correct but I think its looking in my documents???

The error message occurs at this line in the code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
&quot;tblImport&quot; & strSprName, currentfile, True, &quot;Balance!&quot;

Any ideas?

Thanks again.

 

[smitan]

I am missing the path. Forgot that DIR only gives the filename
So here is the easier corrected version:

Do While currentFile <> &quot;&quot;
strSprName=left(currentfile,len(currentfile)-4) 'Part of filename without .xls
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
&quot;tblImport&quot; & strSprName, &quot;C:\P4_Project\spreadsheets _
18-7-03\Current Balances\&quot; & currentfile, True, &quot;Balance!&quot;
currentFile = Dir()
Loop

Enjoy
Hans

 

[cashe]

Thanks Hans,

That worked great. Time to sign myself up on the next VB course HAHA

And Thankyou Foolio12 for getting me on to the right track

 

[smitan]

Now you will have to use the tables via code as manually is a hard job. Use code like this:

Function UseTables()
Dim dbsLocal As Database
Dim tdf As TableDef

Set dbsLocal = CurrentDb
For Each tdf In dbsLocal.TableDefs
If Left(tdf.Name, 9) = &quot;tblImport&quot; Then
........
Do something per table
........
End If
Next
dbsLocal.Close
Set dbsLocal = Nothing

End Function

You might write to me directly on hvd@activesearch.dk

Have a nice time.
Hans