Range ophalen uit andere Excel sheet

[Peter B]

Helaas loop ik weer eens vast in de wondere wereld van Excel VBA.

Het probleem is het volgende. Ik heb een tabel in een honderdtal sheets staan, die ik uitlees. Deze tabel bestaat uit een aantal kolommen en een aantal rijen. Deze lees ik op de volgende manier uit. Dit werk prima:

Set rSource = Workbooks(strBestandsnaam).Sheets("Blad1").Range("celnaamTabelLinksboven", "celnaamTabelRechtsonder")

Maar ... Ik wil dezelfde range onafhankelijk van de sheetnaam uitlezen, omdat de range niet altijd op Blad1 staat. Ik heb al een aantal zaken geprobeerd, maar het lukt mij tot nu toe niet. Vandaar dat mijn hoop is gevestigd op jullie!

PS1: Ik heb vooral zitten stoeien met RefersToRange, dus bv.

Set rSource = Workbooks(FileName_Sh).Names("celnaamTabelLinksboven", "celnaamTabelRechtsonder").RefersToRange

PS2: Het zetten van een rangename "Tabel" o.i.d. in de uit te lezen sheets is geen (echte) optie

 

[Peter B]

Niemand?

Is het probleem niet helder beschreven, of is hier op het oog geen oplossing voor?

 

[zapatr]

Het zetten van een rangename "Tabel" o.i.d. in de uit te lezen sheets is geen (echte) optie

1. Waarom niet? Op het moment dat de tabel in een blad wordt geplaatst kun je toch ook die tabel een naam geven?
2. Je schrijft: " Ik heb een tabel in een honderdtal sheets staan, die ik uitlees." Is er slechts 1 blad waarin een tabel staat die je uitleest, of bevat elk van de 100 sheets een tabel die je wil uitlezen?

 

[Peter B]

Dank voor de reactie.

1. Het probleem is dat er al een honderdtal sheets bestaat en ik het niet zie zitten om in elk van deze sheets (achteraf) een tabel te creëren. Ook omdat de sheets "continue" open staan voor bewerking bij andere gebruikers
2. Elk van de sheets bevat *een* blad waarop een tabel staat. Deze tabel bestaat steeds uit dezelfde reeks cellen met bijbehorende namen, maar de tabel zelf heeft geen naam. Alle tabellen zien er als volgt uit:

celnaam1 celnaam2 celnaam3
celnaam4 celnaam5 celnaam6
celnaam7 celnaam8 celnaam9

 

[zapatr]

Erg onduidelijk en verwarrend, je antwoord.
Je schrijft: "Elk van de sheets bevat een blad", maar een sheet IS een blad.
Dus nogmaals mijn vraag: je excelbestand bevat een honderdtal bladen (=een honderdtal sheets).
Bevat ELK blad de bewuste tabel, of van die 100 bladen slechts 1 blad?

 

[Warme bakkertje]

Wat bedoel jij eigenlijk met Sheet.
Voor mij (en waarschijnlijk de meeste helpers) is een Sheet een werkblad in een bestand.
Als ik echter jouw uitleg lees bedoel jij met een Sheet eigenlijk een bestand (dus heb jij dagelijks 100 bestanden openstaan), en in deze bestanden staat er op een onbepaald werkblad een tabel die jij wil uitlezen.
Dit is echter mijn persoonlijke opmaak uit jouw woorden.
Zoals je ziet is dit (voor mij althans) meer dan verwarrend en mogelijk daarom heb je nog steeds geen antwoord verkregen.

PS: Zapatr was al tot dezelfde conclusie gekomen

 

[Peter B]

Ah. In mijn bewoordingen is de sheet het bestand en een blad de sheet ... Excuus voor de verwarring!

Opnieuw!

1. Het probleem is dat er al een honderdtal *Excelbestanden* bestaan en ik het niet zie zitten om in elk van deze *Excelbestanden* (achteraf) een tabel te creëren. Ook omdat de *Excelbestanden* "continue" open staan voor bewerking bij andere gebruikers
2. Elk van de *Excelbestanden* bevat een *sheet* (één van meerdere *sheets*) waarop een tabel staat. Deze tabel bestaat steeds uit dezelfde reeks cellen met bijbehorende namen, maar de tabel zelf heeft geen naam. Alle tabellen zien er als volgt uit:

celnaam1 celnaam2 celnaam3
celnaam4 celnaam5 celnaam6
celnaam7 celnaam8 celnaam9

Dus bv.:
In bestand1.xlsx staat een tabel op "Blad1"
In bestand2.xlsx staat een tabel op "Blad2"
In bestand 3.xlsm staat een tabel op "Blad1"
etc.

Overigens, voor de volledigheid, de tabellen zijn niet echt als tabel opgenomen (zoals via Invoegen > Tabel), maar is het een verzameling cellen verdeeld over steeds een gelijk aantal kolommen/ rijen. En noem ik deze verzameling cellen een tabel ...

 

[Warme bakkertje]

En staat er op dat werkblad met die 'tabel' ook nog andere gegevens of enkel en alleen deze verzameling cellen ?
Staat deze tabel steeds in hetzelfde bereik of kan dit ook verschillen per bestand ? dus bv. bestand1 beginnend in A1, bestand2 beginnend in D2, enz... of steeds beginnend in dezelfde cel voor elk bestand ?

 

[Peter B]

Er staan op de werkbladen met de 'tabellen' ook andere gegevens. En ze staan niet altijd op dezelfde plek.

 

[Warme bakkertje]

Als ik alles dus goed begrepen hebt wil jij dus zoeken in bestand x op 1 v/d aanwezige werkbladen naar een matrix van 3X3.
Is er dan iets unieks aan die tabel waarop je kan zoeken ?
Of is er iets dat je kan meegeven bv. de naam linksboven die dan gezocht moet worden op elk werkblad en indien gevonden dan de selectie uitbreiden naar 3X3 ?
Je kan zelf toch ook begrijpen dat er ergens iets moet zijn dat die 'tabel' herkenbaar maakt want hoe wil je hem anders vinden.

 

[Peter B]

Hoi Rudi,

Zo zie je maar ... Ik meende duidelijk te zijn door "celnaamTabelLinksboven" en "celnaamTabelRechtsonder" te gebruiken. Helaas ...

De matrix is niet 3x3, maar 3x5 of 3x6. Alle cellen in deze matrix hebben een unieke naam. En de cel linksboven en rechtsonder hebben altijd dezelfde naam. M.i. moet je dus met deze namen de matrix uniek kunnen identificeren.

Vandaar dat deze code prima werkt (maar hier zit de bladnaam in, en die wil ik kwijt):

Set rSource = Workbooks(strBestandsnaam).Sheets("Blad1").Range("celnaamTabelLinksboven", "celnaamTabelRechtsonder")

Ik hoop dat ik nu alles duidelijk heb gemaakt en dat je in staat bent (ik twijfel er niet aan ... ) om een oplossing aan te bieden.

Met vriendelijke groet,

Peter

 

[Warme bakkertje]

De basis

Sub tst()
    With Workbooks(strBestandsnaam)
        For Each sh In .Sheets
            On Error Resume Next
            sn = sh.Cells.Find("celnaamTabelLinksboven", , xlValues, xlWhole).Address
            sp = sh.Cells.Find("celnaamTabelRechtsonder", , xlValues, xlWhole).Address
            If sn <> vbNullString And sp <> vbNullString Then sh.Range(sn & ":" & sp).Copy ThisWorkbook.sheets("Blad1").Range("A1"): Exit Sub
            On Error GoTo 0
        Next
    End With
End Sub

 

[Peter B]

Ik moet er even verder induiken, maar als ik je oplossing goed begrijp dan moet je verplicht de sheet opgeven bij het ophalen van een range o.b.v. naam. Jammer dat Excel niet slimmer is. Want bij het opgeven van de namen is wel steeds aangegeven dat het bereik de werkmap is.

Dank je i.i.g. voor de geboden oplossing en het geduld

 

[Warme bakkertje]

Neen, je moet enkel de bestandsnaam opgeven, en dan gaat hij alle sheets af die zich in dat bestand bevinden totdat er een resultaat gevonden is.