How to disable or delay autosave in MS Word from MS Excel vba

Viewed 80

I have an Excel file that exports a large amount of data to Word (about 100 pages) and I'm trying to turn off autosave during the export to speed it up. I've tried two methods so far but neither seems to do what I expected: (note that the code is in the excel file from which the data is exported)

First method:

oWordfile.AutoSaveOn = False   'oWordfile is type Word.Document
MsgBox "Autosave state: " & oWordfile.AutoSaveOn

I used the msgbox periodically within the code to confirm the autosave state hadn't been changed. The title bar of the Word file I am exporting too indicates it is saving about every 5 minutes while the msgbox popups always indicate that the autosave state is False.

Second method:

oWordApp.Options.SaveInterval = 30    'oWordApp is type Word.Application

After code execution, I confirmed that the AutoRecover save interval had been changed to 30 minutes, but during export, the file still saves about every 5 minutes.

Since neither of these two methods changed the behavior of Word, I figure I must be changing the wrong setting but I'm not sure what other settings control the autosave feature. If any one knows what I'm missing, I'd be grateful for the help.

updates after Byrd's answer:

Third method: cancel save:

MS-Word

Private WithEvents App As Word.Application

Private Sub Document_Open()
  Set App = Word.Application
End Sub

Private Sub App_DocumentBeforeSave(ByVal Doc As Document, SaveAsUI As Boolean, Cancel As Boolean)
  If MsgBox("Save Document?", vbYesNo, "Word: BeforeSave") = vbNo Then
    Cancel = True
  End If
End Sub

Excel

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  If MsgBox("Save File?", vbYesNo, "Excel: BeforeSave") = vbNo Then
    Cancel = True
  End If
End Sub

I used message boxes as a temporary solution as to when a save is wanted to test this method. The message boxes appear any time the code or user initiates a save event in Excel and Word. If the word file is left open the message box also appears when autorecover save event triggers (currently set to 30 minutes). During code execution exporting from Excel to Word, Word still saves at 5-minute intervals without triggering the BeforeSave event.

Word title bar indicating saving

Word title bar indicating file saved

I have now tested a little further and discovered that even though the title bar indicates saving is happening during the export, the file hasn't actually been saved. Using task manager to force close Word directly after export, I discovered that the file was unchanged from before code execution. Autorecover didn't have a version of the file to recover either. So now I have no idea of what is actually happening - all I know is that the export slows down greatly while the title bar indicates a save is in progress.

1 Answers

Excel

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

    Cancel = True

End Sub

Word

Private Sub appWord_DocumentBeforeSave _ 
 (ByVal Doc As Document, _ 
 SaveAsUI As Boolean, _ 
 Cancel As Boolean) 

 Cancel = True

End Sub

I don't know if the user will need to save the document at all during this export but, just canceling the save all together might do it.

Related