Importing From Excel to Access.... With the Microsoft Excel 12.0 Object Library Reference

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
 
With fs
    .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"
 
   Next i
    Else
        MsgBox "There were no files found."
    End If
End With
 
 
'***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));"
 
 
End Sub

Open in new window

dwood1112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
{wild guess}
>///Set fs = Application.FileSearch
fs = fso = Microsoft Scripting Runtime Library

Go to Tools:References, and set a reference to 'Microsoft Scripting Runtime'.  Ok, save, and try again.

Also, in the below line put a space just to the left of WHERE, as SQL likely wont know what [FILE PATHS]WHERE means.
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'));"
 
0
Rey Obrero (Capricorn1)Commented:
use the old reliable Dir()

dim xlFile as string

xlFile=dir(Path & "\*.xls")

while xlfile<>""
        strPath= Path & "\" & xlfile
       DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
       "" & sTableName & "", strPath, True, "Import_SubDepartments"

    xlfile=dir

wend
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dwood1112Author Commented:
>use the old reliable Dir()

This works....Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.