verwijzigingen naar andere werkbladen automatisch aanpassen

[Julie 0]

Ik heb ongeveer hetzelfde probleem als signorfranko een tijdje geleden;
De gegevens die moeten overgenomen worden in de groslijst bevinden zich echter niet op een ander werkblad, maar in een ander bestandje.

Ik heb dus 1 algemeen bestand gemaakt en dan verschillende gedetailleerde bestandjes in afzonderlijke mapjes.
In het algemeen bestand zouden bepaalde gegevens moeten overgenomen worden vanuit de gedetailleerde bestandjes.

Dat lukt, behalve moet ik, net als signorfranko, elke verwijzing naar de cel van het gedetailleerde bestandje aanpassen.
Als je bedenkt dat ik ondertussen zo bijna 200 gedetailleerde bestandjes heb, is dit een heel werkje.

De detailbestandjes hebben als naam 0001, 0002, ... Ik dacht dat dit het eenvoudiger zou maken, maar ik moet dus nog steeds de verwijzingen aanpassen. Ik kopieer dan de vorige celverwijzing en maak van 0001 --> 0002. Maar ook dit is een heel werkje.

Moest iemand een oplossing vinden, zou ik dit heel tof vinden!

Hopelijk is de uitleg een beetje duidelijk ?

 

[richard1970]

Hoi Julie,

Wat jij wilt is mogelijk met de functie INDIRECT, maar die heeft als nadeel dat de verbinding met het andere bestand vervalt als dat bestand niet open is.
Watik eigenlijk zeggen wil is dat het zo niet gaat lukken.
Het blijft dus bij zoeken en vervangen.

Groeten,
Richard

 

[Julie 0]

Bedankt voor je vlugge reactie Richard!

Ik doe wel altijd beide bestanden open.
Dus wanneer ik gegevens invoer in het detailbestand, staat mijn algemeen bestand ook open. Lukt het dan niet?

Of heb ik verkeerd begrepen wat je bedoelt?

Ik ben al een tijdje alle formules manueel aan het aanpassen. Maar toen las ik de vraag van signorfranko en voor hem bestond er eigenljk een heel gemakkelijke oplossing. Maar onze problemen zijn misschien niet echt vergelijkbaar?

Ik vind het moeilijk te geloven dat dit niet zou lukken, omdat Excel net zo uitgebreid is.

Ik zal toch eens proberen met de INDIRECT functie, misschien lukt het?

Bedankt! Julie

 

[MJONK]

Als beide bestanden openstaan lukt het met de indirect functie.


Hier een stukje van de site van een forumlid: excellerend.nl met de uitleg van indirect. Als het niet lukt kun je één van je huidige formules als voorbeeld posten en kunnen we helpen.

INDIRECT
De functie INDIRECT maakt argumenten variabel door tekstdelen of delen van een functie aan elkaar vast te plakken. In ons geval gaan we het rode argument in:
=VERT.ZOEKEN(A1;auto!A1:C25;2;0) variabel maken met INDIRECT.
Om delen aan elkaar vast te plakken wordt het &-teken (ampersand) gebruikt. De tekst moet net als bij andere argumenten tussen dubbele quotjes gezet worden. In cel A2 kan gekozen worden uit 'auto' en 'fiets'. Het bereik blijft A1:C25. De uiteindelijke functie ziet er zo uit:
=VERT.ZOEKEN(A1;INDIRECT(A2&"!A1:C25");2;0).
Werkbladnaam met spaties of cijfers
Wanneer de naam van het werkblad uit cijfers bestaat of een spatie bevat, wordt de syntax van de functie INDIRECT iets ingewikkelder.
Stel dat het bestand niet uit de drie tabbladen 'auto', 'fiets' en 'rapportage' bestaat, maar uit '2007', '2008' en 'rapportage. En stel dat de functie INDIRECT gebruikt moet worden om een rapportage variabel te maken over jaargangen heen. De oorspronkelijke functie zal er dan zo uit zien:
=VERT.ZOEKEN(A1;'2008'!A1:C25;2;0). Het jaartal staat tussen enkele quotjes! Ook een naam met een spatie erin zoals 'Regio Zuid' krijgt in de functie enkele quotjes er omheen. De functie INDIRECT dient die enkele quotjes ook te bevatten.
De uiteindelijke functie ziet er dan zo uit:
=VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'"&"!A1:C25");2;0). Voor en na de A2 staan tussen dubbele quotjes een enkele quote die met z'n drieën aan elkaar geplakt worden, tezamen met het bereik "!A1:C25". Ditzelfde kan iets korter opgeschreven worden door het tweede enkele quotje mee te nemen met het deel '!A1:C25'.:
=VERT.ZOEKEN(A1;INDIRECT("'"&A2&"'!A1:C25");2;0)
Als nu het bereik op werkblad 2007 hetzelfde is als 2008, dan is met deze functie de rapportage variabel gemaakt.

