Excel wijzigt bronbestand

[mariannef]

Hallo, ik heb een probleem met koppelingen in een Excel bestand, namelijk dat Excel de formule wijzigt wanneer het bronbestand wordt hernoemd of op een ander locatie wordt opgeslagen.

Ik heb bij voorbeeld een formule ingevoerd in 'doelbestand': +VERT.ZOEKEN(A1;'Y:\test\[bronbestand.xlsx]Blad1'!$A$1:$B$5;2;ONWAAR)
Nu werkt iemand anders in het doelbestand en diegene heeft het bronbestand ook geopend.

Om de een of andere reden wil diegene dit bronbestand voor eigen gebruik even ergens anders opslaan, bijvoorbeeld op zijn of haar eigen bureaublad.
Op dat moment verandert de formule in het doelbestand in=+VERT.ZOEKEN(A1;'C:\Users\Janneke\Desktop\[bronbestand.xlsx]Blad1'!$A$1:$B$5;2;ONWAAR)

Het doelbestand wordt opgeslagen, iemand anders opent het en voila, foutmelding: bestand is ernstig beschadigd o.i.d.

Hoe kan ik nu voorkomen dat Excel de formule aanpast, want ik kan iedereen op het hart drukken bestanden niet op een andere locatie op te slaan, maar dit gaat natuurlijk op een gegeven moment fout.

Ik hoop dat iemand me kan helpen, want tot nu toe heb ik niks kunnen vinden op internet en ook niet in de verschillende opties van Excel die ik bekeken en uitgeprobeerd heb.

Bij voorbaat dank!
MarianneF

 

[SjonR]

de cellen met een koppeling beveiligen?

 

[jkpieterse]

De formules worden alleen gewijzigd als zowel bron als doelbestand open staan in Excel. Mogelijk kan je je gebruikers leren dat ze als ze een kopie willen opslaan van je bronbestand ze eerst het doelbestand moeten sluiten. Je zou ook een macro in het bronbestand kunnen opnemen die in thisworkbook gebruik maakt van het BeforeSaveAs event. Als volgt:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oSourceWb As Workbook
    'Negeer fouten, bronbestand kan al gesloten zijn en dan geeft volgende code regel een fout.
    On Error Resume Next
    'Probeer of bronbestand open is
    Set oSourceWb = Workbooks("Bronbestand.xlsm")
    'Stop met negeren van fouten
    On Error GoTo 0
    'Is bronbestand open?
    If oSourceWb Is Nothing Then Exit Sub
    'ja, sluit bronbestand, sla wijzigingen altijd op
    oSourceWb.Close True
End Sub

 

[mariannef]

@ Sjon: de cellen zijn beveiligd, maar daar trekt Excel zich niks van aan.
@ Pieterse: de macro lijkt hoopvol, maar ik weet niet hoe ik deze moet inbouwen. Toevoegen mbv alt F11 doet niks, inbouwen in een macro ook niet. Kun je aangeven hoe ik deze moet toevoegen aan het bestand? Andere vraag is: het lijkt of de macro checkt of het bronbestand open is, moet dit niet het doelbestand zijn? Het gaat er toch om dat het doelbestand niet geopend mag zijn als het bronbestand ergens anders wordt opgeslagen?

En is er nu geen instelling in Excel die deze problemen kan tackelen? Ik werk trouwens met Windows 10 en Excel 2016.
Bij voorbaat dank weer!

 

[jkpieterse]

Oeps, je hebt gelijk. Bron, doel, allemaal één pot nat. Of toch niet

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim oTargetWb As Workbook
    'Negeer fouten, doelbestand kan al gesloten zijn en dan geeft volgende code regel een fout.
    On Error Resume Next
    'Probeer of doelbestand open is
    Set oSourceWb = Workbooks("Doelbestand.xlsm")
    'Stop met negeren van fouten
    On Error GoTo 0
    'Is doelbestand open?
    If oTargetWb Is Nothing Then Exit Sub
    'ja, sluit doelbestand, sla wijzigingen altijd op
    oTargetWb.Close True
End Sub

Code plakken in ThisWorkbook module van bronbestand, hetgeen je van te voren dient op te slaan als xlsm bestand terwijl doelbestand ook open is (anders worden de koppelingen dus niet bijgewerkt als je dat juist wel wilt).

 

[mariannef]

Beste Jan Karel,

Ontzettend bedankt, het werkt!Wel heb ik nog 2 kleine wijzigingen in de code aangebracht:
1. oSourceWb moest zijn oTargetWb
2. Ik heb en melding toegevoegd voor de gebruiker, zodat deze weet dat het doelbestand nu eerst gesloten moet worden
3. Ik heb in de laatste regel het woordje True verwijderd, zodat de gebruiker eerst nog een keus heeft of het doelbestand opgeslagen moet worden of niet.

Mijn code ziet er aldus als volgt uit:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim oTargetWb As Workbook
'Negeer fouten, doelbestand kan al gesloten zijn en dan geeft volgende code regel een fout.
On Error Resume Next
'Probeer of doelbestand open is
Set oTargetWb = Workbooks("Doelbestand.xlsx")
'Stop met negeren van fouten
On Error GoTo 0
'Is doelbestand open?
If oTargetWb Is Nothing Then Exit Sub
MsgBox "sluit nu eerst het doelbestand"
'ja, sluit doelbestand, kies of wijzigingen opgeslagen moeten worden
oTargetWb.Close

End Sub

Nogmaals bedankt, dit zal veel problemen gaan voorkomen! Jammer dat er geen optie in Excel bestaat om de formules sowieso vast te zetten, maar hiermee kan ik dus uit de voeten!

Groetjes
MarianneF