Gegevens bijwerken uit ander bestand

[jelle87]

Goedenmiddag,

Ik werk met verschillende bestellijsten, die geupdate worden vanuit 1 bronbestand (met duizenden regels en tientallen kolommen).

De grootste bestellijsten werken zichzelf bij (via verticaal zoeken), zodra ze geopend worden. Echter, tegenwoordig duurt dit minimaal een halve minuut of meer, omdat er zoveel gegevens bijgewerkt moeten worden.

Het bronbestand wordt 1x per dag bijgewerkt, maar de bestellijsten worden meerdere malen per dag geopend.

Is het ook mogelijk om de bestellijsten op de achtergrond bij te laten werken, zodra het bronbestand aangepast wordt ('s morgens)? Op die manier hoeven we niet meer te wachten, zodra de bestellijst geopend wordt.

Ik hoop dat het een beetje duidelijk is wat ik bedoel, alvast bedankt voor jullie hulp.

Groet,
Jelle

 

[jelle87]

Niemand ?

 

[BikerBill64]

Ooit gedacht aan access of een ander manier van database i.p.v. Excel?

 

[jelle87]

Nee, simpelweg omdat dat niet nodig is/was. De bestellijsten zijn niet perse een database op zichzelf, maar ze lezen uit het Excel bronbestand.

 

[BikerBill64]

Juist die bron leent zich als database. Bestellijsten kunnen dan een formulier zijn.
Excel is leuk als spreadsheet (rekenmachine) maar om data uit te wisselen zou ik toch echt eens aan access denken.

 

[jelle87]

Okay, misschien moet ik dat maar eens uit gaan zoeken dan. bedankt voor je hulp!

misschien heeft iemand anders nog een idee ?

 

[VenA]

Ik kan mij even geen beeld vormen bij jouw vraag. Kan je even een voorbeeldje plaatsen met hoe de bronlijst eruit ziet en hoe de grootste bestellijsten zichzelf bijwerken en hoe deze eruit zien?

 

[jelle87]

Zeker, die zet ik hier morgen of overmogen even neer voor je (vanaf mijn werk)!

 

[jelle87]

Goedenmorgen,

Zie hier 2 voorbeeld bestanden!

Het eerste bestand is een voorbeeld bestellijst, de 2e is een voorbeeld bronbestand.

Bekijk bijlage Gegevens_Bronbestand_voorbeeld.xlsx

Bekijk bijlage Bestellijst_Voorbeeld.xlsx

In de bestellijst zie je 7 artikelen staan. De artikelcode is leading, deze wordt gebruikt om cellen B5 tot en met I11 in te vullen met behulp van verticaal zoeken (heb ik niet ingevuld, heb puur de cijfers ingevuld als voorbeeld).

De echte bestellijst is zo’n 800 regels lang, het echte bronbestand ruim 3000 regels. Vandaar dat het nogal wat tijd inneemt, om alle gegevens bij te werken. Is dit te optimaliseren, zodat dit bijvoorbeeld elke dag uitgevoerd wordt, zodra het bronbestand bijgewerkt is?

Alvast bedankt!

 

[jelle87]

@VenA, heb je er nog naar kunnen kijken?

 

[VenA]

Ik denk het wel, Dus maar weer eens met een frisse blik gekeken maar ik begrijp het nog steeds niet. Maar misschien dat iemand anders je op weg kan helpen.

 

[popipipo]

Misschien een idee om slechts 1 bestand te gebruiken want ze zien er exact het zelfde uit.
Door middel van een extra kolom met daarin een kruisje kun je je bestelling aangeven.
Zet er daarna een filter op en kies het kruisje.

 

[jelle87]

Bedankt Willem, maar dat gaat helaas niet werken. Het bronbestand wordt via een query uit onze database gehaald (is gebouwd voor ons) en in een Excel bestand weergegeven. De bestellijsten zijn hier op aangepast, per type artikel.

Het bronbestand bestaat uit meer dan 3000 regels, terwijl de bestellijsten bestaan uit een paar regels (50, 100 of 800 de grootste).

Vandaar dat het logisch is dat de voorbeelden op elkaar lijken. Ik werk nu alle lijsten bij door middel van Verticaal Zoeken. Maar misschien is het ook mogelijk om via VBA dat te doen bijvorbeeld (als dat sneller is), maar daar weet ik niet zoveel van helaas.

 

[Warme bakkertje]

Dit is straight-forward en volledig gebaseerd op jouw voorbeeldbestanden.
Ik ga ervan uit dat de artikelnummers exact dezelfde zijn in beide bestanden alsook de opbouw van je tabellen de werkelijkheid weergeven.
Test het eens uit op een kopie van een bestellijst.

Sub tst()
    sn = ThisWorkbook.Sheets("Gegevens bronbestand").Cells(5, 1).CurrentRegion
    sp = Workbooks("Bestellijst_Voorbeeld.xlsx").Sheets("Blad1").Cells(4, 1).CurrentRegion
    For i = 2 To UBound(sn)
        For j = 2 To UBound(sp)
            If sp(j, 1) = sn(i, 1) Then
                For jj = 2 To 9
                    sp(j, jj) = sn(i, jj + 1)
                Next
            End If
        Next
    Next
    Workbooks("Bestellijst_Voorbeeld.xlsx").Sheets("Blad1").Cells(4, 1).Resize(UBound(sp, 1), UBound(sp, 2)) = sp
End Sub

ps: Beide bestanden moeten geopend zijn en de namen moeten exact overeenkomen met de namen v/d bestanden en de bladnamen gebruikt bij sn en sp

 

[jelle87]

Bedankt, dat ga ik eens proberen.

Echter, als beide lijsten open moeten staan, maakt het geen verschil of ik verticaal zoeken gebruik of bovenstaande. Met verticaal zoeken is hij dan namelijk ook bijgewerkt op het moment dat hij geopend wordt.

is zo'n code ook mogelijk om te maken, zonder dat het 'bronbestand' geopend moet worden?

 

[jelle87]

Of bijvoorbeeld een code te schrijven die het bronbestand automatisch opent, zodat het Excel bestand zich bij kan werken. Daarna mag hij uiteraard weer afgesloten worden.

Dit is slechts een gedachte hoor, ik heb geen idee of het echt mogelijk is

 

[Warme bakkertje]

Met de code heb je in je doelbestand (bestellijst) geen formules nodig, enkel harde waarden.
Maak maar eens een bestellijst met in kolom A enkel de artikelnummers en laat de code dan lopen.
Ik ben er meer dan zeker van dat zelfs je grootste bestellijst in enkele seconden bijgewerkt is.

 

[jelle87]

Okay, maar als ik dan nog steeds het bronbestand moet openen, zodra ik een bestellijst open, heeft het geen toegevoegde waarde. Ook met formules werkt de lijst binnen seconden bij, zodra het bronbestand geopend is.

Als het bronbestand niet geopend is, dan is het een ander verhaal en duurt het bijwerken veeeel langer.

 

[Warme bakkertje]

Dan open je het bronbestand bij het openen v/d bestellijst, leest de matrix in en sluit het bronbestand (Dit zie je niet eens gebeuren)
Daarna werk je de bestellijst bij met de ingelezen matrix.
Enig nadeel is dat er in elke bestellijst deze macro moet staan.
Er bestaat wel een methode om uit gesloten bestanden te lezen, maar weet niet of dit sneller zal zijn dan VLookup daarvoor heb ik testbestanden nodig met meerdere gegevens.

 

[jelle87]

Dus die regel zorgt ervoor dat het bronbestand automatisch geopend en gesloten wordt (op de achtergrond)? Of moet ik daarvoor nog iets apart instellen?