• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

zoek/vervang in Excelbestand; waarden staan in tabel

Status
Niet open voor verdere reacties.

mariannevanh

Terugkerende gebruiker
Lid geworden
14 dec 2004
Berichten
2.085
Geruime tijd geleden stelde ik een vraag om een zoek-/vervangmacro voor een tekstbestand. Daarop heb ik een aantal mooie reacties gekregen. Dat topic is inmiddels gesloten. Nu doet zich een soortgelijke vraag voor, maar is mijn bron geen tekstbestand, maar een Excelbestand.
Wie is in staat een macro in elkaar te zetten (of een van de aangeboden macro's aan te passen) zodat er een reeks zoek-vervangopdrachten wordt uitgevoerd met als bron een Excelbestand, waarbij - analoog aan mijn eerste vraag - de te zoeken gegevens in kolom A staan en de te vervangen in kolom B.

Alvast hartelijk dank voor jullie moeite.
 
Probeer dit eens

Code:
Sub jvr()
  For Each it In Range("A1", Range("A" & Rows.Count).End(xlUp))
    ActiveSheet.UsedRange.Offset(, 2).Replace it, it.Offset(, 1)
  Next
End Sub
 
Ik vergat een belangrijk detail te melden:
Het bronbestand is een beveiligd bestand met macro's. Daar kan ik dus geen macro's aan toevoegen.
Het bestand met de twee kolommen (zoek en vervang) is dus een los bestand. Het bestand waarin de gegevens moeten worden gezocht/vervangen, moet dus (denk ik) worden geopend vanuit die macro.
 
Is van dat bestand alleen het VBA project beveiligd?
Of dat werkblad?
Of het document zelf?
 
Is van dat bestand alleen het VBA project beveiligd?
Of dat werkblad?
Of het document zelf?

Alleen het VBA-project is beveiligd. Het document is gewoon te bewerken.
(nooit geweten dat je de beveiliging op onderdelen kan specificeren)
 
Dat zijn inderdaad de onderdelen die je met een wachtwoord kan beveiligen.
Als alleen het VBA project is beveiligd kan wat je wilt denk ik wel met een stukje VB Script.
Plaats een voorbeeld met gegevens zoals je ze hebt in de kolommen A en B, dan zal ik eens kijken.
 
Laatst bewerkt:
Dit is het begin van de tabel met zoek (kolom A) en vervang (kolom B).
 

Bijlagen

  • zv-hm_20211008.xlsx
    8,6 KB · Weergaven: 16
Zet dit in een bestandje met de extensie .vbs
Code:
Dim xlDown
Dim xlWhole

xlDown  = -4121
xlWhole = 1

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("[COLOR="#FF0000"]F:\Helpmij\mariannevanh\zv-hm_20211008.xlsm[/COLOR]")

With objWorkbook.Sheets("Blad1")
    For i = 1 to .Range("A1").End(xlDown).Row
        msgbox(.Cells(i, 1).Value)
        .Cells.Replace .Cells(i, 1).Value, .Cells(i, 2).Value, xlWhole
    Next
End With

objWorkbook.Save
objExcel.Quit

Vervang het rode gedeelte door de correcte informatie.
Sla het bestandje op en dubbelklik er dan op om hem uit te voeren.
Die msgbox kan er ook uit als je wil.

NB:
Als het ergens fout gaat en je dus een foutmelding krijgt heb je kans dat er een open Excel object aanwezig blijft.
Deze kan je middels de Taakmanager dan afschieten.
Om dit te voorkomen is er e.e.a. aan error checking nodig dat ik er nog maar even niet ingebouwd heb.
 
Laatst bewerkt:
@edmoor, dit lijkt te gaan werken. Dankjewel!
Ik moet echter bekennen dat ik nog iets over het hoofd gezien heb, wat ik had moeten melden. Ik bied om te beginnen daarvoor mijn excuses aan.
- "Blad1" bestaat niet, maar heet anders. Dan kan ik wijzigen in regel 9.
- het Excel-document waarin moet worden gezocht/vervangen, bestaat uit meer werkbladen, er moet dus gezocht/vervangen worden in de gehele werkmap.

Wat is de betekenis van de waarde -4121 voor xlDown? Zegt dat iets over het aantal regels dat worden gezocht/vervangen. Mijn document bevat er véél meer...
 
De xl variabelen bestaan niet in VBScript maar om het gelijk te houden aan VBA heb ik ze er wel in geïntroduceerd, voor het gemak.
Eigenlijk ben je dan dus gewoon in VBA bezig maar dan binnen VBScript.
Zo kan je ook een aanpassing doen voor het vervangen op alle werkbladen in plaats van op 1 werkblad.
Het aantal te verwerken regels komt van: .Range("A1").End(xlDown).Row
 
De xl variabelen bestaan niet in VBScript maar om het gelijk te houden aan VBA heb ik ze er wel in geïntroduceerd, voor het gemak.
Eigenlijk ben je dan dus gewoon in VBA bezig maar dan binnen VBScript.
Zo kan je ook een aanpassing doen voor het vervangen op alle werkbladen in plaats van op 1 werkblad.
Het aantal te verwerken regels komt van: .Range("A1").End(xlDown).Row

Begrijp ik het goed dat het script - omdat het VBScript is en niet VBA - niet hoeft te worden gewijzigd en het dus al vanzelf in de hele werkmap zoekt/vervangt? Maar dan loopt ie toch vast omdat "Blad1" niet bestaat; het heet anders.
Is de screenshot (zie bijlage) de zgn. msgbox en kan ik die negeren (of de vbs aanpassen door regel 11 te verwijderen)?
Ik zie geen nieuw weggeschreven bestand. Het bestand op regel 7 heeft nog steeds de originele omvang, datum en tijd.
 

Bijlagen

  • Schermafbeelding 2021-10-08 224907.png
    Schermafbeelding 2021-10-08 224907.png
    5,1 KB · Weergaven: 14
Die msgbox zegt alleen dat 'ie geen waarde heeft gevonden die vervangen moet worden.
Ik wil er best wat meer aan doen maar dan heb ik een beter voorbeeld bestand nodig.
 
Ik heb een poging ondernomen een beter voorbeeldbestand te maken.
Het originele bestand met macro's is echter een commercieel product, dus ik lever alleen een werkblad aan met verschillende bladen.
In elk blad (behalve Menu) is kolom A een kolom met nummers (omgeven door "@" en beginnend met een letter.
Het geheel is de bron van een stamboombestand (gedcom-bestand) met 110.000+ personen (dus '1 INDI' heeft meer dan 110.000 rijen met gegevens), @I1@ tot en met (ver boven de) @I110000@ omdat er een heleboel ontbrekende nummers zijn.
Maar het bestand blijkt te groot om als bijlage op dit forum te plaatsen...
 
Het echte bestand is uiteraard ook geen voorbeeld bestand.
Dus elk blad bevat in kolom A gegevens die in dat werkblad moeten worden vervangen door wat er in kolom B staat?
 
Ik begrijp wel dat het echte bestand geen voorbeeld bestand is.
In het Excelbestand (beveiligd met macro's) staan veel rijen/kolommen en in veel daarvan komen @X0000@-referenties voor.
De te zoeken/vervangen referenties staan uiteindelijk in het tweede bestand.
Omdat ik uitging van een macro in een Excel-werkblad heb ik gekozen voor een separaat bestand, maar omdat je gebruik maakt van een VBScript (ipv VBA die je niet kunt plaatsen op een beveiligd werkblad) mogen de zoek/vervang-gegevens in plaats van in een separaat bestand ook wel op een extra tabblad staan.
 
Ik zal het bestand dat je plaatste bekijken en er eventueel vragen over stellen.
Maar niet meer nu... ;)
 
Groot gelijk, tijd om te slapen, andere dingen te doen, enzovoorts
 
Klopt dit?
Dus elk blad bevat in kolom A gegevens die in dat werkblad moeten worden vervangen door wat er in kolom B staat?
 
Nu ik het goed overlees, geef ik toe dat het wat verwarrend staat. Ik doe een poging het goed te omschrijven.
Er is een bestand met data (het zgn. GedTool-bestand) waarin verschillende tabbladen staan en er is een bestand zv.xlsx met zoek/vervang-gegevens, een simpel Excel-bestand met slechts 2 kolommen.
In het GedTool-bestand moeten gegevens worden gezocht (kolom A uit zv.xlsx) en vervangen worden (kolom B uit zv.xlsx).
In #16 schreef ik (met andere woorden) al dat zv.xlsx ook mag worden toegevoegd als een nieuw tabblad aan het GedTool-bestand omdat je toch werkt met VBScript.
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan