I have used the attached code for quite some time....I recently migrated to Vista...So I now have an active reference to Microsoft Excel 12.0 Object Library.
Apparently 12.o does not support the old Application.FileSearch method (See line marked /// in attached Snippet). When my code hits this line, I get the following error:
"You entered an expression that has an invalid referece to the property FileSearch"
Is there some other method in 12.0 that will accomplish the same task as the old Applicaiton.Fileearch method??
Dim strFileName As String, strFileName1 As String, sTableName As String, strPath As String
Dim i As Integer
Dim fs As Object
Dim CForm As Form
Dim FileType As String
Dim FilePath As String
Dim TemplateTeam As String
Dim FileRS As ADODB.Recordset
Set db = CurrentDb
Set CForm = Forms![IMPORT_EXPORT (ACTIVITY)]
Set FileRS = New ADODB.Recordset
TemplateTeam = CForm![lstTemplates]
FileType = "Activity_Templates"
FilePath = "SELECT [FILE PATHS].Path FROM [FILE PATHS]WHERE ((([FILE PATHS].Team) = " & Chr(34) & TemplateTeam & Chr(34) & ") AND (([FILE PATHS].Template) = " & Chr(34) & FileType & Chr(34) & ") AND (([FILE PATHS].Item)='Import File Location'));"
FileRS.Open (FilePath), CurrentProject.Connection, adOpenKeyset, adLockOptimistic
///Set fs = Application.FileSearch
db.Execute "DELETE * From [TEMP TO IMPORT SUB DEPT TEMPLATES]" ' Clear out old data
.LookIn = FileRS![Path]
.Filename = "*.xls"
If .Execute > 0 Then '(SortBy:=msoSortByFileName, _
'SortOrder:=msoSortOrderDescending) > 0 Then
For i = 1 To .FoundFiles.Count
strPath = .FoundFiles(i)
strFileName = Dir(strPath)
strFileName1 = Left$([strFileName], InStr(1, [strFileName], ".") - 1)
sTableName = "TEMP TO IMPORT SUB DEPT TEMPLATES"
' MsgBox "There were " & .FoundFiles.Count & _
' " file(s) found. And you want to Import " & strFileName
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"" & sTableName & "", strPath, True, "Import_SubDepartments"
MsgBox "There were no files found."
'***Delete any empty lines that may have come in with 40xx & CP Imports
db.Execute "DELETE [TEMP TO IMPORT SUB DEPT TEMPLATES].[Sub Department], [TEMP TO IMPORT SUB DEPT TEMPLATES].* " & _
"FROM [TEMP TO IMPORT SUB DEPT TEMPLATES]WHERE ((([TEMP TO IMPORT SUB DEPT TEMPLATES].[Sub Department]) Is Null));"