importing *.xls into access 1

[splats]

I have a folder (BeforeImport) that has only one .xls file in it at a time. I would like to get the code to work to import this file, then move this file to another folder (AfterImport) and rename it. It did work fine when I put in a specific file name. However, i would like it to work with any file (*.xls) that is in the folder. Each file that is in the (BeforeImport) folder is named specifically by date so that we can track the accuracy of the data.

below is my code. It is not recognizing the file name that is given. Any suggestions would be greatly appreciated. Thanks

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stExport1 As String
Dim stExport2 As String
Dim SourceFile, DestinationFile
stExport1 = "*.xls"
stExport2 = "HRIS to Safety Export " & Format(Now(), "YYYYMMDD") & " UPDATED.xls"
SourceFile = "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\BeforeImport\" & stExport1 ' Define source file name."
DestinationFile = "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\AfterImport\" & stExport2 ' Define target file name."

'DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel2000, "ImportData", "C:\Documents and Settings\CharleneF\My Documents\Test1.xls", False
DoCmd.TransferSpreadsheet acImport, acspreadsheetTypeExcel2000, "ImportData", SourceFile, False
DoCmd.OpenQuery "QuEmployeeImport", acNormal, acEdit
'DoCmd.OpenQuery "QuInsertEmployee", acNormal, acEdit
'DoCmd.OpenQuery "QuUpdateEmployee", acNormal, acEdit

DoCmd.RunSQL "delete * from ImportData"

'DoCmd.DeleteObject acTable, "ImportData"

'FileCopy ("C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\Test211.xls", "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\AfterImport\"HRIS to Safety Export YYYYMMDD UPDATED.xls")

FileCopy SourceFile, DestinationFile ' Copy source to target.
If Dir(SourceFile) <> "" Then Kill (SourceFile)

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

 

[JerryKlmns]

This line shall give you the name of the only one excel you have

ThePath = "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\"

SourceFile= ThePath & "BeforeImport\" & Dir(ThePath & "BeforeImport\*.xls")

and this line will do the renaming

Name SourceFile As DestinationFile

 

[splats]

Thank you JerryKlmns. It works great! Below is the code for any other folks having a similar need.

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stExport1 As String
Dim stExport2 As String
Dim SourceFile, DestinationFile
stExport1 = "*.xls"
stExport2 = "HRIS to Safety Export " & Format(Now(), "YYYYMMDD") & " UPDATED.xls"
ThePath = "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\"
SourceFile = ThePath & "BeforeImport\" & Dir(ThePath & "BeforeImport\*.xls") ' Define source file name."
DestinationFile = "C:\Documents and Settings\tina.DCC\My Documents\Databases\Safety WCB Database\AfterImport\" & stExport2 ' Define target file name."


DoCmd.TransferSpreadsheet acImport, acspreadsheetTypeExcel2000, "ImportData", SourceFile, False
DoCmd.OpenQuery "QuEmployeeImport", acNormal, acEdit
DoCmd.RunSQL "delete * from ImportData"

Name SourceFile As DestinationFile

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub