Results 1 to 7 of 7

Thread: Macro to prevent or answer Excel recovering windows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to prevent or answer Excel recovering windows

    Hello everyone,

    I have a macro that creates a new file almost similar to the one it runs on by Saving it As, copy/pasting as values every sheet and removing the old ones. It goes like this :

    Code:
    Sub Export_EDF()
        Dim Feuille As Worksheet
        Dim FeuilleRef As Worksheet
        Dim Sheet As Object
        Dim FichierCible As Variant
    
    
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        
        FichierCible = Application.GetSaveAsFilename
        If FichierCible <> False Then
            MsgBox FichierCible
            ThisWorkbook.SaveAs Filename:=FichierCible & "xlsm", FileFormat:=52, Password:="", ReadOnlyRecommended:=False, ConflictResolution:=xlOtherSessionChanges
            
            Set FeuilleRef = ActiveSheet
        
            ' copy/paste as values of all worksheets
            For Each Feuille In ActiveWorkbook.Worksheets
                With Feuille
                    .Cells.Copy
                    .Cells.PasteSpecial Paste:=xlPasteValues
                End With
            Next Feuille
            
         
            For Each Sheet In Worksheets
                If Sheet.Visible = False Then
                    Sheet.Delete
                End If
            Next
            
            Call RemoveButtons
    
            FeuilleRef.Activate
    
        End If
    
        ActiveWorkbook.Save
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
        Application.DisplayAlerts = True
        
    End Sub
    Sub RemoveButtons()
        If ActiveSheet.ProtectContents = True Then
        End If
        On Error Resume Next
            ActiveSheet.Buttons.Delete
    End Sub
    After this macro finishes running I am in a new Workbook pretty similar to the one I had to begin with. It is already saved and I can close it.

    But when I try to reopen it afterward (only the first time), an Excel pop-up window shows up saying :
    "We found a problem with some content in "documentname.xlsx" Do you want us to try to recover as much as we can?"
    If I click 'Yes', which is what I do (, the default value is 'No'), I am presented with a dialogue box stating what was "fixed." The box states "Repaired Records: AutoFilter from /xl/worksheets/sheet1.xml part" (a lot of times) and I click on 'Close', which is the default and only option I have.

    This will show up everytime I create a workbook using my macro and I can't really tell my clients to ignore the pop-up Windows and to just click on 'Yes' and then 'Close' for
    it lacks professionnalism
    so I was thinking of writing a macro to do it for them (preferably without them noticing).

    My idea was to use the Sub Workbook_Open(). With a little researches I found out that I might use this to prevent Excel from asking me wheather to repare :
    Code:
    Private Sub Workbook_Open()
        If ActiveWorkbook.Name <> "theOriginalFile'sName.xlsm" Then
            Application.DisplayAlerts = False
            Application.AskToUpdateLinks = False
            ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
        End If
    End Sub
    Sounds good, doesn't work. Unfortunately. The first pop-up window shows up before to Sub Workbook_Open() function reaches .UpdateLink.

    Does anyone has any idea what to do to prevent this windows from poping up ?

    Thank you very much for reading,

    Marie

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent or answer Excel recovering windows

    Hi,

    I try to reproduce your error but I have no message. Indeed I can close it without any message and when I open it, I have a 'enable editing' (like when I receive a file from someone) on top of the screen but no message about fixing data.

    If your file has links, vba does not update them automatically, so maybe use
    Code:
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
    after .save and then re-save it?
    Last edited by Kamolga; Jul 31st, 2019 at 12:16 PM.

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent or answer Excel recovering windows

    Thank you for taking the time to test it and to answer !

    Like you advised me to do, I tried this

    Code:
    ActiveWorkbook.Save
        ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
        ActiveWorkbook.Save
    Instead of just saving it but nothing changed at all. Since I use a Worksheets.Cells.PasteSpecial Paste := xlPasteValues, I really wonder why I have links recovering problems to begin with, since my new file is supposedly, after saving it, just a normal file with no formlas at all, right ?

    Thank you again,

    Marie

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent or answer Excel recovering windows

    Indeed.

    In your new file, you can check 'existing connection' in your data tab.
    Formulae as long as within the workbook are fine, might have troubles if linked to other workbooks that are closed when you duplicate the file. I assume you had links since you used
    Application.AskToUpdateLinks = False. You might use that code in your duplicating sub before saving but I guess it will be set back to true when closing/opening.
    The code seems right, I would only dim Sheet as Worksheet instead of object and not put "
    ConflictResolution:=xlOtherSessionChanges" but
    XlSaveConflictResolution.xlLocalSessionChanges
    and what I usually do is to dim 2 workbooks, the origininal (activeworkbook) as Wb1 and saves as Wb2. When overwriting, it seems you need to use Wb2.displayalterts=false but since your message seems to be about dataintegrity, I am not sure what to do to solve it.
    By the way, do you have the issue if you leave your reference/original workbook opened while closing and opening the new one?



  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent or answer Excel recovering windows

    Thank you again for your reply,

    First of all I tried (without changing anything to my code) to leave my original workbook open while closing and opening the copy. It doesn't change anything to the issue.

    Regarding the Application.AskToUpdateLinks = False, honestly it was more like a vain attempt to get rid of the two Windows poping-up than anything else. But ! I went to check 'existing connections' in the data tab and I have no 'connections' to other files but in 'tables' I do have linked tables to the original workbook !

    So I tried searching for a way to remove them and this sounded good :

    Code:
    Sub removeconnections()
        
        MsgBox "removeconnection function is entered"
        Dim xConnect As Object
        Dim i As Integer
        i = 1
        For Each xConnect In ActiveWorkbook.Connections
            MsgBox "connection " & i
            If xConnect.Name <> "ThisWorkbookDataModel" Then
                xConnect.Delete
                MsgBox "connection deleted"
            End If
            i = i + 1
        Next xConnect
    End Sub
    I added MsgBoxes for debugging purposes and the only message I get is "removeconnection function is entered" implying that ActiveWorkbook.Connections is empty and no connection has been deleted, right ? So I find it really weird that now the data tab > existing connections > tables of the copied workbook has no connections with the original workbook anymore ! How could they be deleted if the For Each loop isn't entered ?
    Also it didn't solve the issue of the tabs poping up at the opening of the file asking me to recover links. I really am cluelessÂ… I'll keep on trying to get rid of it,

    Thank you,

    Marie

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent or answer Excel recovering windows

    In File/options/advanced you have at the very end an option to 'update links to other documents' in 'when calculating this worbook' segment. It can be set up by Activeworkbook.UpdateRemoteReferences = False.
    The 'ask to update automatic link' in next segment can be set up with Application.AskToUpdateLinks = False

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to prevent or answer Excel recovering windows

    Thank you again for your reply,

    I tried puting it in the code after the copy has been done completely. I also tried putting it in the ThisWorkbook > Workbook_open() function.
    None of it worked, I am sorry...

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •