importing multiple excel files

[Bill West]

I have about 200 excel files with 50 records in each. Is
there a way to import all of them at once, or do I need to
impot each file separately?
....uggh!

 

[PC Datasheet]

If the Excel files have similar names such as MyFile1, MyFile2, MyFile3 or
MyFileA, MyFileB, MyFileC then you can put the import routine in a loop that
increments the file name each iteration of the loop. If there is no commonality
in the file names, ...uggh!

 

[bill west]

Thanks for the quick response. I have file name
commonality. What I don't hae is a programming background.
Where do I build a loop? In VB editor? How do I execute
it. I have a high-tech sales background and have seen a
lot of code, just never written any. Can you give me
simple engish or sample code with instructions on where to
paste it and how to execute it?

I know I ask much...

Thanks

 

[robbinma]

Hi,

The following code should get you started
To get the files use something like the following:

Dim foundFnames As New Collection ' collection to store found
filenames
' dirPath & fileFilters are string variables containing
' the path to search
' filefilter should be self explanatory

With Application.FileSearch
.NewSearch
.LookIn = dirPath
.SearchSubFolders = True
.fileName = fileFilter
.MatchTextExactly = True
End With

' apply the search
' vader possibly store the filenames in a collection
With Application.FileSearch
If .Execute(SortBy:=msoSortbyFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
foundFnames.Add .FoundFiles(i)' store the filenames in
a collection
Next i
Else
MsgBox "There were no files found."
End If
End With

' now process each filename found
' need to open the individual spreadsheets listed above
' sheetRange defines name/range of sheet to import
For Each wbFname In foundFnames
DoCmd.TransferSpreadsheet acImport, 8, importTable, wbFname,
hasFldNamesFlg, sheetRange
next wbFname
' there are various error conditions to handle.
' the transfer spreadsheet is ok (see transferspreadsheet action help
topic) but it does have the odd gotcha.
If you are loading the data into an Access table (as opposed to a
linked table) then you should be ok.
Remember that if you get any errors with individual fields the import
is likely to load the rest of the row and just let you know that there
has been a problem in a seperate ImportErrors table.
If you get data conversion errors then use the tool here to make sure
all your cells are of the same datatype.
http://www.j-walk.com/ss/excel/tips/tip28.htm

Good luck,

Mark