Nadelen INDIRECT
De functie INDIRECT voegt vele mogelijkheden toe in combinatie met andere functies. Maar het heeft wel nadelen. Als de functie VERT.ZOEKEN verwijst naar A1:C25, en in dat bereik wordt een rij ingevoegd, dan verandert het bereik in de functie naar A1:C26. Bij de functie INDIRECT blijft het bereik onveranderd en dient dus handmatig aangepast te worden! Of via een andere functie zoals AANTALARG.
Ook is het mogelijk om via INDIRECT te verwijzen naar een ander bestand. Echter, zodra het bronbestand waarnaar verwezen wordt gesloten wordt, raakt Excel de verbinding kwijt, en alle functies tonen de foutmelding #VERW!.

 

[Julie 0]

Oei, dit is dan toch niet echt de bedoeling.

Het zou wel moeten blijven staan in het algemene bestand. Dus als ik de gegevens ingeef, doe ik beide bestanden open.
Maar de hele bedoeling is, dan de algemene informatie overgenomen wordt in het algemeen bestand en daar dan ook wel blijft staan.

Toch heel fel bedankt!

Julie

 

[Julie 0]

Misschien nog even dit:

De detailmapjes waar ik de gegevens uithaal hebben als naam 0001,0002,0003,...
dit staat ook in kolom A waarachter de gegevens volgen

dus:
A In kolom B, C, D, E moeten gegevens komen die gehaald worden uit een detailmapje met naam 0001 (0002,...)-0001 waarin een excelbestandje opgeslaan is met naam 0001 (0002,...)
0002
0003

 

[MJONK]

Post eens een klein voorbeeldbestand, ik denk dat dit wel op te lossen is. En inderdaad met indirect.

 

[Roncancio]

Misschien nog even dit:

De detailmapjes waar ik de gegevens uithaal hebben als naam 0001,0002,0003,...
dit staat ook in kolom A waarachter de gegevens volgen

dus:
A In kolom B, C, D, E moeten gegevens komen die gehaald worden uit een detailmapje met naam 0001 (0002,...)-0001 waarin een excelbestandje opgeslaan is met naam 0001 (0002,...)
0002
0003

Moeten de gegevens in de bestanden 0001,0002, etc worden gezocht, of staan de gegevens op een vaste plek?
In dat laatste geval zou je zelfs zonder de bestanden te openen de gegevens op kunnen halen.

Met vriendelijke groet,


Roncancio

 

[Julie 0]

Dag Roncancio,

De gegevens staan op een vaste plek.
De gegevens die bv in kolom B achter de 0001, 0002, ... moeten komen staan in het detailmapjes altijd onder D3.

Kan je mij uitleggen hoe ik de formules gemakkelijk kan overnemen? Is dat dan nog met de INDIRECT-functie?

Bedankt!

Julie

 

[Julie 0]

Hier de bestandjes. In het origineel staan natuurlijk veel meer gegevens dan deze vereenvoudigde versie.

Hopelijk vind je de oplossing :thumb:

 

[Roncancio]

Hier de bestandjes. In het origineel staan natuurlijk veel meer gegevens dan deze vereenvoudigde versie.

Hopelijk vind je de oplossing :thumb:

Afgaande op je bericht, haalt onderstaande code de gegevens op van de bestanden zonder deze te openen.
LET OP: het kan zijn dat de padverwijzing nog aangepast moet worden naar jouw omgeving.
Met padverwijzing bedoel ik C:\....

Sub Gegevens()
Dim lRij As Long
    lRij = 2
    While Range("A" & lRij).Value <> ""
        For kolom = 2 To 5
            Cells(lRij, kolom).Value = ExecuteExcel4Macro("'C:\[" & Range("A" & lRij) & ".xlsx]Blad1'!R" & kolom + 1 & "C2")
        Next
        lRij = lRij + 1
    Wend
