• 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.

Draaitabel met verschuiving naar ander bestand

Status
Niet open voor verdere reacties.
Albatros

Albatros

Gebruiker
Lid geworden
4 nov 2001
Berichten
388
Hoi,

Ik hoop dat ik het volgende probleeem een beetje duidelijk kan uitleggen. :mad: Er zijn 2 bestanden:
Bestand "metingen" bevat een tabel met waarden.
Bestand "draaitabel" bevat een draaitabel dat de gegevens haalt uit het blad "metingen".
Nu wil ik middels "verschuiving" het bereik van de tabel niet elke keer opnieuw aan passen.

De tabel heb ik via - invoegen - naam - definiëren genoemd: tabelAW

Het bereik van de tabel :
Code: 
 =VERSCHUIVING([Metingen.xls]Blad3!$B$7;0;0;AANTALARG([Metingen.xls]Blad3!$B:$B);3)
Vervolgens zet ik bij het bereik van de draaitabel:
Code: 
tabelAW
.
Hierna volgt een foutmelding.
Wat doe ik verkeerd?

Albatros
 

Bijlagen

Als je die dynamische naamgeving nu eens aanmaakt in het databestand (metingen.xls) en dan de source van de pivot table daarheen doet verwijzen in de vorm van:

DIRECTORY\BESTAND.EXTENSIE!DYNAMISCHENAAM

Werkt het dan ook niet?
 
ook een issue met extern bestand.

Hallo,

Ik ben toevallig ook bezig met een pivot table die zijn data uit een ander bestand haalt.
Ik wil deze tabel middels een macro laten refreshen, zonder dat ik het bestand hoef te openen. Onderstaande VB tekst heb ik nu maar hij blijft vastlopen op "pt.RefreshTable". Hij zegt dan: "Run-time error 1004 - The connection for viewingyour linked Microsoft Excel Worksheet was lost."

Kan iemand aan onderstaande VB tekst zien wat ik fout doe?
============================
Code: 
Sub refresh_Pivot()

    Dim pt As PivotTable
      
    Set ThisBook = ActiveWorkbook
    
    Call AllWorksheetPivots(pt)
    
       
End Sub

Sub AllWorksheetPivots(pt As PivotTable)

Application.ScreenUpdating = False

    Workbooks.Open Filename:= _
        "C:\Input\MI_Servicecalls.xlsx"
    Range("A2").Select
    Windows("DBA_Overall_Report.xlsm").Activate
    Sheets("Sheet1").Activate

    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt

    Windows("MI_Servicecalls.xlsx").Activate
    Range("A2").Select
    ActiveWorkbook.Close
    
Application.ScreenUpdating = True
    
End Sub
===============================

Alvast bedankt.
 
Laatst bewerkt door een moderator:
Finch,

Dankzij jouw suggestie kom ik in de buurt.
Als ik bij de draaitabel het volgende bereik aangeef, dan werkt het perfect, MITS het bestand "metingen.xls" geopend is:
Code: 
Metingen.xls!=VERSCHUIVING(Blad3!$B$7;0;0;AANTALARG(Blad3!$B:$B);3)

Als ik nu bij het bereik van de draaitabel, het volgende invul:
Code: 
C:\Documents and Settings\Mijn documenten\Excel\metingen.xls!=VERSCHUIVING(Blad3!$B$7;0;0;AANTALARG(Blad3!$B:$B);3)
en het bestand metingen.xls is gesloten, krijg ik een foutmelding. :shocked:

Dus we zitten wel al op de goede richting :thumb:

Albatros

ps 4rest, Jouw uitdaging zal ik ook volgen, maar voor de duidelijkheid kan je beter een nieuwe topic aanmaken.
 
4rest zei:
Hallo,

Ik ben toevallig ook bezig met een pivot table die zijn data uit een ander bestand haalt.
Ik wil deze tabel middels een macro laten refreshen, zonder dat ik het bestand hoef te openen. Onderstaande VB tekst heb ik nu maar hij blijft vastlopen op "pt.RefreshTable". Hij zegt dan: "Run-time error 1004 - The connection for viewingyour linked Microsoft Excel Worksheet was lost."

Kan iemand aan onderstaande VB tekst zien wat ik fout doe?
============================
Code: 
Sub refresh_Pivot()

    Dim pt As PivotTable
      
    Set ThisBook = ActiveWorkbook
    
    Call AllWorksheetPivots(pt)
    
       
End Sub

Sub AllWorksheetPivots(pt As PivotTable)

Application.ScreenUpdating = False

    Workbooks.Open Filename:= _
        "C:\Input\MI_Servicecalls.xlsx"
    Range("A2").Select
    Windows("DBA_Overall_Report.xlsm").Activate
    Sheets("Sheet1").Activate

    For Each pt In ActiveSheet.PivotTables
        pt.RefreshTable
    Next pt

    Windows("MI_Servicecalls.xlsx").Activate
    Range("A2").Select
    ActiveWorkbook.Close
    
