Importing csv files Access 2007 error 2455

[tugsy]

I had a module for importing csv files into a Access database. I have since upgraded (or downgraded as it seems) to Office 2007.

Now when I run the macro I get this error:

Run-time error '2455':

You entered an expression that has an invalid reference to the property FileSearch.

Here is my module:

Sub ImportWorkbooks()
    Dim FilesToProcess As Integer
    Dim i As Integer
    Dim ThisEntry
    Const TOP_FOLDER = "D:\calls2008" 'adjust folder name to suit
    
    With Application.FileSearch
        .NewSearch
        .LookIn = TOP_FOLDER
        .SearchSubFolders = True
        .Filename = "*.csv"
        .Execute
        FilesToProcess = .foundfiles.Count
        
        For i = 1 To FilesToProcess
          'import each file
          DoCmd.TransferText acImportDelim, "phone_import_specs", "PhoneData", .foundfiles(i), True
        Next i
    End With
End Sub

Any help would be appreciatated.
Thanks,
Tugsy

 

[tugsy]

I discovered that Application.FileSearch is apparently no longer supported. Google search.

Is there an alternative to do this?

 

[tugsy]

With a little bit of searching I found this:

dim sFile

sFile=Dir(pathToFolder & "\*.*")

while sFile<>""
           listfiles=listfiles & ";" & sfile

       sFile=Dir
wend

and incorporated some of the original code

Sub ImportWorkbooks()
       
    Dim sFile
    Dim foundfiles
sFile = Dir("D:\Shared\calls2008\*.*")

While sFile <> ""
           foundfiles = "D:\Shared\calls2008\" & sFile
        DoCmd.TransferText acImportDelim, "phone_import_specs", "PhoneData", foundfiles, True
       foundfiles = ""
       sFile = Dir

Wend
End Sub

All I have to do is copy the files I want to import into the directory and then run it.

I hope this can help somone else with similar problems.

 

[SydneyGeek]

Thanks Tugsy. There was a thread a while back that covered a few options for FileSearch replacements. Apparently it was dropped by mistake, and will be back in the next version.

Denis

 

[sdwyer72]

Re: Importing files to Access 2007 error 2498

I have a similar problem and tried to apply the changes recommended. I now get a RTE 2498 '...wrong data type for one argument' (the debug highlights the DoCmd line). I got the original code from this forum and am only savvy enough to try and replicate changes - I am no expert by any means...

I have several files in the ...\Loans folder, all of which have a named range called "Debt_Schedule". I want to import all of the information in these named ranges into a table called "Loans".

Any help you can provide that troubleshoots this code is most appreciated:

Function ImportWorkbooks()
Dim sFile
Dim foundfiles

sFile = Dir("T:\Debt Management\Loan Tracking\Loans\*.*")
While sFile <> ""
foundfiles = "T:\Debt Management\Loan Tracking\Loans\" & sFile
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Loans", "Debt_Schedule", foundfiles, True
foundfiles = ""
sFile = Dir
Wend
End Function