exporting from access to excel

[kazprog]

Please can anybody help

I am in the process of transferring data from an access db (MDB)to an excel spreadsheet.
Access 2003 / Excel 2002

I have created a procedure that will

Present a form allowing the user to select what they want to transfer
Run a macro that will

Run a query selects what to transfer from the database
Run a Transferspreadsheet command to send the information to an Excel spreadsheet.
Send a message

My problem is not transferring the correct data, it is being able to create a new spreadsheet each time in a specified directory. And checking to see if there is already a spreadsheet there

I wondered if anybody has done anything like this before

Ta
Kazprog

 

[SkipVought]

Kazprog,

You are trying to create a new workbook in a folder?

What does the Excel user do with these poliferating workbooks?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[OhioSteve]

You say that the problem is "checking to see if there is already a spreadsheet there". Its not clear to me if you want to kill the existing .xls file or preserve it. However, I will give you my general advice on outputting .xls files:


1) The most common way to handle exports is the the "output to" action in the macro builder.

2) The "output to" action has an argument called "output file".

3) If you leave that argument blank, the user will be able to choose a home for the file. This makes it easy to output the same thing several times.

4) If you provide a hardcoded address for that argument, the file will go to the same place each time, and it will have the same name. It will overwrite anything that is already at that address.

5) I prefer the second option, because it stops the user from creating a zillion .xls files quickly. However, the second option will not work if the user has the .xls file open when the "output to" action fires. You will get an error because Access will not be able to kill the old .xls file.