VBA voor dynamische cel referenties op gesloten workbooks

[Molovhic]

Hoi

Graag had ik hulp gehad op een topic die waarschijnlijk al ettelijke keren voorbij is gekomen.
Daar ikzelf niets van VBA ken, maar dit blijkbaar wel de enige oplossing is hiervoor..

Ik heb dus een "MAIN" workbook waar ik data uit andere file's haal..
Daar de file's als benaming de datum van de huidige dag meekrijgen zijn de links naar de file's dynamisch.
vb: 01042018.xls, 02042018.xls enz...

Op dit moment los ik dit op met INDEX MATCH en verander de datum in de formule manueel (veeeeel werk aan )
Ik dacht dit met de INDIRECT functie op te lossen, maar zoals geweten gaat deze NIET linken naar gesloten workbooks!

In bijlage zitten 3 file's:

Celreferenties: is dus als vb de main file, in kolom F staat de INDIRECT formule, hier zou dan VBA op toegepast moeten worden
zodat de values zichtbaar worden zonder de file's te openen (values zouden dan overeen moeten komen met kolom E)
01042018: vb file 1 waar de values in terug te vinden zijn
02042018: idem aan vb 1.

Mijn vraag is dus of iemand mij op weg kan helpen om de values te laten verschijnen, dit met VBA (waar ik zelf niets van ken..)
wel rekening houdend dat het jaar 365 dagen telt en dus mijn echte file ook al de file's eruit haalt. Moet er ook wel bij zeggen
dat de toekomstige file's niet bestaan.. deze worden op de dag zelf aangemaakt.

Bekijk bijlage celreferenties.xlsm
Bekijk bijlage 01042018.xlsm
Bekijk bijlage 02042018.xlsm

Alvast bedankt

 

[Gijsbert1]

Het zal wellicht makkelijker/beter kunnen, maar probeer dit eens:

Sub gijs()
Dim strFilename As String
Dim strPerc As String
Dim intRows As Integer
intRows = Range(Range("B1"), Range("B1").End(xlDown)).Rows.Count - 1
For i = 1 To intRows
    sFilename = "C:\Users\KXO0302A\Desktop\" & Format(Cells(i + 1, 1), "ddmmyyyy") & ".xlsm"
    With GetObject(sFilename)
        Set Range1 = .Sheets("Sheet1").Range("A1:B4")
        strPerc = Application.WorksheetFunction.VLookup(Range("C1").Value, Range1, 2, False)
        .Close
    End With
    Cells(i + 1, 6) = FormatPercent(strPerc, 0)
Next i
End Sub

 

[Molovhic]

Hoi Gijs

Dit werkt idd wel, maar ik merk dat hij de formule eruit haalt en de value alleen over houdt..
Als ik dan een rij wil doortrekken met een extra dag, neemt hij geen formule meer over.

Probleem is dat als je in C1 de naam wijzigt, hierdoor ook de waardes blijven staan van de vorige..

 

[Gijsbert1]

intRows = Range(Range("B1"), Range("B1").End(xlDown)).Rows.Count - 1

Deze regel leest het aantal regels wat moet worden meegenomen. (moet in kolom B wel iets ingevuld staan)

Het is juist de bedoeling om geen formule achter te laten als je met VBA werkt?!

 

[Gijsbert1]

Test maar even:

 

[Molovhic]

Geen idee of het de bedoeling is om formules weg te laten bij VBA, in ieder geval lukt het nu wel als ik de namen wijzig en de macro achteraf activeer.

Nog wel een vraag zodat ik verder kan testen:
stel dat ik in kolom G en H ook waardes wil laten zien (andere dan %) ik voeg deze ook toe in de aparte file's (kolom C en D) wat moet ik dan allemaal wijzigen?

        Set Range1 = .Sheets("Sheet1").Range("A1:D4") 'Wijzigen in D4 zoals dit?
     
    Cells(i + 1, 6) = FormatPercent(strPerc, 0) 'hoe kan ik hier ipv alleen kolom 6, ook kolom 7 en 8 laten invullen?

 

[Gijsbert1]

Hallo Molovhic,

Je dacht in de goede richting.

Zie onderstaand. Ik heb de wijzigingen in rood gemarkeerd en de toevoegingen blauw.
De code kan stukken korter/beter maar ik ben dan ook geen expert.

Sub gijs()
Dim strFilename As String
Dim [COLOR="#FF0000"]KolomB[/COLOR] As String[COLOR="#0000FF"], KolomC As String, KolomD As String[/COLOR]
Dim intRows As Integer
[COLOR="#0000FF"]On Error Resume Next[/COLOR]
intRows = Range(Range("B1"), Range("B1").End(xlDown)).Rows.Count - 1
For i = 1 To intRows
    sFilename = Application.ActiveWorkbook.Path & "\" & Format(Cells(i + 1, 1), "ddmmyyyy") & ".xlsm"
    With GetObject(sFilename)
        Set Range1 = .Sheets("Sheet1").Range("A1:[COLOR="#FF0000"]D[/COLOR]4")
        [COLOR="#FF0000"]KolomB[/COLOR] = Application.WorksheetFunction.VLookup(Range("C1").Value, Range1, 2, False)
[COLOR="#0000FF"]        KolomC = Application.WorksheetFunction.VLookup(Range("C1").Value, Range1, [COLOR="#FF0000"]3[/COLOR], False)
        KolomD = Application.WorksheetFunction.VLookup(Range("C1").Value, Range1,[COLOR="#FF0000"] 4[/COLOR], False)[/COLOR]
        .Close
    End With
    Cells(i + 1, [COLOR="#FF0000"]6[/COLOR]) = FormatPercent([COLOR="#FF0000"]KolomB[/COLOR], 0)
[COLOR="#0000FF"]    Cells(i + 1, [COLOR="#FF0000"]7[/COLOR]) = KolomC
    Cells(i + 1, [COLOR="#FF0000"]8[/COLOR]) = KolomD[/COLOR]
Next i
End Sub

 

[Molovhic]

Hoi

Ik probeer ook wel mee te denken hoor, het is niet dat ik stil zit.. programmeren is alleen mijn ding niet

Wel super dat je mij geholpen hebt, dat de code mss niet optimaal is kan geen kwaad, ik ga dit zowiezo
eens testen op de oorspronkelijke file's en kom er dan nog op terug

Heel fel bedankt tot nu toe!

Update:

Heb het kunnen verwerken in onze file's.
Echter staan deze op een gedeelde schijf van het bedrijf, als ik 365 dagen moet laten runnen voor 18 kolommen van waardes te voorzien, gaat hij alle file's op achtergrond openen en ook sluiten, ik merk dat dit enorm traag en omslachtig is.. helaas dus geen optie.

Wel veel bijgeleerd en dat is voor mij ook belangrijk natuurlijk.

Alvast bedankt voor de hulp!