Helpmij.nl
Helpmij.nl
Helpmij.nl

Quote

Weergeven resultaten 1 tot 10 van 10

Onderwerp: macro om data uit ander document op te halen

  1. #1
    Senior Member swanwil's avatar
    Geregistreerd
    30 augustus 2010
    Locatie
    Hoogeloon
    Vraag is niet opgelost

    Question macro om data uit ander document op te halen

    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
    Bijgevoegde bestanden Bijgevoegde bestanden
    Laatst aangepast door swanwil : 25 januari 2023 om 16:53

  2. #2
    Tera Honourable Senior Member edmoor's avatar
    Geregistreerd
    9 september 2000
    Locatie
    Zuid-Holland
    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.
    "Bij IT-ers gaat alles automatisch, maar niets vanzelf. "
    Op rechtstreekse vragen via email of privébericht reageer ik niet. Daar is het forum voor.
    Lees ook: http://www.helpmij.nl/forum/announcement.php?f=5

  3. #3
    Senior Member swanwil's avatar
    Geregistreerd
    30 augustus 2010
    Locatie
    Hoogeloon
    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

  4. #4
    Tera Honourable Senior Member edmoor's avatar
    Geregistreerd
    9 september 2000
    Locatie
    Zuid-Holland
    Ik bedoel dat wanneer je dat uit zet het openen van die documenten een stuk sneller zal gaan.
    "Bij IT-ers gaat alles automatisch, maar niets vanzelf. "
    Op rechtstreekse vragen via email of privébericht reageer ik niet. Daar is het forum voor.
    Lees ook: http://www.helpmij.nl/forum/announcement.php?f=5

  5. #5
    Senior Member swanwil's avatar
    Geregistreerd
    30 augustus 2010
    Locatie
    Hoogeloon
    Ah, Okay.
    Daarom staat dit bij ons al uit inderdaad

  6. #6
    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.
    Bijgevoegde bestanden Bijgevoegde bestanden
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  7. #7
    Giga Senior JEC.'s avatar
    Geregistreerd
    27 februari 2019
    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

    Code:
    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
    Bijgevoegde bestanden Bijgevoegde bestanden
    Laatst aangepast door JEC. : 25 januari 2023 om 22:03 Reden: Alvast wat controle ingebouwd
    Gr,
    JEC

  8. #8
    Giga Senior JEC.'s avatar
    Geregistreerd
    27 februari 2019
    @Jkp,

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

    PHP Code:
    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
    Laatst aangepast door JEC. : 25 januari 2023 om 22:21
    Gr,
    JEC

  9. #9
    Senior Member swanwil's avatar
    Geregistreerd
    30 augustus 2010
    Locatie
    Hoogeloon
    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
    Klik op afbeelding voor grotere versie

Naam:  2023-01-26 10_19_39-Window.png
Bekeken: 9
Grootte:  12,3 KB
    Groeten swanwil

    Ik gebruik Office 365 Nederlands

  10. #10
    @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 :-)
    Groetjes,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl en business

Partners
Sponsoren