Verwijzen naar bestand met wisselende bestandsnaam via macro

[Gurtie]

Hallo allemaal,

Een macro zorgt ervoor dat uit het ene geopende excelbestand (bestelboekweek33) de data wordt overgezet naar een ander bestand (bestelboekbasis).
Uit de hele code heb ik even het relevante stuk gehaald:

Sub NieuwBestelboekAanmaken()

Workbooks.Open Filename:="C:\Excel\bestelboekbasis"
Sheets("FormulesBestellingen").Select
    Range("BF7").Select
    ActiveCell.FormulaR1C1 ="=(bestelboekweek33.xls")FormulesBestellingen!R7C81"
    Range("BG7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C82"
    Range("BH7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C83"
    Range("BF7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!RC81"
    Range("BG7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!RC82"
    Range("BH7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!RC83"
    Range("BF7:BH7").Select
    Selection.autofill Destination:=Range("BF7:BH218"), Type:=xlFillDefault
    Range("BF7:BH218").Select

Deze code werkt prima (ok ok, het kan vast korter dan ik het heb ), alleen wijzigt het weeknr elke week. In plaats van de verwijzingen naar "bestelboekweek33.xls" moet er iets komen als: "bestelboekweek" & Wknr

waarbij Wknr als volgt wordt gespecificeerd:

Dim Wknr

Wknr = Mid(ActiveWorkbook.Name, 15, 2)

Ik hoop dat de vraag duidelijk is. Ik heb op dit forum helaas weinig topics gezien die over zulk soort bestandsnaamperikelen gaan

 

[Wigi]

Haal eens eerst die ) uit de code tags, zodat alle code mooi in aparte venstertjes komt.

En moeten in het doelbestand de formules blijven staan, of enkel de waarden?

 

[Gurtie]

Het gaat om de waarden. Als ik namelijk de formules kopieer, verwijst het doelbestand niet meer naar het bronbestand maar naar zichzelf. Het moet een eenmalige kopie van de waarden worden.

 

[Wigi]

Ik zie in de code BF7, BG7 en BH7. En daarna nog een keer? Wat is daar de bedoeling van?

 

[Gurtie]

Dat vroeg ik me ook al af. Ik heb m namelijk opgenomen met de macro-recorder.
Ik heb wel, behalve het verwijzen van het ene naar het andere document, de "$" weggehaald uit de verwijzing, aangezien hij anders met het auto-fill naar dezelfde regel blijft verwijzen.Misschien dat die kolomnrs er om die reden dubbel in staan.
Maar op zich gaat het mij daar niet om, of heb je dat nodig voor de oplossing? Het enige waar het mij bij deze vraag om gaat is of ik de manier van verwijzen naar het bronbestand kan veranderen. Dus niet bestelboekweek33, maar "bestelboek" + Wknr oid. Of moet dan de hele code herschreven worden?

Wat er moet gebeuren is dat kolom BF, BG en BH (regel 7-218) van sheet Formulebestellingen uit bestand Bestelboekbasis een eenmalige verwijzing via een macro krijgen naar dezelfde kolommen in Bestelboekweek33-->Formulebestellingen. (met R1C1 reference style R7C81 R7C82 en R7C83).

Mocht dit niet duidelijk genoeg zijn, laat maar weten, dan zal ik een voorbeeldje toevoegen wanneer ik weer achter de pc zit. In elk geval alvast bedankt voor de moeite!

vriendelijke groet

edit: code met uitleg erbij (ook een aantal cellen zijn veranderd maar dat is voor de vraag niet belangrijk

Sub Voorbeeld()

         'aanroepen doelbestand en sheet
    Windows("bestelboekbasis.xls").Activate
    Sheets("FormulesBestellingen").Select
    
         'Cel CG7 = (verwijzing naar) bestelboekweek33-->formulebestellingen--> R7C81
    Range("CG7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C81"
         'uit CG7 wordt een "$" verwijderd
    Range("CG7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!RC81"
    
         'idem als bovenstaand maar dan voor CH7
    Range("CH7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C82"
    Range("CH7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!RC82"
    
         'idem als bovenstaand maar dan voor CI7
    Range("CI7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C83"
    Range("CI7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!RC83"
    
         'autofill voor bovenstaande 3 cellen vanaf 7 tot aan 218
    Range("CG7:CI7").Select
    Selection.autofill Destination:=Range("CG7:CI218"), Type:=xlFillDefault
    Range("CG7:CI218").Select
End Sub

Ik hoop dat het zo wat duidelijker is. En met het volgende topic: http://www.helpmij.nl/forum/showthread.php?t=246588&highlight=wknr
heb ik geprobeerd er uit te komen maar dat lukt me niet..

 

[Jan van Asseldonk]

Pas

    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C81"

als volgt aan

    ActiveCell.FormulaR1C1 = "=[bestelboekweek" & Mid(ActiveWorkbook.Name, 15, 2) & ".xls]FormulesBestellingen!R7C81"

Als je het weeknummer vaker nodig hebt kun je ook eerder WkNr bepalen en die gebruiken. Je krijgt dan

WkNr = Mid(ActiveWorkbook.Name, 15, 2)
ActiveCell.FormulaR1C1 = "=[bestelboekweek" & WkNr & ".xls]FormulesBestellingen!R7C81"

 

[Gurtie]

Ahhhhh, als ik het goed zie had ik dus alleen het &.xls nog moeten toevoegen. Met het ene stukje code moet dat er wel bij en met het andere niet Bedankt weer Wigi en Jan!

 

[Wigi]

Het is uiteindelijk niet zo heel ingewikkeld, maar je moet het weten.

Die formule in een cel is gewoon een stuk tekst, een string. Bv.

ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C82"

Die kan je gewoon uit elkaar halen zoals Jan al heeft getoond. Net als die 33 variabel gemaakt wordt, kan je in principe al de rest ook variabel maken. Alleen moet je zien dat de syntax klopt, dus & en " " enz.

Wigi

PS: en nu maar de code opkuisen wanneer je tijd hebt

Dit

Range("CI7").Select
    ActiveCell.FormulaR1C1 = "=[bestelboekweek33.xls]FormulesBestellingen!R7C83"

kan bv. als

Range("CI7").Formula = "=[bestelboekweek" & Mid(ActiveWorkbook.Name, 15, 2) & ".xls]FormulesBestellingen!R7C83"

Dus Select is over het algemeen niet nodig. Zie ook eerdere opmerking over cellen die 2 keer achtereen een andere formule krijgen.

 

[Gurtie]

Wigi, jij bent verslaafd hè? Heb je nog tips voor goeie excelboeken en dan vnl Visual Basic?
Ik heb nu 2 boeken uit de reeks van O'Reilly liggen, waaronder "Writing Excel Macro's with Visual Basic" van Steven Roman. Wat mij opvalt is dat ik in de praktijk meer heb aan het doorzoeken van dit forum dan aan die boeken