using transferspreadsheet in macro with variable filename

[Hosacans]

Hi all

I have a database that will be distributed to other computers and use to run off other data with same formatting. in the pass i would manually reset all the file directories that links raw data (excel files) from local hard drives. But now i am trying to create a form that will prompt the user to input the file directory to a table and hvae all the transferspreadsheet commands in my macros to refer to this "directory" table and it's corresponding directory.

for example...

if the user have raw data: dailysales.xls in C:\

i want to create a form that will store this directory onto a table and then have the transferspreadsheet command in my macros refer to C:\dailysales.xls

Do i ahve to use VB codes to implement this?

any help would be appreciate

thanks so much
i hope i explain this clearly

 

[PHV]

With VBA you could use the Dir function, the DoCmd.TransferSpreadsheet method and do some error handling.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Hosacans]

PHV-
thanks for the tip. can you please explain how to intergrate the dir function into the transferspreadsheet command? also i am having trouble referencing a variable to the record in the "directory" table.

much thx