External spreadsheet links

[souris]

My Access data links to spreadsheet I receive.
The name of spreadsheet name is changed all the time.
For example the name will be 'Data for Feb/14/2005' if I receive data on
Feb/14/2005.
If I receive the file on Jan/31/2005 then the file name is 'Data for
Jan/31/2005'

Are there any way to link spreadsheet dynamic file name?
Is it good idea to search the file name in the directory or build a string
base on system date?
Or to manual link every time I receive the file?

Any information is great appreciated,

 

[Dirk Goldgar]

My Access data links to spreadsheet I receive.
The name of spreadsheet name is changed all the time.
For example the name will be 'Data for Feb/14/2005' if I receive data
on Feb/14/2005.
If I receive the file on Jan/31/2005 then the file name is 'Data for
Jan/31/2005'

Are there any way to link spreadsheet dynamic file name?
Is it good idea to search the file name in the directory or build a
string base on system date?
Or to manual link every time I receive the file?

Any information is great appreciated,

Sure, you could do it in code, something like this:

Dim strFileName As String

strFileName = "Data for " & Format(Date(), "mmm/dd/yyyy")

DoCmd.TransferSpreadsheet _
acLink, _
acSpreadsheetTypeExcel9, _
"LinkedTableName", _
"C:\Your Folder Path\" & Format(Date(), "mmm/dd/yyyy")

That doesn't help much if the file was received yesterday, but you're
only linking it today. Still, it's a start.

Another possibility is to open the Windows File Open dialog,
prepositioned to the folder you expect the spreadsheet to be in and with
the default file name set from the date. Then if that's not right, the
user can select the correct file from the dialog. When the dialog
returns the chosen file name, you'd use the TransferSPreadsheet method
to link that file.