TransferSpreadsheet Help

[fredk]

I am using the following code to rename my current file (basically added the current date to the file name and then importing the most recent file from excel into access and naming that the file name used for my queries.


DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="tblCommision", Filename:="tblCommision.xls", _
Hasfieldnames:=True

In thinking this through, I want to make sure that the user has the file saved in excel under the corrent name - If they don't the first part of the code to rename the current file will run but the new file won't import.

Any suggestion on making sure the excel file is there before running the code?

thanks!!

Fred

 

[p27br]

you can use the Dir function or the Filesearch object.

 

[fredk]

Thanks p27br - I will look that up in help and try it

Fred

 

[JR2913]

Don't know if you have solved this yet, but you could create a module, something like this:

Public Sub Filesearch(Filename As String, Folder As String)

Dim obj As AccessObject


Set obj = Application.Filesearch
With obj
.LookIn = Folder
.Filename = Filename
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then
MsgBox "The required file does not exist."
End If
End With

End Sub



then just call the module from within your event, e.g.

Filesearch("tblCommision.xls", "C:\Datalocation&quot



John R

 

[fredk]

actually, I did get it to work by using the dir function like p27br suggested - I appreciate your input as I am trying to grasp more coding techniques and your input helps!

Thanks much!

Fred