Rijen uit variabele bestanden invoegen in totaal bestand

[mjdejoode]

Goededag,

Ik heb een probleem met excel waar ik niet uitkom.
Voor een totaal overzicht moet ik gegevens uit andere documenten halen. Deze documenten zijn een kopie van een basisbestand. Als er een nieuw document wordt aangemaakt moet er via een knop 50 rijen uit het nieuwe document worden ingevoegd in het totaaldocument. Daarna moeten in een aantal cellen in het totaalblad een koppeling worden gemaakt met het nieuwe document.
Dit lukt me via "macro opnemen" wel voor het basisbestand, maar als ik de bestandsnaam verander dan werkt de macro niet meer. Waarschijnelijk omdat de macro als document waaruit hij kopieërt het basisbestand pakt. Hoe zorg ik ervoor dat ik VBA de documentnaam mee veranderd?

 

[mjdejoode]

Daarnaast nog 1 toevoeging? Hoe maak je dit mogelijk voor om in een totaal bestand te kopieren vanuit verschillende tabbladen uit een ander document?

Alvast bedankt!

Michael

 

[Wigi]

Ik heb een probleem met excel waar ik niet uitkom.
Voor een totaal overzicht moet ik gegevens uit andere documenten halen. Deze documenten zijn een kopie van een basisbestand. Als er een nieuw document wordt aangemaakt moet er via een knop 50 rijen uit het nieuwe document worden ingevoegd in het totaaldocument. Daarna moeten in een aantal cellen in het totaalblad een koppeling worden gemaakt met het nieuwe document.
Dit lukt me via "macro opnemen" wel voor het basisbestand, maar als ik de bestandsnaam verander dan werkt de macro niet meer. Waarschijnelijk omdat de macro als document waaruit hij kopieërt het basisbestand pakt. Hoe zorg ik ervoor dat ik VBA de documentnaam mee veranderd?

Goeiedag

Je kan gebruik maken van ThisWorkbook om te verwijzen naar het bestand dat de code bevat (het bronbestand neem ik aan).

 

[Wigi]

Daarnaast nog 1 toevoeging? Hoe maak je dit mogelijk voor om in een totaal bestand te kopieren vanuit verschillende tabbladen uit een ander document?

Voor elk stuk dat je wil kopiëren, moet je aangeven wat:

- de naam van het bronbestand is, + naam van de sheet, + juiste cellen
- de naam van het doelbestand is, + naam van de sheet, + juiste cellen

Evt. ergens met ThisWorkbook (zie vorige post).

Wigi

 

[mjdejoode]

Als ik ThisWorkbook gebruik krijg ik bij het uitvoeren van de macro een pop up scherm "waarde bijwerken: ThisWorkbook.name". Hij hoort toch meteen de naam van het bestand te pakken van waaruit de macro is gestart? of heb ik dit mis?

Alvast bedankt.

 

[Finch]

Post de code eens die je tot dusver hebt (tussen code tags liefst).

 

[mjdejoode]

Dit is de code die ik heb opgenomen:

Public Sub regels_koppelen_Click()

If MsgBox("Wilt u het project koppelen met het totaalblad?", vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
        Exit Sub
    Else

    Rows("11:60").Select
    Selection.Copy
    Windows("totaal.xls").Activate
    Rows("12:12").Select
    Selection.Insert Shift:=xlDown
    Range("A12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF('[project1.xls]Blad1'!R[-1]C="""","""",'[project1.xls]Blad1'!R[-1]C)"
    Range("A12").Select
    Selection.AutoFill Destination:=Range("A12:A61"), Type:=xlFillDefault
    Range("A12:A61").Select
    Selection.AutoFill Destination:=Range("A12:K61"), Type:=xlFillValues
    Range("L11").Select
    Selection.AutoFill Destination:=Range("L11:L60"), Type:=xlFillDefault
    Range("P11").Select
    Selection.AutoFill Destination:=Range("P11:P61"), Type:=xlFillDefault
    Range("M12").Select
    ActiveCell.FormulaR1C1 = _
        "='[project1.xls]Blad1'!R[-1]C"
    Range("M12").Select
    Selection.AutoFill Destination:=Range("M12:M61"), Type:=xlFillDefault
    Range("T12").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('[project1.xls]Blad1'!R[-1]C="""","""",'[project1.xls]Blad1'!R[-1]C)"
    Range("T12").Select
    Selection.AutoFill Destination:=Range("T12:T61"), Type:=xlFillDefault
    Range("T12:T61").Select
    Selection.AutoFill Destination:=Range("T12:U61"), Type:=xlFillDefault
    Windows("project1.xls").Activate
    Range("A1").Select
    
    End If
End Sub

Ik ben nog een beginner met VBA, maar ik neem aan dat ik "project1.xls" moet veranderen in ThisWorkbook.name. Alleen krijg ik dan een popupscherm waarbij staat dat ik de waarde moet bijwerken

 

[Finch]

Kijk eens even in deze thread, daar is een soortgelijk probleem behandeld.

http://www.helpmij.nl/forum/showthread.php?t=328622

 

[mjdejoode]

Hoi Finch,

Ik heb de naar die link gekeken en toegepast. Het enige waar ik mee zit is dat ik constant het pop up scherm "waarde bijwerken" krijg. Dit krijg ik bij de oplossing in de link en ook als ik thisworkbook gebruik. Ik denk dat het komt omdat ik een verwijzing moet maken in het activeworkbook naar het workbook van waaruit de macro wordt gestart. Ik heb het ook op een andere manier proberen op te lossen. Door in het totaal workbook een tabblad aan te maken waarin ik de macro de bestandsnaam, van het bronbestand, laat overnemen uit het bronbestand als "waarde". Daarna probeer ik de koppeling te maken door te verwijzen naar de cel waar het bestandsnaam staat in het totaalblad. Alleen krijg ik dan weer het pop up scherm "waarde bijwerken".
Dit is de formule die ik heb gebruikt:
=als('&Blad1!B2&'!A11="";"";'&Blad1!B2&'!A11)

In cel B2 op Blad1 staat het bestandsnaam van het bronbestand.

 

[Finch]

Naar mijn mening krijg je dat screen "waarden bijwerken" omdat je in je werkbladformules verwijzingen legt naar een ander bestand. Haal die verwijzingen eruit (bv. dmv het copieren van waarden ipv formules met linken naar het andere bestand) en deze message zou moeten verdwijnen.

 

[mjdejoode]

Hey Finch,

Ten eerste bedankt dat je meedenkt over dit probleem, alleen is het zo dat de bronbestanden bijgewerkt worden en het moet dan ook in het totaalblad worden bijgewerkt. En als ik het als waarde kopier dan gebeurt dat niet.

 

[Finch]

Zonder over je specifiek probleem te spreken en abstractie makend van uitzonderingen e.d. kan je een totaal bestand op 2 manieren opbouwen:

1) door met formules te linken naar andere excel bestanden (maw zonder VBA, maar met koppelingen)

2) dmv van bv. VBA code het totaal bestand te laten samenstellen met waarden uit de andere bestanden en formules binnen het totaal bestand (gebruik makend van VBA code, en deze code telkens opnieuw uitvoeren wanneer er waarden veranderen in de bronbestanden)

Elke methode heeft voor en nadelen, en uiteraard zijn ook voorbeelden waar je beide manieren gaat combineren.
Nu is het aan jou om te kiezen wat je wilt gebruiken binnen je project.

 

[mjdejoode]

Hey finch,

Wat je hierboven beschrijf dat begrijp ik. Alleen begrijp ik een werking in excel niet. Hieronder een voorbeeld:

Public Sub MyInputBox()

Dim MyInput As String
    MyInput = InputBox("Vul de bestandslokatie in", _
    "Bestandslokatie", "Bestandslokatie")

If MyInput = "" Then
Exit Sub
End If

MsgBox "De bestandslokatie is " & MyInput
End Sub

Als ik in het invulscherm dan de lokatie invul dan krijg ik in het Msgbox op de plaats van Myinput netjes de bestandslokatie te zien. Maar als ik dit toepas in bovenstaande code (vorige post) en Myinput plaats op de plaats waar daar project1 staat dan krijg ik het popupscherm waarde bijwerken. Hetzelfde heb ik bij thisworkbook en wbSource. Waarom lukt het wel in het Msgbox in niet in de code?

 

[Finch]

Ik heb het niet uitgetest maar hoop met onderstaande uitleg niet ver van de waarheid af te zitten.

Met je macro voer je op je worksheet een formule in die een waarde gaat opzoeken in een ander Excel bestand. Je Calculation Mode zal waarschijnlijk op automatisch staan (Excel standaard) en wanneer je dus in die cel de formule zet, wordt je sheet opnieuw berekent, en gaat hij dus op zoek naar de waarde in het andere bestand, en dat veroorzaakt dus je popup "waarden berekenen".

Oplossingen hiervoor:
* application.displayalerts bij het begin van de code op False zetten, en op het einde op true
* je calculatie methode in het begin op handmatig zetten en op het einde terug op automatisch

Een algemene opmerking: waarom maak je gebruik van een macro? Je kan de formules ook dadelijk in de cellen schrijven, aangezien je in deze opzet dat werk toch maar 1x moet doen. Of is het bronbestand telkens anders?

 

[mjdejoode]

Het bronbestand is inderdaad telkens anders en er moeten elke keer tussen de 30 en 250 regels gekoppeld worden. Als je dit elke keer handmatig moet koppelen dan neemt het veel tijd in beslag. Vandaar dat ik het via een macro wil doen. Ik zal proberen of het lukt via je laatste oplossing.

 

[mjdejoode]

Van een ding ben ik nu wel af, geen pop up scherm meer alleen wordt de formule dan niet aangepast en staat er MyInput op de plaats waar ik de bestandslokatie wil hebben staan. Waarom verandert hij "MyInput" wel in het Msgbox en waarom niet in de formule? Bij de formule zet hij gewoon MyInput als tekst erin inplaats van de bestandslokatie :S

Dit is hoe de code er nu uitziet

Public Sub regels_koppelen_Click()

If MsgBox("Wilt u het project koppelen met het totaalblad?", vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then
        Exit Sub
    Else

Dim MyInput As String
    MyInput = InputBox("Vul de bestandslokatie in", _
    "Bestandslokatie", "Bestandslokatie")

If MyInput = "" Then
Exit Sub
End If

MsgBox "De bestandslokatie is " & MyInput

    Rows("11:60").Select
    Selection.Copy
    Windows("totaal.xls").Activate
    Rows("12:12").Select
    Selection.Insert Shift:=xlDown
    Range("A12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=IF('& MyInput& Blad1'!R[-1]C="""","""",'& MyInput& Blad1'!R[-1]C)"
    Range("A12").Select
    Selection.AutoFill Destination:=Range("A12:A61"), Type:=xlFillDefault
    Range("A12:A61").Select
    Selection.AutoFill Destination:=Range("A12:K61"), Type:=xlFillValues
    Range("L11").Select
    Selection.AutoFill Destination:=Range("L11:L60"), Type:=xlFillDefault
    Range("P11").Select
    Selection.AutoFill Destination:=Range("P11:P61"), Type:=xlFillDefault
    Range("M12").Select
    ActiveCell.FormulaR1C1 = _
        "='& MyInput& Blad1'!R[-1]C"
    Range("M12").Select
    Selection.AutoFill Destination:=Range("M12:M61"), Type:=xlFillDefault
    Range("T12").Select
    ActiveCell.FormulaR1C1 = _
        "=IF('& MyInput& Blad1'!R[-1]C="""","""",'& MyInput& Blad1'!R[-1]C)"
    Range("T12").Select
    Selection.AutoFill Destination:=Range("T12:T61"), Type:=xlFillDefault
    Range("T12:T61").Select
    Selection.AutoFill Destination:=Range("T12:U61"), Type:=xlFillDefault
    Windows("project1.xls").Activate
    Range("A1").Select
    
    End If

Ik snap dat hij gaat zoeken naar een lokatie waar hij die cel aan moet koppelen, maar dat geef ik toch ook al in als "MyInput"?

 

[Finch]

Je code om de variabele naam in te verwerken is niet correct, dat is soms (zeker in het begin) wat prullen met aanhalingstekens en ampersanten, maar na een tijdje heb je de syntax door. Hieronder een voorbeeldje, probeer maar even en pas jouw code dan maar aan.

bestand = "Input1.xls"
ActiveCell.FormulaR1C1 = "='[" & bestand & "]Sheet1'!R1C1"

 

[mjdejoode]

Hij werkt!!!!

Dank je wel :thumb::thumb: