Open an Excel Spreadsheet from Access 1

[bxgti4x4]

I have a Access form with a command button that exports the results of a query to Excel using the following code:

DoCmd.OutputTo acOutputQuery, "QryCTP", acFormatXLS, "CTP.xls", True

The data in "CTP.xls" is then imported into another spreadsheet named "CTPartic.xls".

I would like to automate the data transfer so that spreadsheet "CTPartic.xls" is opened by the code, the links are updated and the "GetSaveAsFilename" dialog box is opened.

Can anyone advise if this is possible and the code I should use.

Best Regards
John

 

[PHV]

A starting point:
DoCmd.OutputTo acOutputQuery, "QryCTP", acFormatXLS, "CTP.xls", False
Application.FollowHyperlink "CTPartic.xls"

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

 

[bxgti4x4]

Thanks PHV, that was just the hint I needed.

Best Regards
John

 

[bxgti4x4]

PHV

I successfully followed your tip and now have the Excel form opening as I want it. The Excel form has an auto macro which emails it and then closes the form down. However, if I have the macro quit Excel, which is what I want it to do, the "followhyperlink" code in my Access form asks if I want to re-open the Excel form.

If I close the Excel form manually and then close down Excel, this does not happen.

Could you please advise how I can stop the code from looping round.

Best Regards
John