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

1
2
3
4
5
6
7
8
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 :-)