Solved: Office 2007 and VBA

[waysofolde]

Being stuck between a rock and a hard spot is no place to be at all.

I am trying to get VBA script, that was written for 2003 version of Access to work with 2007.

Unfortunately, it tells me that the Application.Filesearch no longer works.

Here's the script that i am using:

Private Sub cmdGetSource_Click()
Dim count As Integer, rs As Object, foundfile As String, counter As Integer, docname As String, fs, rst As Object
DoCmd.SetWarnings False
With Application.FileSearch
.NewSearch
.LookIn = "C:\BAT Leads\December 2007"
.filename = "*.xls"
If .Execute() > 0 Then
Set fs = CreateObject("Scripting.FileSystemObject")
counter = .FoundFiles.count
For count = 1 To counter
foundfile = .FoundFiles(count)
Me.Path = foundfile
docname = fs.GetFileName(foundfile)
Forms("frmImport").cmdGetSource.StatusBarText = "Transferring Worksheet Data for ." & docname
DoCmd.TransferSpreadsheet acImport, , "tbl_Temp", Me.Path, True
DoCmd.OpenQuery ("tbl_Temp Query")
DoCmd.DeleteObject acTable, "tbl_Temp"
Next count
Else
MsgBox "There were no files found."
End If
End With
DoCmd.SetWarnings True
MsgBox "Finished Copying Data to Table"
Exit Sub

Is there anyone that can help me out with this small dilema?

Please.

Thank you sooo much in advance for all your help.

[matthewspatrick]

Please use the VBA tags; it makes your posts much more readable.

How about something like this:


Dim fso As Object, fld As Object, fil As Object

DoCmd.SetWarnings False

Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.GetFolder("C:\BAT Leads\December 2007")

For Each fil In fld.Files
If fil.Name Like "*.xls" Then
DoCmd.TransferSpreadsheet acImport, , "tbl_Temp", fil.Path, True
End If
Next

DoCmd.SetWarnings True

Set fil = Nothing
Set fld = Nothing
Set fso = Nothing

MsgBox "Done"

[waysofolde]

That works perfectly.

Thank you very much.

I will use the VBA tags from now on.