FollowHyperlink problem

[bxgti4x4]

I have an Access form with a command button that runs the following code to export the data to Excel:

DoCmd.OutputTo acOutputQuery, "QryVP", acFormatXLS, "VP.xls", True
Application.FollowHyperlink "N:\Access\BackgroundVP.xls"

The Excel spreadsheet BackgroundVP.xls has an automacro with the following code:

Application.AskToUpdateLinks = False
    Workbooks.Open Filename:="N:\Access\VesselPartic.xls"
    Application.Dialogs(xlDialogSendMail).Show
    Windows("VesselPartic.xls").Activate
    ActiveWorkbook.Close
    Windows("VP.xls").Activate
    ActiveWorkbook.Close
    Application.Quit

This opens spreadsheet VesselPartic.xls, updates the links to VP.xls and sends it to Outlook for emailing. It then closes the open files, leaving only Background.xls open. The reason for putting the automacro into a seperate file is to avoid having macros in VesselPartic.xls.

I actually want to close Excel completely once the spreadsheet has been mailed, which I can do if I add "Application.Quit" to the end of the code. However, when Excel closes, I then get a message from Access asking if I want to open Background.xls. If I answer no, I get the message that "the hyperlink cannot be followed to its destination".

It would appear that the access code is looping but I cannot find a way of stopping it. Any assistance would be very welcome.

Best Regards
John

 

[Craig0201]

I doubt it is looping. I would suggest that is because FollowHyperlink is an asynchronous call.

What about using an Excel object to open the spreadsheet and run the macro. This would be synchronous.

 

[bxgti4x4]

Craig,

Not sure how you go about this. My understanding is that the Excel worksheet has to be embedded and that is getting a bit beyond my knowledge. Any help would be much appreciated.

Best Regards
John