Waiting For a File Save To Finish

[richlocus]

Hello:
I have an Access program that saves Excel Documents in "rapid fire" succession but sometimes loses the first page or two. For example, let's say I have an Excel document that emulates a letter-size form. If the document is 4 pages, then four .xlsx files are pushed out from Access.

From Access VBA, I create an Excel interface:
Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Open("C:\BeerMovementTemplate\BillOfLadingShortFormV2.xlsx")
Set xlSh = xlWB.Sheets("BOLShortForm")

Since these documents are exported in "rapid fire" mode, I need to slow it down so that when the first document of a multi-page document is saved, Excel ((through the Access interface) won't start saving the other Excel documents until the previous one is finished.

When I Issue the Save command (xlWB.SaveAs FileName:=strSavedFileName, FileFormat:=xlOpenXMLWorkbook) I want to wait until it is finished before I save the next one.

An article I read said that immediately after the save command, use this:

xlWB.SaveAs FileName:=strSavedFileName, FileFormat:=xlOpenXMLWorkbook
Do
Loop Until ActiveDocument.Saved

Is this correct, or is there a better way to handle it?
Thanks,
Rich Locus

[HansV]

I'd first insert a line

Code: Select all

    DoEvents

below the SaveAs line, to see if that is sufficient. If not, try

Code: Select all

    Do
        DoEvents
    Loop Until xlWB.Saved

(ActiveDocument is Word VBA, not Excel VBA. And don't use ActiveWorkbook either. Explicitly refer to the xlWB workbook.)