Dynamische verwijzing bestandsnaam in macro

[gvanwijk]

Ik heb 2 Excelbestanden openstaan en wil data vanaf het hoofdbestand ("Databestand") via een macro kopiëren naar een ander openstaand bestand ("Ontwerp-1"). Dat gaat prima via onderstaande macro (die is opgenomen en kan wellicht korter).

Maar nu de vraag: het bestand "Ontwerp-1" wordt steeds gekopieerd en wordt dan "Ontwerp-2" genoemd, dat wordt weer gekopieerd en "Ontwerp-3" genoemd etc. De macro uit "Ontwerp-1" werkt dan natuurlijk niet in "Ontwerp-2". Ik kan in de macro natuurlijk overal waar Ontwerp-1 staat -> Ontwerp-2 veranderen. Maar ik vroeg me af of er een dynamische bestandsverwijzing mogelijk is in de macro. Zo ja: hoe zou onderstaande code dan worden (evt korter mag ook)?
En: de macro staat in het bestand "Ontwerp-1" (niet in "Databestand").

P.S. in werkelijkheid komt "Ontwerp-1" natuurlijk vaker voor in de macro, vandaar mijn vraag!

Sub Dataextraktie()

    Windows("Databestand.xlsm").Activate
    Range("B2:D12").Select
    Selection.Copy
    Application.WindowState = xlNormal
    Windows("[COLOR="#FF0000"]Ontwerp-1.xlsm[/COLOR]").Activate
    Range("B3:D9").Select
    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("B3").Select
End Sub

 

[LucHeyndrickx]

Ik heb je macro niet getest.
Ik ga me ook niet bezighouden met het opschonen van je macro, omdat ik echt wel de indruk heb dat je constructie (telkens een nieuw bestand maken) absoluut niet efficiënt is.
Toch een antwoord op je vraag: een String (een tekst dus tussen aanhalingstekens), kan je aanvullen met een variabele. En dat is dus je vraag.

Sub Dataextraktie()

    Windows("Databestand.xlsm").Activate
    Range("B2:D12").Select
    Selection.Copy
    Application.WindowState = xlNormal
    Windows("Ontwerp-[COLOR="#FF0000"]" & InputBox("Geef een nummer") & "[/COLOR].xlsm").Activate
    Range("B3:D9").Select
    Selection.PasteSpecial Paste:=xlPasteAllExceptBorders, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("B3").Select
End Sub

Overigens... je kan ook VBA code aanpassen met VBA. Dat heb ik ooit in een ver verleden wel 's gedaan, maar sindsdien nooit meer.
Je zou die MsgBox kunnen vervangen door een verwijzing naar een cel waar je telkens het nummer verhoogt.

 

[gvanwijk]

Oke dank je, ga ik eens proberen, deze optie kende ik nog niet. Als iemand nog een optie weet om de VBA dynamisch aan te passen.

 

[LucHeyndrickx]

Eén van de eerste dingen die je moet leren bij het programmeren is het gebruik van Variabelen.

Dim tekstje as String ' een tekst dus
Dim iGetal as Integer ' een getal tussen -32768 en 32767

Een volledige lijst vind je hier: https://worksheetsvba.com/nl/vba-variabelen-declareren

Deze variabelen kan je vervolgens in je code gebruiken... en de naam zegt het al zelf: de waarde ervan staat niet vast, maar bepaal je bij het gebruik van de code:

iGetal = InputBox("Typ hier een getal in")  'Het getal wordt gevraagd
iGetal = iGetal +1 'Het getal wordt met 1 eenheid verhoogd
Dim i as Integer
For i = 1 to 5
iGetal = iGetal + 1
MsgBox iGetal
Next 'Je krijgt 5 x het getal met ééntje verhoogd.

Ik merk dus dat ik bij mijn vorige code een vergissen gemaakt heb: MsgBox moet eigenlijk InputBox zijn... ik pas dit meteen aan.

 

[HSV]

Maak gebruik van:
Application.Inputbox met als Type een 1.
Zo kan je alleen maar een getal invoeren.

 

[edmoor]

De macro staat dus in het bestand met het variabele nummer?
Dan heb je dat nummer helemaal niet nodig om terug te gaan naar dat bestand.
Dit is dan voldoende:

ThisWorkbook.Activate

Al zou ik het zelf totaal anders doen.
Dus zonder Select, Activate en Selection.

 

[gvanwijk]

ThisWorkbook.Activate... Ja zo loopt ie ook goed natuurlijk, stom.

@LucHeyndrickx De tip met inputbox vind ik verder wel fijn om te weten, komt vast een keer van pas!

@Edmoor Je zegt dat je het zonder Activate, Selection en Select zou doen (dan kortere code denk ik)
Hoe zou onderstaande deel van de code (een opgenomen macro ) dan worden?

Windows("1. Assortiment.xlsm").[COLOR="#FF0000"]Activate[/COLOR]
    Range("Tabel15[[mrt]:[jul]]").[COLOR="#FF0000"]Select[/COLOR]
    [COLOR="#FF0000"]Selection[/COLOR].Copy
    Range("R4:V33").Select

 

[HSV]

@edmoor "Help"

 

[LucHeyndrickx]

Bij opgenomen macro's kan je inderdaad de Select gewoon verwijderen.
Je zal dan iets als dit krijgen:

Range("Tabel15[[mrt]:[jul]]").Copy

Ook de laatste regel dus: waarin je opnieuw iets selecteert... en voor wat???? Gewoon weglaten.

 

[gvanwijk]

@edmoor "Help"

:thumb: