OLE Action issue when using Excel VBA to run Access Macro

[Bullpen]

Hey everyone,

I have written an Excel VBA that runs an Access Macro. The problem is that I receive the error message 'Microsoft Excel is waiting for an OLE action to complete' over and over.

The Access macro results in a large file and takes about 1 minute when I run it by itself (manually, not through Excel macro). The Access Macro was made to output the data into a new spreadsheet and I cannot change the Access macro.

I included the lines 'Application.DisplayAlerts = False' and 'Application.DisplayAlerts = True' because I saw someone recommend that on this site.

Here is my Excel VBA Code

Sub Run_Access_Macro()
Dim A As Object
Set A = CreateObject("Access.Application")
A.OpenCurrentDatabase ("C:\Users\john\desktop\SFWH.accdb")
A.DoCmd.RunMacro "mT-MobileProject"
Application.DisplayAlerts = False
A.CloseCurrentDatabase
A.Quit
Application.DisplayAlerts = True
Set A = Nothing
End Sub


Any help is greatly appreciated!!

Thank you

John

 

[Derek Brown]

I am not sure but I wonder if Excel is not waiting for the Access macro to finish.
You could try adding a wait after the RunMacro:

Application.Wait Now() + TimeValue("00:01:00")

Example given is for one minute.

 

[Bullpen]

Thank you Derek Brown for responding to my question. Unfortunately it did not help. I think the problem is with the Access macro. The Access macro is programmed to automatically export the data into a new spreadsheet and it seems to have an issue with the fact that Excel is already open or that the Access macro is being initiated by Excel.

John

 

[koltregaskes]

I have a similar issue. My Excel macro runs Access to export a file and get the OLE error. Has anyone come up with a solution to suppress te OLE error message? If I click on it when the Access macro is complete then the Excel macro carries on like normal but I'd like to have no prompts during the process so I can leave it running without any action.

 

[koltregaskes]

Ignore, Application.DisplayAlerts = False command eventually worked.

 

[Derek Brown]

You could try:

Application.DisplayAlerts = False
.
.
.
Application.DisplayAlerts = True

but if your code is not waiting for the Access process to complete, could that result in a later error?