tranfer spreadsheet macro

[ODBCERRORKING]

whazzz uppp! hey i was wondering if you could help me out i am writing this macro to pull 5 diffrent workbooks from an excel spreadsheet and the file name always changes i was wondering if there is a command or somthing i could put in there to prompt me for a file name in the tranfer spreadsheet function? since the file name always changes i have to go to the macro each time and change the file name.
there has got to be an easyier way without code i hope!

 

[link9]

Well, you could always just build a little form that would pop up and be sort of like your explorer view (without all the bells and whistles) that would allow you to browse to the file -- use the fileSystemObject to achieve this...

or if you know the path, the form could simply be a little text box, where you would type in the full path of the file you wanted, and then just refer to the value of that text box when you go to open your file....

is that what you were asking, or am i off base?

Paul Prewett

 

[ztm]

I use the OutputTo command to output a table to a spreadsheet, and I leave the filename argument blank. It prompts me for a filename with a standard windows 'save as' dialog box. I think the same would work with the TransferSpreadsheet command. Be sure to include the comma, though.


DoCmd.OutputTo acOutputTable, "tblName", "Microsoft Excel (*.xls)", , 1