• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Gegevens ophalen uit ander bestand met wisselende bestandsnaam

Status
Niet open voor verdere reacties.
R

Roy3001

Gebruiker
Lid geworden
29 jul 2011
Berichten
54
Beste leden,

Ik wil in een verzamelblad gegevens ophalen uit 3 andere excelbestanden (bronbestanden) van 3 verschillende afdelingen.
Deze excel bronbestanden zien allemaal hetzelfde uit maar zijn van 3 verschillende afdelingen en de gegevens van het bronbestand dat moet worden opgehaald staat altijd in dezelfde cel.
Echter de naam van het bronbestand dat verschilt per keer, bijvoorbeeld januari.xlsx, februari.xlsx enz. en dat van 3 afdelingen.

De bijlage is een vereenvoudigde versie van het bestand.

Wat het bestand moet doen:
- In cel B10 moeten de gegevens komen van cel B2 van bestand van locatie 1;
- in cel C10 moeten de gegevens komen van cel B2 van bestand van locatie 2;
- In cel D10 moeten de gegevens komen van cel B2 van bestand van locatie 3.
- In cel b11 moeten de gegevens komen van cel B3 van bestand van locatie 1;
- enz.

Wat ik zou willen dat ik de bestandslocatie (staat nu in cel b2-b4) wijzig en dat dit dan automatisch wordt aangepast in de formule in cel B10 tot en met D16.

Hoe krijg ik dit voor elkaar of zijn er andere opties om dit voor elkaar te krijgen?

Alvast bedankt voor het meedenken!
 

Bijlagen

  • voorbeeld1.xlsx
    8,7 KB · Weergaven: 18
Laatst bewerkt:
Gaat druk worden in B10. Verder zie ik nergens een formule en met een formule gaat het lastig worden denk ik.
 
Ook handig om te weten welke data er opgehaald moet worden en hoe die bestanden er uit zien.
 
@VenA: foutje... is aangepast;
@JVeer: de waarde van die cel in het andere bestand moet worden opgehaald.
 
Je zou dit even kunnen testen
Wel even de sheetnamen aanpassen naar de juiste.

Code: 
Sub j_v()
j = 2
 For Each Path In Sheets(1).Range("B2:B4")
   With GetObject(Path)
      ThisWorkbook.Sheets(1).Cells(10, j).Resize(7) = .Sheets(1).Cells(2, 2).Resize(7).Value
     .Close 0
      j = j + 1
   End With
 Next
End Sub
 
Mijn voorkeur:

Een querytabel gekoppeld aan een extern bestand. Bij opening wordt de link bijgewerkt.
Die bijgewerkte gegevens worden aan een ander werkblad toegevoeegd.
Nieuwe gegevens worden opgeslagen onder de naam van het via de link gekoppelde bestand en vervangen daardoor de gegevens in het gekoppelde bestand.

Voorbeeld:
- de gegevens van januari worden opgeslagen in G:\OF\gegevens.xlsx
- bij opening van het Excelbestand G:\OF\alles.xlsb met de link naar G:\OF\gegevens.xlsx worden de gegevens van G:\OF\gegevens.xlsx gelezen, en vervolgens gekopieerd en toegevoegd aan het werkblad 'overzicht' van G:\OF\alles.xlsb.
- het bestand G:\OF\alles.xlsb wordt opgeslagen en gesloten.
- de gegevens van februari worden opgeslagen in/ vervangt G:\OF\gegevens.xlsx
- het bestand G:\OF\alles.xlsb wordt geopend, etc.
 
@JVeer: ik kan dit helaas nu niet uittesten.

@iedereen: ik heb nu:
Cel B10: ='[bestand1.xlsx]Blad1'!$B$2
Cel C10: ='[voorbeeld1.xlsx]Blad1'!$B$2
Cel D10: ='[test1.xlsx]Blad1'!$B$2
Cel B11: ='[bestand1.xlsx]Blad1'!$B$3
- enz.

Echter de andere maand moet ik de bestandnamen altijd weer veranderen in alle formules. Ik wil dit dus oplossen door in B2 t/m B4 de bestandsnaam te wijzigen zodat dit in de formules wordt aangepast en dan automatisch uit de goede bestanden de gegevens worden gehaald.
 
