Automate import of 600+ excel files into access quarterly 1

[ch4meleon]

We have a site checklist for all our premises which are done quarterly and i want to import into access to compare and produce mi from they are stored on a shared drive and titled by their premises id number, in a folder set called "current Qtr", i want to be able to just press a button and import them all each quarter without having to do lots of messing about each time !?!
any help most gratefully received

 

[JerryKlmns]

This shall find all excel files named File_Name in folder Search_Folder

Sub Searching4Excels(ByVal File_Name As String, ByVal Search_Folder As String)

With Application.FileSearch
.NewSearch
.FileType = 4 'msoFileTypeExcelWorkbooks
.Filename = File_Name & "*.xls"
.LookIn = Search_Folder
.SearchSubFolders = False
If .Execute > 0 Then
For iCount = LBound(.FoundFiles) To UBound(.FoundFiles)
Call ImportThisExcel(.FoundFiles(iCount))
Next iCount
Else
MsgBox "No file found in " & Search_Folder , vbCritical + vbOKOnly, "Importing Excels"
End If
End With
End Sub

Then use the TrenasferSpreadsheet method of DoCmd to import apropriately all excels in a sub named ImportThisExcel

Sub ImportThisExcel(ByVal FilePathName As String)

DoCmd.TransferSpreadsheet acImport,acSpreadsheetTypeExcel9, "YourTableNameHere", FilePathName , True

End Sub

Use F1 for help and post back for any questions

 

[ch4meleon]

Thanks Jerry, If i need to put an address for the folder to search in how do i do it ?
e.g.
oop\retail\allsites\retailmi\currentQtr

 

[JerryKlmns]

Files are HAHA1.XLS, HAHA2.XLS, HAHA3.XLS, etc
Folder is C:\ManyExcels

Next line does it

Call Searching4Excels("HAHA", "C:\ManyExcels")

place it on the click event of a button on a form

 

[ch4meleon]

Hi Jerry
ive hit a bit of a prob wondering if you can help -
Im getting an complie error : expected array
on the line -
For icount = Lbound(.foundfiles) to UBound(.FoundFiles)

any idea why ?
huge thanks

 

[RoyVidar]

Try:

[tt]for iCount = 1 to .FoundFiles.Count[/tt]

Roy-Vidar

 

[ch4meleon]

thanks Roy, thats now running throught the code fine, all i have to do now is work out why it isnt actually finding the files in the folder !
will it make a difference that they are all named numerically e.g. 445216.xls ? i have changed the call the macro to -
Searching4Excels("", "C:\correct address for my file")
grateful for any ideas

 

[RoyVidar]

I think I'd just try remove or comment the .FileName line, and have it just look for Excel files (if there are no other excel files there)

If there are other files too, then check whether the file names contain only numbers (InStrRev, Val/IsNumeric functions, toghether with some text manipulating functions, should probably do the trick).

Roy-Vidar

 

[ch4meleon]

thanks for reply, yes the only files in the folder are ones that i want to import so if i comment out
.Filename = File_Name & "*.xls"
presumably i need to remove other refs to file name such as ByVal File_Name As String ??