End Sub

Als je nog vragen of problemen hebt, dan hoor ik het graag.

Met vriendelijke groet,


Roncancio

 

[Julie 0]

Roncancio,

Ik begrijp niet helemaal wat ik met de code moet doen. Ik heb vroeger een basis excel op school gehad, maar dat is ondertussen ook alweer een tijdje geleden.

Ik heb (volgens mij) de juiste padnaam ingegeven maar ik krijg toch dat in de formule een fout zit. De help duidt "Value" aan?

Kan je misschien uitleggen wat ik precies met de code moet doen?

Mijn excuses voor het ongemak!

En toch al bedankt!!

 

[Roncancio]

Roncancio,

Ik begrijp niet helemaal wat ik met de code moet doen. Ik heb vroeger een basis excel op school gehad, maar dat is ondertussen ook alweer een tijdje geleden.

Ik heb (volgens mij) de juiste padnaam ingegeven maar ik krijg toch dat in de formule een fout zit. De help duidt "Value" aan?

Kan je misschien uitleggen wat ik precies met de code moet doen?

Mijn excuses voor het ongemak!

En toch al bedankt!!

Wordt de fout in de cel aangegeven?
Wellicht heb je een fout gemaakt in het invoeren van het juiste pad.

De code dien je in de VBE te plaaten.
Toets in ALT-F11, voeg een module in en plaats daar de code.
Mocht het niet lukken dan hoor ik het wel.

Met vriendelijke groet,


Roncancio

 

[Julie 0]

Roncancio,

Ik ben zover dat ik een module heb aangemaakt, hierin heb ik jou code gekopieerd.
Ik heb achter de C:\ de juiste locatie van het eerste detailbestandje (0001) ingegeven. Dan heb ik voor de.xlsx opnieuw 0001 ingegeven. Heb ik dan alle nodige gegevens ingegeven?

Ik krijg nog steeds een foutmelding wanneer ik in de VBE op F5 druk (=invoeren in bestand, heb ik in de help gelezen)

Zijn er misschien nog andere gegevens in jou code die ik zou moeten aanpassen of aanvullen?

Bedankt voor al je moeite !

 

[Roncancio]

Roncancio,

Ik ben zover dat ik een module heb aangemaakt, hierin heb ik jou code gekopieerd.
Ik heb achter de C:\ de juiste locatie van het eerste detailbestandje (0001) ingegeven. Dan heb ik voor de.xlsx opnieuw 0001 ingegeven. Heb ik dan alle nodige gegevens ingegeven?

Ik krijg nog steeds een foutmelding wanneer ik in de VBE op F5 druk (=invoeren in bestand, heb ik in de help gelezen)

Zijn er misschien nog andere gegevens in jou code die ik zou moeten aanpassen of aanvullen?

Bedankt voor al je moeite !

De code kijkt naar de naam die in de A-kolom staat.
Kan je anders het pad geven of het bestand (zonder gevoelige informatie) bijsluiten, dan kan ik kijken wat er aan de hand is.

Met vriendelijke groet,


Roncancio

 

[Julie 0]

Ik krijg het niet doorgestuurd. Er staat een rood uitroepteken wanneer ik het wil uploaden.
Bij het bestand zelf staat ook een uitroepteken op het icoontje?

 

[Roncancio]

Ik krijg het niet doorgestuurd. Er staat een rood uitroepteken wanneer ik het wil uploaden.
Bij het bestand zelf staat ook een uitroepteken op het icoontje?

Dat komt omdat er VBA-code inzit.
Excel 2003 met VBA kan je zo plaatsen maar Excel 2007 met code niet.
Wat je kan doen is het bestand in een .ZIP-bestand, .RAR-bestand of gecomprimeerde map plaatsen.

Met vriendelijke groet,


Roncancio

 

[Julie 0]

Ik heb het opgeslagen als 97-2003 werkmap, is dit ook goed?

Zo lukte het wel

 

[Roncancio]

Ik heb het opgeslagen als 97-2003 werkmap, is dit ook goed?

Van mij wel maar ik zie geen code.
Wat is het pad van de bestanden?

Met vriendelijke groet,


Roncancio

 

[Julie 0]

='C:\Users\Jenny Smeers\Desktop\Test Dossiers\0001\[0001.xlsx]Blad1'!$B$3

dossiertje 2 is dan map 0002 en bestand 0002.xlsx