Intercepting user input from subdialog VBA

Viewed 29

I'm working on file saving, the code for saving is following:

Dim workbookName As Variant

workbookName = wb.Application.GetSaveAsFilename( _
        fileFilter:="Microsoft Excel Worksheet (*.xlsx), *.xlsx")
If workbookName <> False Then
    If Not Dir(workbookName) <> "" Then
        wb.SaveAs Filename:=workbookName
    Else
        On Error Resume Next
        If Err.Number = 1004 Then
            On Error GoTo 0
        Else 
            wb.SaveAs Filename:=workbookName, _
                      AccessMode:=xlExclusive, _
                      FileFormat:=xlWorkbook, _
                      ConflictResolution:=xlLocalSessionChanges
        End If
    End If
End If
wb.Close (True)

This allows to choose file location and file name. It also partially handles error when file name is being used by other file (1004). In case when file already exists GetSaveAsFilename displays dialog that asks whether to replace or not.

Dialog displayed by GetSaveAsFilename looks like this: Replace confirmation dialog

That causes 2 problems:

  • replaced file becomes corrupted (the code is invoked from user form and no other code is executed after wb.Close (True))
  • the dialog is created by GetSaveAsFilename so I cannot handle user input in code

Any idea what could cause file to become corrupted? Is there a way to handle that user clicked "No"?

0 Answers
Related