Search code examples
vbaexceldefaultcommand-prompt

VBA: Answer "save as - overwrite" prompt (and others) NOT with default option

I have a .xlsm file from which I need some sheets saved in a non-macro file format (.xlsx). For this, I have created the following code. The first part saves the active Worksheet, called "CombinedRaw", as a seperate file "DATATRY.xlsx". This closes the active file, i.e. we are no longer in the file "data retrieve 9.xlsm", but "DATATRY.xlsx" instead, so after deleting the unnecessary worksheets in the new DATATRY file, I open the workbook data retrieve 9.xlsm in which I originally started again, and close DATATRY, so that at the end of the macro I am where I was before. Otherwise, the VBA window would close, and I'd have to manually close the new file, open the other, and open VBA developer again.

You notice that I managed to override the Delete-Worksheet-Confirmation prompt; however, there are other prompts that I cannot override in this way as I do not want to answer with the default answer:

The first one is "the following features cannot be saved in Macro-free workbooks: ... to continue saving, click yes". Here I need yes, and it is also default answer, too.

However, "A file named ... already exists in this location, do you want to replace?" needs to be yes, but default is no.

After deleting the sheets comes "This workbook contains links to other data sources", as it contains formulas to get data from Thomson Reuters database. Default is "Update", but I want "Don't update".

Then again "cannot be saved in macro-free workbooks", where I need the default yes.

Are there methods to predefine different answers that should be given to several sequential prompts?

Sub Speichern()

    ChDir ThisWorkbook.Path

    'save as DATATRY.xlsx without macros
    Worksheets("CombinedRaw").SaveAs Filename:= _
        ThisWorkbook.Path & "\DATATRY.xlsx", FileFormat:=51, _
        CreateBackup:=False

    'delete other sheets in the new file, then close
    Application.DisplayAlerts = False
        Worksheets("Codes").Delete
    Application.DisplayAlerts = True

    'open "this" book again
    Workbooks.Open Filename:=ThisWorkbook.Path & "\data retrieve 9.xlsm"
    Workbooks("DATATRY.xlsx").Close SaveChanges:=True

End Sub

Alternatively, deleting the previously existing file from the folder and then saving it would seem to me like a brute workaround, but if it helps, perhaps it is better? But this will still not solve the non-default "update" prompt problem.

Solution

  • You are doing a very wrong thing here, and naturally you get warnings in the process.

    Here's what you should be doing instead.

    Sub Speichern()

  Dim w As Workbook
  Set w = Workbooks.Add

  ThisWorkbook.Worksheets("CombinedRaw").Copy Before:=w.Sheets(1)

  Dim i As Long

  Application.DisplayAlerts = False
  For i = w.Sheets.Count To 2 Step -1
    w.Sheets(i).Delete
  Next
  Application.DisplayAlerts = True

  w.Close SaveChanges:=True, Filename:=ThisWorkbook.Path & "\DATATRY.xlsx"

End Sub

    You won't get the xlsm warnings provided you don't have code in the code module of CombinedRaw. If you do, you might want to move it out to a module.

    You will still get the overwrite warning, but only that one.
    If you'd like to suppress that warning either, move the Application.DisplayAlerts = True to be after the w.Close.