Open Workbook from Access Problem

[Carly]

I have got an access database which opens an Excel workbook using the following code:

With CreateObject("Excel.Application")
   .Workbooks.Open "\\stoke\analytic$\Price Increase\MarginRecoveryPlan " & vMonth & "\zPricingExtractManipulation\PricingExtractManipulation-MACRO.xlsm", True
   .Visible = True
End With
DoCmd.Close acForm, "FrontPage", acSaveNo
DoCmd.Quit

The Excel workbook as a form which loads when the workbook is opened as follows:

Private Sub Workbook_Open()
   frmStages.Show
End Sub

The problem I have got is the Access database just hangs instead of closing until something is done with the form on the excel workbook, which is not what I want to happen...& if I change the form on the Excel spreadsheet to be ShowModal = False, then the excel workbook doesnt seem to open properly.

Is there anything I can do so that it works like, Access Database closes, Excel workbook open as if I was manually doing so?

 

[Ruddles]

Is the DoCmd.Quit intended to close Access? I think you need to do Application.Quit.

BICBW...

 

[Carly]

It is...I will try this just to check if it does something differently, however, this code works normally without the automatic open form bit in excel.

Will let you know how it goes.

 

[Norie]

What is the Excel userform meant to do?

Do you actually want it to he shown?

 

[Carly]

Changing it to Application.Quit makes no difference.

The Excel userform just pops up on open which i want it to do, but I would like it to close access before this happens & without the form being there, that is exactly what happens with this code.

In theory, I want to close access, & open excel with the form popping up as it should on open.

 

[Norie]

This has been answered here:

http://www.utteraccess.com/forum/Open-Workbook-Access-Pro-t1968726.html

Carly please try not to cross post.

If you do at least post links and try to give it a liitle time on whatever forum you post in first before trying another one.

 

[Carly]

I do apologise, however, I was just at lunch and was about to post the answer when I got back, but you obviously beat me to it.

I cross post to capture as many peoples brains as possible on a post that wasn't answered as quickly as usual. To help myself & others.

I didn't realise there were rules about this!

 

[Carly]

This has now been completely answered on the other forum:

Call Shell("Excel.EXE ""\\stoke\analytic$\Price Increase\MarginRecoveryPlan " & vMonth & "\zPricingExtractManipulation\PricingExtractManipulation-MACRO.xlsm""", vbNormalFocus)

 

[Norie]

Carly

Wasn't it suggested you use a hyperlink via FollowHyperlink instead of Shell?

 

[Carly]

Carly

Wasn't it suggested you use a hyperlink via FollowHyperlink instead of Shell?

I tried the hyperlink way and the result was the same as my original problem...Call Shell works exactly how I want it to work, so I'm very happy with the way this is working.