TransferSpreadsheet Method

[MrsGorilla]

I've been struggling with this since yesterday morning and I have a couple of questions.

I'm creating a button for one of my users to export a query to Excel once a month for distribution. Is there a way to use the TransferSpreadsheet method but have it prompt for a file location/document name? I've successfully used the OutputTo method but it automatically plops it into an old version of Excel. Not a big deal I suppose, but I'm trying to make it as easy as possible for the user. Which brings me to another point, is it possible to specify which version of Excel you want to format as with OutputTo? I'm guessing no, but want to double-check.

Also, is it possible to do any formatting of the Excel file as it exports? Currently I have a macro written in Excel that the user will have to run as a separate step. I was just wondering if it's possible to do any of that through Access to eliminate the extra step of running the macro.

Thanks for any help.

 

[mhartman]

Hello:

Might this help?

http://msdn2.microsoft.com/en-us/library/aa193071(office.10).aspx

Regards
Mark

 

[mhartman]

Hello again:

http://www.bluemoosetech.com/micros...le=Microsoft Access Transferspreadsheet Macro

Regards
Mark

 

[mhartman]

One more

http://www.mvps.org/access/general/gen0008.htm

Just change import to export

Regards
Mark

 

[MrsGorilla]

Thanks for the replies. I've looked at most of that already. My problem was when I looked in Access help it mentioned filename as being an optional variant argument. I knew in the OutputTo method if you leave the filename blank it will prompt you for one. When I tried that with TransferSpreadsheet it gave me an error stating it needed a filename. I looked on MSDN and it does say that filename is required. I therefore didn't know if there was a way to get it to prompt for a filename, or if I'm stuck to using OutputTo and having it go to an old Excel format.