VBA Automation Error (System call failed) while running Excel 2013 macro from Access 2013

Question

I have an Access (2013) database that I use to store/process all of our data received from a Qualtrics online survey. The raw data downloaded from Qualtrics is in a csv file that's poorly formatted for importing to Access, so I've got a fairly complex Macro in Excel (2013) that I use to pre-process the data before importing to Access.

In Access, I use the following code to open the excel file that contains the macro, run the macro, save the workbook, and close it. This had been working well several for several months, but now when I run it, it stops near the end of the Excel Macro with the run time error: -2147417856 Automation error System call failed.

ActivateOrOpenWorkbook WbkName & ".xlsm", strWbkPath
appExcel.Run ProcName, ProcArg

appExcel.Workbooks(WbkName).Save
If appExcel.Workbooks.Count = 1 Then
    appExcel.Quit
Else
    appExcel.Workbooks(WbkName).Close True
End If

ActivateOrOpenWorkbook is just a custom function to do exactly what the name implies, appExcel is the Excel Application. The workbook always opens fine, and the macro begins to run, but it never actually reaches the point where control returns to the Access VBA and saves the workbook.

It does run fine if I open the Workbook before running the Access procedure, insert breakpoints at every major VBA step (in both Access and Excel), and step through the whole thing one Sub at a time. It just fails if I try to let VBA run it all from start to finish on it's own.

Based on that evidence plus stories of similar problems that I've seen online, I suspect that the error is occurring because the Excel macro is taking too long to run (we recently added some new variables to the Qualtrics survey), and Access is trying to take back control before Excel is finished. I just haven't found any viable way to solve that suspected problem or investigate it further.

I did try inserting this makeshift Wait routine into my ErrorHandling for the Access Sub, but it didn't work at all, because the error message still popped up in the same amount of time as before.

If Err = -2147417856 Then
TWait = Time
TWait = DateAdd("s", 15, TWait)
Do Until TNow >= TWait
    TNow = Time
Loop
Resume Next

Any help will be appreciated!

barrowc barrowc · Accepted Answer · 2014-07-25T01:23:03

Have you tried using DoEvents in the pre-processing macro?

If you have a loop where you're processing the CSV file line by line then use DoEvents every so often to give any pending events a chance to run. It would be overkill to call it on every line so maybe start with every 100 lines and adjust from there.

Given that the macro works when you step through the Subs, it would appear that it isn't the overall execution time that's the problem. If Access is throwing an error because Excel appears to be unresponsive then DoEvents might be enough to stop Access from giving up

VBA Automation Error (System call failed) while running Excel 2013 macro from Access 2013

1 Answers