Searching Multiple Excel files 1

[OneMadCoder]

I need to search (100 or so) multiple excel files for a specific field, all contained within one directory. Is there a way to easily do so without opening each one then selecting each tab to search.

If I could append them all that would be fine too...

I pulled this from the web, but encountered errors:
I've replaced the variables with my own

Sub find()
Dim myfile
Dim mypath
mypath = "C:\Users\Chris\Desktop\xls\As Run xls files"
Do
myfile = Dir(mypath & "*.xls")
' Search and Find
DoCmd.TransferSpreadsheet acImport, 8, "RSPF", mypath & myfile
myfile = Dir
Loop Until myfile = ""
End Sub

 

[PHV]

What about this ?

mypath = "C:\Users\Chris\Desktop\xls\As Run xls files\"
myfile = Dir(mypath & "*.xls")
Do Until myfile = ""
  DoCmd.TransferSpreadsheet acImport, 8, "RSPF", mypath & myfile
  myfile = Dir
Loop

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[OneMadCoder]

I'll try it, thanks for the FAST response

 

[OneMadCoder]

No, same as before
Debug message is: Identifier under cursor is not recognized (acImport)

mypath = "C:\Users\Chris\Desktop\xls\As Run xls files\"
myfile = Dir(mypath & "*.xls")
Do Until myfile = ""
DoCmd.TransferSpreadsheet acImport, 8, "RSPF", mypath & myfile
myfile = Dir
Loop

 

[PHV]

Seems you automate msacces, so replace this:
DoCmd.TransferSpreadsheet acImport, 8, "RSPF", mypath & myfile
with this:
[!]yourAccessApplicationObject.[/!]DoCmd.TransferSpreadsheet 0, 8, "RSPF", mypath & myfile

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886