Application.ScreenUpdating = True
    
End Sub
===============================

Alvast bedankt.
Klik om te vergroten...
Ik kon het zien ja, je heb dit niet gelezen.
 
Mijn excuses.

Heren,

Sorry dat ik me niet helemaal aan de regels van het forum heb gehouden.
Ik heb nu een eigen topic aangemaakt met een voorbeeld bestand erbij.

helpmij.nl

Mvg.
 
Albatros zei:
Hoi,



Niemand meer voor de laatste %-en om dit op te lossen, of is dit off-excel? :confused:

Albatros
Klik om te vergroten...

Ik was deze vraag compleet uit het oog verlopen.
Ik zal morgen ofzo nog eens naar het probleem kijken, vandaag gaat niet meer lukken vrees ik.
 
Finch,

Ik was deze vraag compleet uit het oog verlopen.
Ik zal morgen ofzo nog eens naar het probleem kijken, vandaag gaat niet meer lukken vrees ik.
Klik om te vergroten...

Bedankt voor je aandacht :cool:
Dan wens ik jou komende nacht een goede rust toe!

Albatros

ps. jou site heeft er weer een fan bij. Mooi helder zoals je alles uitlegt! :thumb:
 
Albatros zei:
Finch,



Bedankt voor je aandacht :cool:
Dan wens ik jou komende nacht een goede rust toe!

Albatros
Klik om te vergroten...

Ik zal mijn best doen om morgen uitgerust te zijn ;)

ps. jou site heeft er weer een fan bij. Mooi helder zoals je alles uitlegt! :thumb:
Klik om te vergroten...

bedankt. Wanneer de tijd het toelaat probeer ik regelmatig voor nieuw leesvoer te zorgen.

PS. Wat is eigenlijk de bedoeling van je opzet met die PT? Mag het absoluut niet via een tempstore (dus tijdelijk data automatisch overhalen) in het bestand waar de PT in staat, of indien het niet anders kan dan wel? Mag er wat VBA aan te pas komen?
 
Laatst bewerkt:
Finch,

PS. Wat is eigenlijk de bedoeling van je opzet met die PT? Mag het absoluut niet via een tempstore (dus tijdelijk data automatisch overhalen) in het bestand waar de PT in staat, of indien het niet anders kan dan wel? Mag er wat VBA aan te pas komen?
Klik om te vergroten...

De voorkeur gaat uit naar een verwijzing in de draaitabel,
omdat ik meerdere tabellen heb met variabele lengte en breedte. Op die manier kan ik dan ook vanuit de datum, via "groeperen" de waarden zetten in maanden en jaren. Mocht dit überhaupt niet gaan, dan sta ik open voor verdere suggesties.

Albatros
 
Laatst bewerkt:
Hallo,

ik heb nog even snel naar je bestandje kunnen kijken, maar echt veel tijd had ik nog niet. Dit zijn mijn bevindingen:

De opzet van een paar posts geleden gaat idd niet werken wanneer het bestand niet open is. Een dynamisch bereik moet men kunnen evalueren (lees:berekenen), en om dat in deze context te doen is het nodig dat het bestand open is.

Wat kan je dan doen?:

1) Je baseert de PT op een externe gegevensbron (=optie 2 in het eerste scherm wanneer je de wizard doorloopt). Je laat die dan verwijzen naar een ander Excel bestand, en dan kan je bv. zo via MS Query de juiste gegevens naar de PT over halen. Werkt niet slecht, het opzetten van zo'n verbinding is soms wat prullen met de syntax, maar als ie bolt doet ie het goed.

2) Ingrijpen op de datasource van je PT dmv VBA. Dan krijg je meerdere mogelijkheden, maar dit is er 1 van:
In het databestand (metingen) telt je in een lege cel hoeveel rijen je moet meenemen (dus via aantalarg). In je PT bestand refereer je naar die cel. Die kan je bijwerken zonder het bestand te openen dmv koppelingen toe te staan. Stel dat je die waarde laat zien in C5 (evt. een paar rijen bij tellen omwille van lege rijen bovenaan zoals nu het geval is), dan kan je deze code gebruiken om de data te vernieuwen. (C5 geeft dus de eindrij van je data aan)

Code: 
Sub RefreshDynamisch()
Dim Source As String
Dim pt As PivotTable

Set pt = Worksheets("blad1").PivotTables(1)
Source = "[Metingen.xls]Blad3!R7C2:R" & Worksheets("blad1").Range("C5") & "C4"
pt.SourceData = Source
End Sub

Uiteraard moet je wel die code aanpassen naar jouw situatie, dus bladnamen veranderen, evt. het itemnummer van de pivottable aanpassen (evt. de naam gebruiken - tussen "").

Je beziet het maar eens, en geeft maar wat feedback.

groeten,

Finch
 
Finch,

....gaat idd niet werken wanneer het bestand niet open is....
Klik om te vergroten...

