Importing from Excel

[ottenbrj]

Anyone know if it is possible to import multiple spreadsheets (workbooks) from Excel into Access. I have hundreds of individual spreadsheete - in the same format - that I would like to convert to a more functional Access database. Any advice could possibly would save me hundreds of hours of work. Please advise....thanks.

Jeff

 

[paron]

The really critical thing to me is the scrupulous, painstaking effort required of mere human beings to keep spreadsheets that are consistent internally, let alone from one to the next, over time. Still, if they really did keep text out of the number columns, comments out of the date columns, etc., then great! VisualBasic talks to Excel and the rest is a walkover.

I once did a tiny little contract job for a University here which converted 300+ spreadsheets to Access(these were Lotus, now that I think of it). I used plain vanilla VisualBasic, as I recall. The tricky part was recognizing the header row or skipping it, but it wasn't that bad. I think I saved the code, but it wasn't very user-friendly, since I was the only user, and once it ran, I only ran it once. A lot of stuff was hard-coded that should really have been event-driven, etc.


Ron

 

[WP]

Two methods:

TransferSpreadsheet method ... see Access help

or

using OLE code to read the spreadsheet.




Bill Paton
william.paton@ubsw.com

http://www.neptune400.co.uk/

Check out

http://www.deepturtle.ch

 

[Guest_imported]

hi there

this is vinod from india i have found a way to import excel data into access,

in access create a form and put a button in to it

in the codebuilder of the button on the button click event

write the following

Dim db As Database
Set db = currentdb

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tablename", "c:\windows\desktop\GIPL.xls", False

End Sub

 

[B827]

If you have hundreds then you'll need an automatic method of getting the file names otherwise you'll still have hours of work. Using WP's method you could try a variation on this theme:
Place all your Excel Files in a folder with no other files. This code uses some arrays to store the names of the files to be imported - I can't remember why I did it that way. This will place all the spreadsheets in a single table which should already exist in the database.

With Application.FileSearch
.LookIn = FileLoc 'Location Of Excel
.SearchSubFolders = False
.FileName = "*.Xls" 'Look for Excel Extension
If .Execute() > 0 Then
J = .FoundFiles.Count
For I = 1 To J
ReDim Preserve Fnames(I)
Fnames(I) = .FoundFiles(I)
Next I
Else
MsgBox "There were no files found."
End If
End With
If J > 0 then
For I = 1 To J
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, "TableName", FNames(I), True, ""

Next I
end if