Import and Excel to Access

[Mallol02]

Hi Everyone,

í'm trying to create a module that verify if the Filename that is created in Excel have a record. IF this filename in Excel doesn't have a record, the module will not transfer any information and it will display a MSG saying that there are no records to transfer. Well. in Access 2003 its works fine but in Access 2007 its not working!!! can some one please help me out on this one.

Thanks in Advance
HERE IS THE CODE.

Function LOADSHEET_LOADACCESS()
On Error GoTo LOADSHEET_LOADACCESS_Err

Set FS = Application.FileSearch
With FS
.LookIn = "S:\DIV_PLN\DIV_SAUX\"
.SearchSubFolders = True
.filename = "MatriculaMerge.xls"
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(i){commented out}
school = .FoundFiles(i)
DoCmd.TransferSpreadsheet acImport, 8, "TABLAMAT2008", school, True, "Sheet2!A130000"
Next i
Else
MsgBox "There were no files found."
End If
End With
LOADSHEET_LOADACCESS_Exit:
Exit Function

LOADSHEET_LOADACCESS_Err:
MsgBox Error$
' Resume LOADSHEET_LOADACCESS_Exit

End Function

 

[Mallol02]

>>>in Access 2003 its works fine but in Access 2007 its not working!!!<<<

I would suggest you create a temporary form, and a command button with the command button wizard that closes the form, save this form and press the command button and let me know what happens.

Hi Uncle Gizmo, But i don't understand what are you trying to explain.
but i get and error "You entered and Expression that has an invalid reference to the Property FileSearch".

 

[boblarson]

Um, sounds like a MISSING Reference error to me. In the VBA window go to Tools > References and see if anything has MISSING listed beside it. Or, you may not have added that reference when trying to do it in 2007 where you had it selected in 2003 so it needs to be checked now too.