Da's jammer. :shocked:.

Maar gelukkig heb je nog meer pijlen op je boog (valentijn?) :p

Suggestie 2:

De aantalarg formules heb ik ingevoerd, en de makro geplaatst.
Echter blijf deze hangen bij:

pt.SourceData = Source
Klik om te vergroten...

Als je nog tijd over hebt...........

De bestanden in de bijlagen zijn hierop aangepast.

Albatros
 

Bijlagen

Probeer onderstaande bestanden eens.
Het kan wel zijn dat je de linken tussen de 2 bestanden wel opnieuw moet leggen op basis van het path. Ik heb dat van u code gebruikt, maar misschien is dat niet helemaal correct ingeschat.

Graag je bevindingen.

edit: het gaat over de PT onder de groene streep.
 

Bijlagen

Finch,

Bedankt voor je reactie.
Maar de makro blijft weer op dezelfde regel steken

Code: 
Sub RefreshDynamisch()
Dim Source As String
Dim EndRow As String
Dim EndCol As String
Dim pt As PivotTable

EndRow = "K48"
EndCol = "K49"

Set pt = Worksheets("blad1").PivotTables("Draaitabel1")

Source = "'C:\Documents and Settings\Mijn documenten\Excel\[Metingen.xls]Blad3'!R7C2:R" & Worksheets("blad3").Range(EndRow) & "C" & Worksheets("blad3").Range(EndCol)

[COLOR="Red"][B][U]pt.SourceData = Source[/U][/B][/COLOR]
End Sub

Tevens, als ik waarden toevoeg aan de metingen, worden deze niet meegenomen in de draaitabel (bij handmatig verversen)

Albatros
 
Albatros zei:
Finch,

Bedankt voor je reactie.
Maar de makro blijft weer op dezelfde regel steken

Code: 
Sub RefreshDynamisch()
Dim Source As String
Dim EndRow As String
Dim EndCol As String
Dim pt As PivotTable

EndRow = "K48"
EndCol = "K49"

Set pt = Worksheets("blad1").PivotTables("Draaitabel1")

Source = "'C:\Documents and Settings\Mijn documenten\Excel\[Metingen.xls]Blad3'!R7C2:R" & Worksheets("blad3").Range(EndRow) & "C" & Worksheets("blad3").Range(EndCol)

[COLOR="Red"][B][U]pt.SourceData = Source[/U][/B][/COLOR]
End Sub

Tevens, als ik waarden toevoeg aan de metingen, worden deze niet meegenomen in de draaitabel (bij handmatig verversen)

Albatros
Klik om te vergroten...

Heb je de dir waarheen Source verwijst correct aangepast naar jouw situatie? Want hier deed ie het, met verwijzingen naar mijn dir. opbouw.
Je dient de string te gebruiken die Excel aanmaakt wanneer je binnen een cel (in bestand draaitabel) verwijst naar een andere cel in dat andere bestand (metingen). Wanneer je dan metingen afsluit zet Excel het path voor dat bereik, dat path moet je gebruiken. Uiteraard wel met een verwijzing naar het bereik dat je zoals hierboven wil vatten.

Idd bij het toevoegen van nieuwe data werkt de standaard refresh knop niet. Immers het bereik moet aangepast worden, dat doet die code. Dus het verversen van de PT verloopt best via die code (misschien moet daar nog een extra regel bijkomen om de PivotCache te refreshen, dat heb ik niet bekeken).
Ik zou denken dat je deze techniek ook gewoon kan toepassen binnen de wizard voor het aanmaken van de PT, dus waar je je bron moet zetten. Maar om één of andere reden krijg ik dat niet voor elkaar. Het kan zijn, dat het inderdaad gewoon weg niet kan, maar het kan evengoed een klein syntax probleem zijn.

Als je echter je PT opbouwt via het eerste alternatief dat ik beschreef, kan je de standaard refresh functionaliteit enzo gebruiken.
 
Finch,

Bedankt voor je geduld, maar...........helaas. :shocked:

Code: 
Sub RefreshDynamisch()
Dim Source As String
Dim EndRow As String
Dim EndCol As String
Dim pt As PivotTable

EndRow = "K48"
EndCol = "K49"

Set pt = Worksheets("blad1").PivotTables("Draaitabel1")

Source = "'C:\[Metingen.xls]Blad3'!R7C2:R" & Worksheets("blad3").Range(EndRow) & "C" & Worksheets("blad3").Range(EndCol)

pt.SourceData = Source
End Sub

blijft nog steeds "steken".
Als bijlage voeg ik de bestanden toe,die ik hier direct onder de "C-root" heb gezet. Misschien zie ik wel iets simpels over het hoofd.

Albatros
 
Ik zie jammergenoeg geen bestanden :confused:

Maar als je ze post zet ik ze ook onder mijn C root.
 
Het zal wel pas iets voor morgen zijn vrees ik hoor.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan