Waiting For a File Save To Finish

richlocus
Lounger
Posts: 41
Joined: 03 Oct 2015, 00:30

Waiting For a File Save To Finish

Post by 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

User avatar
HansV
Administrator
Posts: 71216
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Waiting For a File Save To Finish

Post by 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.)
Regards,
Hans