Macro to prevent or answer Excel recovering windows

[MarieBocc]

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 :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Export_EDF()[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    Dim Feuille As Worksheet
    Dim FeuilleRef As Worksheet
    Dim Sheet As Object
    Dim FichierCible As Variant

[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    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[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub RemoveButtons()
    If ActiveSheet.ProtectContents = True Then
    End If[/FONT]
[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]    On Error Resume Next
        ActiveSheet.Buttons.Delete
End Sub[/FONT]

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 [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]Sub Workbook_Open(). With a little researches I found out that I might use this to prevent Excel from asking me wheather to repare :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]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[/FONT]

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_{^{<strike>
</strike>}}[/FONT]​

 

[Kamolga]

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

[LEFT][COLOR=#666666][FONT=Courier New]ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks[/FONT][/COLOR][/LEFT]

after .save and then re-save it?

 

[MarieBocc]

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

Like you advised me to do, I tried this

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]ActiveWorkbook.Save
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
    ActiveWorkbook.Save[/FONT]

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

 

[Kamolga]

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?


​

 

[MarieBocc]

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 :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]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[/FONT]

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

 

[Kamolga]

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

 

[MarieBocc]

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...