Saving and closing file without warnings

cbrf23

Board Regular
Joined
Jun 20, 2011
Messages
241
Can anyone tell me the best way to do suppress all warnings when saving a file programatically?
I'd like to suppress all warning dialogs.
I've set "application.displayalerts" to false, but I've got a dialog that still pops up which halts code execution.

The nature of this program is that it will update ALL the excel files on our server, so it needs to run when nobody else is on the server as it creates a lot of traffic opening and saving files.
Since there is nobody there to click "ok" when the dialog pops-up, the program never finishes :(

Here is the procedure that updates the files and saves the changes.
Interestingly, I found that just using the close method with "save changes" turned on wasn't working - it wasn't saving the changes. So I found I had to force it to save using the save method.
(the application.displayalerts setting is set in the calling procedure)
Code:
Private Function UpdateLinks(strPATH As String) As Boolean
    Dim lnkX    As Excel.Hyperlink
    Dim wshX    As Excel.Worksheet
    Dim wbkX    As Excel.Workbook
    Dim lngH    As Long
    
    UpdateLinks = False
    Application.StatusBar = "Updating file: " & strPATH & "..."
    On Error Resume Next
    Set wbkX = Excel.Application.Workbooks.Open(strPATH, 3, False, , , , True, , , , False, , False, True, xlNormalLoad)
    On Error GoTo 0
    
        If Not wbkX Is Nothing Then
                For Each wshX In wbkX.Worksheets
                    lngH = 0
                    lngH = wshX.Hyperlinks.Count
                        If lngH > 0 Then
                            UpdateLinks = True
                                For Each lnkX In wshX.Hyperlinks
                                    lnkX.Address = Replace(lnkX.Address, "\\oldserver\oldshare\", "\\newserver\newshare\")
                                Next lnkX
                        End If
                    wshX.Cells.Replace What:="\\oldserver\oldshare\", Replacement:="\\newserver\newshare\", MatchCase:=False, lookat:=xlPart
                Next
            wbkX.Save
            wbkX.Close
            UpdateLinks = True
        End If
    
    
End Function
 

cbrf23

Board Regular
Joined
Jun 20, 2011
Messages
241
Displayalerts=false is working to suppress this dialog.
I must have someone skipped that line or changed it in the immediate window when I was originally testing as I just stepped through it again and it's working as intended.
Mods can feel free to delete this question or leave it if you see any value to it.

Thanks!
 

Forum statistics

Threads
1,082,247
Messages
5,364,003
Members
400,774
Latest member
Goldi paul

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top