macro om data uit ander document op te halen

[swanwil]

Hallo,
Momenteel haal ik in onze stuklijsten de order data op via vertikaal zoeken formules uit het (gesloten) document Order_data.xlsx (en is beveiligd met wachtwoord "test")
Nadeel hiervan is dat het originele document 223000 regels bevat.
Hierdoor wordt mijn stuklijst-bestand flink groter en trager bij openen doordat op de achtergrond de order-data ook in dit bestand gekoppeld wordt.
Daarom zou ik dit willen oplossen door de data via een macro op te halen en weg te schrijven als tekst i.p.v. de formules (zoals in kolom D weergegeven). Hierdoor zou ik geen koppeling met het order-data bestand hebben en lichtere stuklijsten krijgen.
De macro zou dan eerst moeten testen of op het tabblad wat begint met "Voorblad" op cel C4 een ordernummer van 10 cijfers is ingevuld. Vervolgens zou de data dan als waarde weggeschreven moeten worden op de plaats van de formules C6 t/m C10
Ter verduidelijking heb ik 2 voorbeeldbestanden toegevoegd.

Kan iemand me hiermee helpen? Ik ben redelijk nieuw in VBA-macro’s maken.
Alvast bedankt

Met vriendelijke groet, swanwil

 

[edmoor]

Heb je het al eens getest met Automatisch berekenen uitgeschakeld?
Dus op Handmatig berekenen gezet?
Dan laat je de berekeningen pas doen op het moment dat het nodig is.

 

[swanwil]

Automatisch herberekenen staat standaard uit bij ons.
Ik begrijp niet goed wat je hiermee bedoeld ...?
Wat ik wil bereiken is dat na de macro gerund heeft er geen formules staan die verwijzen naar het (gesloten) Order_data document, maar dat alleen de waardes ingevuld staan.
Door die formules blijft het document op de achtergrond gekoppeld. Te zien via Gegevens Koppelingen bewerken

Groeten swanwil

 

[edmoor]

Ik bedoel dat wanneer je dat uit zet het openen van die documenten een stuk sneller zal gaan.

 

[swanwil]

Ah, Okay.
Daarom staat dit bij ons al uit inderdaad

 

[jkpieterse]

Je zou het ook zo kunnen doen. (even de Orders query bewerken zodat het naar de juiste locatie wijst).
Enig nadeel: de verversing moet je met de hand doen na het invoeren van het ordernummer.
Maar je kan berekenen nu wel weer op auto zetten.

 

[JEC.]

Optie met VBA. Zoals je ziet moet je het pad nog even aanpassen naar het juiste.
De data wordt opgehaald zodra je het ordernummer intikt in cel C4

Private Sub Worksheet_Change(ByVal Target As Range)
 Dim xp, x01, x02
 If Target.Count > 1 Then Exit Sub
 If Target.Address(0, 0) = "C4" And Target.Value <> "" Then
    xp = "C:\Users\xxx\Downloads\Order_data (1).xlsx"
    x01 = "SELECT * FROM `Order$` WHERE Order_nr = " & [c4]
    x02 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & xp & ";Extended Properties=""Excel 12.0"""
 
    With CreateObject("ADODB.recordset")
     .Open x01, x02
      Range("B7").CopyFromRecordset .DataSource
     .Close
    End With
 End If
End Sub

 

[JEC.]

@Jkp,

Gezien de "named range" van het ordernummer altijd gelijk is, heb je geen aparte functie nodig

let
    source = Excel.Workbook(File.Contents("C:\Users\XXX\Downloads\Order_data (1).xlsx"), null, true),
    sht = source{[Item="Order",Kind="Sheet"]}[Data],
    headers = Table.PromoteHeaders(sht, [PromoteAllScalars=true]),
    oNum = Excel.CurrentWorkbook(){[Name="Ordernummer"]}[Content]{0}[Column1],
    fltr = Table.SelectRows(headers, each ([Order_nr] = oNum))
in
    fltr

 

[swanwil]

Dank voor de reacties!
@jkpieterse - Ik wil eigenlijk geen gebruik maken van queries omdat ik die dan in iedere stuklijst moet kopiëren en niet iedereen bekend is met queries

@JEC - Dit gaat de goede richting in. Wat ik vergeten ben om te vermelden is dat wij gebruik maken van een bestand wat altijd op de achtergrond geopend wordt in Excel waar we diverse macro's in hebben zitten, zodat iedereen die macro's kan gebruiken. Nu wil ik hier een macro in toevoegen die bovenstaande uitvoert. Als mensen dan die macro (handmatig) starten, dat dan de data op de betreffende velden ingevuld worden.
Ik zou graag aan de layout niks willen wijzigen omdat deze zo al jaren gebruikt wordt. Zie screenshot voor alle data die binnengehaald wordt

 

[jkpieterse]

@JEC: dat weet ik, maar ik gebruik named ranges vaak en de functie maakt e.e.a. wat beter leesbaar.

@swanwil: Of je nu die query tabel moet kopieren of een setje formules, maakt in principe natuurlijk weinig verschil

 

[swanwil]

Kan er dan misschien een VBA gemaakt worden om 1 waarde weg te schrijven als tekst (bijv. van Dealer) in cel C7 als er gezocht wordt naar het ordernummer in C4 in het document stuklijst en dat dan de waarde van dealer van het betreffende ordernummer opgehaald wordt uit het document Order-data?

 

[swanwil]

Kan iemand me vooruit helpen? Ik kom er zelf niet uit

 

[snb]

Haal iedere vorm van 'beveiliging' uit beide bestanden.
Sla beide bestanden op in dezelfde directory.

Gebruik dan:

 

[swanwil]

@snb - Ik heb de beveiliging eraf gehaald maar krijg een foutmelding bij openen; Fout - 2147221020 (800401e4) tijdens uitvoering Automatiseringsfout Ongeldige Syntaxis"
En als ik deze beieindig en het ordernr wijzig, krijg ik; "Fout 13 tijdens uitvoering: Typen komen niet met elkaar overeen"

Overigens wil ik de data niet ophalen tijdens typen, maar met een knopje (zie #9). Ik weet niet of het hier moeilijker (of makkelijker) van wordt ...?

 

[snb]

Sla beide bestanden op in een directory en niet in bijv. C:\ of G:\

 

[swanwil]

@snb - Ik heb beide bestanden samen in een aparte map gezet, maar ik blijf de foutmelding krijgen.
Als ik foutopsporing selecteer blijft hij gaan naar de regel "For j = 2 To UBound(sn)"

 

[AD1957]

Waarschijnlijk heb je de code in Thisworkbook niet overgenomen.

Private Sub Workbook_Open()
    With GetObject(ThisWorkbook.Path & "\order_data.xlsx")
      sn = .Sheets(1).UsedRange
      .Close
    End With
End Sub

En kijk meteen even naar Module1

 

[snb]

Dit soort dingen doe je stapsgewijs:
1 - bestanden downloaden en opslaan
2 - bestanden gebruiken om te testen of de code correct werkt
3 - de essentie van de aanpak doorgronden
4 - de code in andere bestanden plaatsen
5 - deze eigen bestanden testen

 

[swanwil]

In het bestand van snb zit code in de module, in Thisworkbook en in Worksheet, maar ik krijg meteen een foutmelding bij openen. Ik had ook al mijn order_data.xlsx document eerst geopend maar maakt niets uit.

Ik heb dus niks aangepast, alleen het bestand gedownload en in de map geplaatst.

 

[snb]

Je moet alleen het bestand 'stuklijst' openen.
Vertel op welke regel de uitvoering van de code vastloopt en welke melding je krijgt, desnoods met een schermafdruk.