Het extraheren van gegevens uit een gesloten bestand in een andere werkmap is een veelvoorkomend verzoek van de meeste Excel-gebruikers. Ze willen gegevens uit gesloten bestanden ophalen of consolideren; dit is echter niet mogelijk. Er is een oplossing die dit probleem helpt oplossen. De oplossing is het gebruik van macrocode.
Vraag: Dagelijks besteed ik tijd aan het kopiëren van gegevens van het ene bestand naar het andere. Er zijn twee bestanden "Open.xls" & "Closed.xls" en ik wil gegevens kopiëren van "Closed.xls" naar "Open.xls" via VBA-code.
Belangrijke dingen om te weten:
- Het eerste is dat we de "Usedrange" van de gesloten werkmap moeten kennen, d.w.z. "Closed.xls" in de open werkmap voor b.v. "Open.xls"
- We kunnen de IF-functie gebruiken in Usedrange van de werkmap "Closed.xls" in de werkmap "Open.xls" en het zal de gegevens uit de werkmap "Closed.xls" halen
- Als de verwijzingscel leeg is, wordt #N/A geplaatst. Met behulp van de specialcells-methode kunt u vervolgens alle #N/A-fouten verwijderen en de formule wijzigen in waarden
Om te beginnen zullen we twee Excel-bestanden opslaan 1) Open.xls 2) Closed.xls in pad “D:\Test Folder”
Hieronder volgt de momentopname van de werkmap "Closed.xls":
Om automatisch gegevens van het bestand "Closed.xls" naar het bestand "Open.xls" op te slaan, moeten we de onderstaande stappen volgen om de VB-editor te starten
- Klik op het tabblad Ontwikkelaar
- Selecteer in de codegroep Visual Basic
- Kopieer de onderstaande code in ThisWorkbook (Closed.xls)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _ Cancel As Boolean) 'Put in the UsedRange Address of Sheet1 Closed.xls (this workbook)' --- Comment Sheet2.Cells(1, 1) = Sheet1.UsedRange.Address End Sub
- Om de gegevens in "Open.xls" te halen, kopieert u de volgende code in de standaardmodule:
Sub Importdata() Dim AreaAddress As String Sheet1.UsedRange.Clear Sheet1.Cells(1, 1) = "= 'D:\Test Folder\" & "[Closed.xls]Blad2'!RC" AreaAddress = Sheet1.Cells( 1, 1) Met Sheet1.Range(AreaAddress) .FormulaR1C1 = "=IF('D:\Test Folder\" & "[Closed.xls]Blad1'!RC="""",NA(),'D: \Test Folder\" & _ "[Closed.xls]Blad1'!RC)" Bij fout Hervatten volgende .SpecialCells(xlCellTypeFormulas, xlErrors).Clear bij fout GoTo 0 .Value = .Value End With End Sub
- Kopieer de volgende code in ThisWorkbook (Open.xls)
Private Sub Workbook_Open() Voer "Importdata" uit End Sub
Nu zijn de VBA-codes allemaal ingesteld; het enige wat we hoeven te doen is de bestandsnaam "Open.xls" openen. Hieronder volgt de momentopname van het bestand "Open.xls":
De code is niet beperkt tot celkopie van A1.usedrange; de code kiest het startbereik en werkt prima. De bestanden zijn beschikbaar om te downloaden en we raden u aan om het eens te proberen.
Conclusie: Met VBA-code kunnen we de gegevensextractietaak automatiseren zonder de bestemmingswerkmap te openen en we besparen onszelf van handmatig kopiëren en plakken.
Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook.
We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite