Solved: Import Excel to access using foundfiles

[Scottyboy]

Hi, newbie on board

I'm trying to automate an import routine. My problem is the both the file and folder change Daily/monthly.

the bit i'm really struggling with it how to get acccess to import the result of the subfolder search

Code i have so far...

Sub FindMaxFileandImport()
Dim iSearch As Variant
Dim iCount As Long
Dim max_file As String
max_file = "dailyfile" & Format(Date, "ddmmyy") & ".xls"
Set iSearch = Application.FileSearch
With iSearch
.LookIn = "Mypath"
.SearchSubFolders = True
.Filename = max_file
If .Execute > 0 Then
For iCount = 1 To .FoundFiles.Count
DoCmd.TransferSpreadsheet acImport, 9,"Max_csv_table", ???????, True
Next iCount
Else
MsgBox "The file does not exist."
End If
End With
End Sub

Thanks in advance

Scotty