How can I automate data gathering from multiple excel files?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 1,000 excel files with survey answers. How do I automate the data
gathering using a macro?
 
I got this from a previous post and have not tried this.
It is code not a macro.

A - 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.


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,

Jim
 
Here is another method

I hope this can help

This creates the tables with the same name as in the
directory C:\testimport\ without the ".xls" without the xls

Private Sub Command0_Click()
Dim filename As String
filename = Dir("c:\testimport\*.*")

Do Until filename = ""
DoCmd.TransferSpreadsheet acImport,
8,Left(filename, InStr(filename, ".") - 1),"c:\testimport\"
& filename, True, ""
filename = Dir
Loop
End Sub


' if you want to import into the same table change
Left(filename, InStr(filename, ".") - 1)
to
"tablename"


Jim
 
Back
Top