Simple problem with OLE warning msgbox

[baldmosher]

I've got a control macro in Excel VBA that opens a database in Access, runs an Access macro, then closes the DB, before carrying on with Excel and running off a load of reports.

Dim A As Object
Set A = CreateObject("Access.Application")
A.Visible = False
A.OpenCurrentDatabase ("C:\Database\Database.mdb")
A.DoCmd.RunMacro "01 do all queries"
A.CloseCurrentDatabase
A.Quit
Set A = Nothing

I want it to run overnight because the Access macro takes a couple of hours, and the reports take an hour or so to run after that. After a couple of minutes, Excel pops up an error message "waiting for OLE application to complete" which then stays in focus until I click OK. If I click OK before Access is finished, the error reappears a few moments later. So at the moment, I have to click OK when I come back into work the following morning to run the reports. Not exactly a massive problem, but there must be a way to avoid it?

I'm thinking there must be a way to either suppress the OLE warning completely whilst Access is still processing, or to make Excel simply wait for a predetermined amount of time (e.g. 10 hours) before checking to see if Access has finished updating the database.

 

[kpark91]

I'm not too sure as I don't deal with external applications but at the start of your code, you could add

Application.DisplayAlerts = False

and at the end

Application.DisplayAlerts = True

 

[baldmosher]

You know, I find it's usually the simplest idea.....

I can't actually remember ever trying that in this case. I don't know if it will work with external applications, but I will try it as soon as it's finished today's reports update (half way through as I type).

(I'm sure someone else will be along in a moment to say that it won't work!)

 

[baldmosher]

Whaddyaknow, it seems to have worked. Many thanks for stating the obvious, sometimes it's necessary!