Laatst bewerkt:
@SNB: 1. Nee niet over het hoofd gezien 2. een querytabel zegt mij niets. 3. De gegevens februari kunnen niet opgeslagen worden onder dezelfde naam, dus dat wordt sowieso een andere naam.

@iedereen: Ik was aan het denken aan een formule iets in de trant van in Cel B10: ='[locatie genoemd in B2 van het bijgevoegde voorbeeld]Blad1'!$B$2
Mij lukt het tot nu toe niet... Dus weet niet of het mogelijk is.
 
Macro al getest?
 
Niet getest maar werkt niet? Ook knap
 
Naam van sheets(...) aangepast in de code? Ik denk dat je die 3 vb bestanden ook even moet plaatsen voor een betere oplossing
 
Nu met meerdere bestanden erbij en hopelijk wordt het nu duidelijker.

De 3 bestanden per maand staan op diverse locaties op diverse netwerkschijven, in het voorbeeld even op 1 locatie (c:\) aangehouden.
In het 'voorbeeld totaal januari.xlsx' staat in b2 tot en met b4 de locatie waar de bestanden te vinden zijn met de juiste bestandsnaam waar het 'voorbeeld totaal januari.xlsx' zijn gegevens moet halen. In cel B10 van 'voorbeeld totaal januari.xlsx' staat de locatie waar hij moet kijken, in dit geval ' Blad1'!$B3 ' .

Mijn bedoeling is dus dat cel B10 van bestand 'voorbeeld totaal januari.xlsx' kijkt naar cel B2 van 'voorbeeld totaal januari.xlsx' uit welk bestand hij de gegevens moet halen en van dat bestand de gegeven van ' Blad1!$B3 ' haalt. enz.

Dus als ik cel B2 verander naar bijvoorbeeld c:\test\testen\testexcel.xlsx dat hij de formule in B10 automatisch aanpast naar deze locatie en daar de gegevens uit cel Blad1!$B3 haalt enz.
 

Bijlagen

  • Locatie 1 februari.xlsx
    8,6 KB · Weergaven: 15
  • Locatie 1 januari.xlsx
    8,6 KB · Weergaven: 14
  • Locatie 2 februari.xlsx
    8,6 KB · Weergaven: 12
  • Locatie 2 januari.xlsx
    8,6 KB · Weergaven: 16
  • Locatie 3 februari.xlsx
    8,6 KB · Weergaven: 13
  • Locatie 3 januari.xlsx
    8,6 KB · Weergaven: 14
  • voorbeeld totaal januari.xlsx
    12 KB · Weergaven: 13
Bij mij werkt dit toch echt. Wel de 2 in een 3 veranderd na het inzien van je bestanden.
Dit kun je koppelen aan een knop. Iedere keer als je iets verandert kun je op de knop drukken en de data wordt ververst.

Code: 
Sub j_v()
Application.ScreenUpdating = False
j = 2
 For Each Path In ThisWorkbook.Sheets(1).Range("B2:B4")
   With GetObject(Path)
      ThisWorkbook.Sheets(1).Cells(10, j).Resize(7) = .Sheets(1).Cells([COLOR="#FF0000"]3[/COLOR], 2).Resize(7).Value
     .Close 0
      j = j + 1
   End With
 Next
Application.ScreenUpdating = True
End Sub
 
Laatst bewerkt:
Bij mij blijft hij een foutmelding geven.

Begrijp ook even naar aanleiding van jouw vorige opmerking niet naar wat ik 'Naam van sheets(...) aangepast in de code? ' moet aanpassen.

Hoe zou mijn code moeten uitzien als je naar al mijn bestanden kijkt. Ik gebruik een Nederlandstalige versie van Excel.

Bijgevoegd de foutcode die ik krijg als ik de macro uitvoer.

foutmelding.JPG
 
Variabelen geen namen geven die al vergeven zijn door Excel.

Verander Path bv. in cl.
 
Verander het woordje 'Path' in iets anders, maak niet uit wat.

Bv. xx
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan