VBA to Click 'Yes'

[rjbinney]

I have a file with some macros that do a "Save As".

(One is to save a backup to an external drive, another strips all of the formulas to save on my phone... Yes, maybe I'm a little anal about this file! It's a database I've been building since my days of 1-2-3 on the computer lab in grad school...)

Anyways, when I click the Macro, it always lets me know the Save As is writing over an existing document. Is there a way to force the Macro to go ahead and save without the confirmation?

Also, it wants to confirm that my macros will be dumped when I save it in a Macro-Free format (for my phone). Can I also suppress THAT confirmation?

Thanks

Sub SaveForDevicesFixed()

'Save Current
    ActiveWorkbook.Save

'Save Local Backup
    ChDir "C:\Users\RJB\Documents"
    ActiveWorkbook.SaveAs Filename:="Inventory Backup.xlsm", FileFormat:=52
      
'Strip Formulas
    Sheets.Select
    Sheets("Inventory").Activate
    Cells.Select
    Range("b1").Activate
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
'Strip Button
    ActiveSheet.Buttons.Delete
   
'Save to X  
      If XExists("X:\Device Documents\Music") Then
        ChDir "X:Device Documents\Music"
        ActiveWorkbook.SaveAs Filename:="Inventory for DG.xlsx", FileFormat:=51
      End If
      
'Save to DTG
    ChDir "C:\Users\RJB\Docs to Go\Music"
    ActiveWorkbook.SaveAs Filename:="Inventory for DTG.xlsx", FileFormat:=51
      
End Sub
      
        
'Check if External Drive is Connected
      Private Function XExists(ByVal Path As String) As Boolean
          On Error Resume Next
             XExists = Dir(Path, vbDirectory) <> ""
      End Function

 

[Michael M]

You could switch off alerts at the beginning and turn them back on at the end !!

Sub SaveForDevicesFixed()
[color=red]Application.DisplayAlerts = False[/color]
'Save Current
    ActiveWorkbook.Save

'Save Local Backup
    ChDir "C:\Users\RJB\Documents"
    ActiveWorkbook.SaveAs Filename:="Inventory Backup.xlsm", FileFormat:=52
      
'Strip Formulas
    With Sheets("Inventory").UsedRange
    .Value = .Value
    End With
    
'Strip Button
    Sheets("Inventory").Buttons.Delete
   
'Save to X
      If XExists("X:\Device Documents\Music") Then
        ChDir "X:Device Documents\Music"
        ActiveWorkbook.SaveAs Filename:="Inventory for DG.xlsx", FileFormat:=51
      End If
      
'Save to DTG
    ChDir "C:\Users\RJB\Docs to Go\Music"
    ActiveWorkbook.SaveAs Filename:="Inventory for DTG.xlsx", FileFormat:=51
 [color=red]Application.DisplayAlerts = True[/color]
End Sub

 

[rjbinney]

Oh, that's brilliant.

I probably shouldn't gush over simple fixes like that, but I am visibly excited.

Now why didn't I add that to my Macros back when I first started writing them in Excel 97?

 

[Michael M]

Maybe because somtimes you NEED to be alerted !!
It's not always a good idea to supress messages / warnings.

Also note that I shortened your strip formula section of the code !!

 

[rjbinney]

I had NOT noticed that. I will likely keep the "Activate" bit so it always opens on the correct cell/sheet...

Thanks again.

 

[Michael M]

You shouldn't need to activate the sheet......that just slows done the code !!

 

[rjbinney]

Actually, the shorter code took much longer to run, while it was "Filling Cells" and then recalculating.

Also, it only ran the formula on the top sheet - there are formulas in multiple sheets that need to be stripped before it can be saved (there are LOOKUPS and INDEX/MATCHES that neither Documents to Go or Excel for Android can handle).

(Western NSW? I guess that explains why you can play on Excel on a springtime Saturday afternoon! What is there to do in Western NSW!?!)

 

[Michael M]

.....I Play comp golf 4 days a week !!

Ok, you can loop through all the sheets that need stripping and convert then to values the same way I did earlier.
You can also turn off calculation until ALL cells are filled, similar to the line I provided earlier.
Something like
at the start

With Application
.DisplayAlerts = False
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

and at the end

With Application
.DisplayAlerts = True
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With