VBA om pivottables te kopieren

[DaviddeV1990]

Beste,

Ik heb een VBA-code om pivottables te importeren die op een andere locatie staan:

Sub CopyPvts()
Dim ws As Worksheet
Dim wb As Workbook
Set wb = Workbooks.Open("D:\Test\MyPivot.xls") ' change to suit
For Each ws In Worksheets(Array("Sheet1", "Sheet4", "Data")) ' change to suit
    ws.Copy after:=ThisWorkbook.Worksheets(Worksheets.Count)
Next ws
wb.Close savechanges:=False
End Sub

Echter, die pivottable wordt om de 2 uur geupdate met nieuwe waarden. Bij het openen van het bestand waarnaar ik de pivottables wil kopieren wil ik dus dat de macro elke keer als ik het bestand open de nieuwste pivottable importeert. Hoe doe ik dit? Op dit moment als ik de macro uitvoer dan voegt hij alleen maar nieuwe sheets toe. Dus bijvoorbeeld "Sheet1 (2)". Het is echter de bedoeling dat hij de oude "Sheet1" vervangt. Hoe doe ik dit?

Alvast bedankt!

Met vriendelijke groet,
David

 

[mcfreak]

ik ken de taal niet echt maar misschien de oude wissen en dan de nieuwe erin zetten?
dan bestaat hij niet meer dus zou hij hem er gewoon ik kunnen zetten

 

[roeljongman]

Waarom kies je ervoor om elke keer die sheets met de pivottable te importeren. Als ik de code goed lees word gewoon de werkende pivot geimporteerd en geen platte tekst versie o.i.d.
Kun je de pivot niet gewoon in de sheet zetten en dan elke keer vernieuwen als er nieuwe data is?

 

[DaviddeV1990]

De situatie is als volgt:
De pivottable wordt eens per 2 uur opnieuw geexporteerd door een ERP-pakket. Nieuwe gegevens over verkopen komen dan in de pivottable terecht. Deze pivottable staat op de server en kan gelezen worden door de diverse filialen, maar niet bewerkt en er kan ook niet vanuit worksheets naar verwezen worden.

Daarom is gekozen om de pivottable te kopieren naar een lokaal bestand. Zo kunnen er wel dingen met de pivottable gedaan worden en kan ieder filiaal alleen het deel van de pivottable gebruiken wat voor zijn filiaal nuttig is.

De oude versie verwijderen is geen optie daar dit het werken belemmert en daar de eindgebruikers geen kennis hebben van Excel.

In ieder geval bedankt voor de reacties tot nu toe.

 

[roeljongman]

Ok duidelijk waarom het niet kan met die pivots

Om de huidige macro die "CopyPvts" te runnen als je opstart kun je die macro gewoon aanroepen in een andere macro, in dit geval een eventmacro die draait als je een het bestand opent.

De volgende code moet in de vba editor komen in de programmacode pagina "Thisworkbook"

Private Sub Workbook_Open()
On Error GoTo einde

CopyPvts

einde:
End Sub

Alles wat die code dus doet is het laten starten van de macro CopyPvts en als er om een of andere reden een fout ontstaat dat de macro word beëindigd (zonder dat de gebruiker word lastiggevallen met foutmeldingen)
Voordeel van het verwijzen naar die macro ipv de hele code overnemen is dat als je de macro aanpast je de workbook_open code niet ook hoeft aan te passen en de "copypvts"-macro kun je ook aan een knop koppelen dus als een filiaal het bestand nog open heeft staan dat ze ook via een knop kunnen vernieuwen ipv het bestand moeten heropenen.

Het andere deel van je vraag is iets lastiger op te lossen. zonder voorbeeld.
als ik je goed begrijp de essentie van je probleem is dat je nu hele tabbladen kopieert en steeds die 3 tabbladen toevoegt ipv de oude vervangt.

ik heb in elk geval even een eigen voorbeeldje gemaakt die steeds 3 werkbladen verwijderd en dan weer 3 nieuwe werkbladen invoegt met dezelfde naam (bij mij blad1 (2) enz.., bij jou Sheet1, Sheet4 en Data) , ik heb het openen van het bestand uitgeschakeld voor het doel van de test. zie het meer even als een studiemodel om te kijken of dit is wat je zoekt.

(voor de zekerheid: aan dit testbestand is nog niet de code toegevoegd om bij openen direct uit te voeren)

 

[DaviddeV1990]

Dit is volgens mij wel wat ik zoek. Ik krijg alleen wel de melding "Data may exist in the sheet(s) selected for deletion. To permanently delete the data, press Delete". Is het ook mogelijk om deze meldingen niet te krijgen? Dus gewoon de oude worksheets te vervangen door de nieuwe zonder te vragen om een bevestiging.

 

[roeljongman]

o ja die was ik vergeten omdat ik de code niet meer wist.
maar als je deze code aan het begin van je macro zet of kort voor de verwijder instructie

Application.DisplayAlerts = False

aan het eind van je macro (boven end sub ) of direct na de verwijder instructie moet je dan ook die waarschuwingen weer aanzetten.

Application.DisplayAlerts = True

 

[DaviddeV1990]

Bedankt voor je antwoorden. Ik heb alleen niet echt verstand van VBA. De volgende zin snap ik dus ook eigenlijk niet: "De volgende code moet in de vba editor komen in de programmacode pagina 'Thisworkbook'".

Kun je uitleggen hoe ik dit doe?

 

[roeljongman]

Je kunt VBA code op verschillende locaties in je sheet hebben

De macrocode voor toevoegen en verwijderen van tabbladen staat in een module.

De code die zorgt dat een macro word uitgevoerd als je je bestand opent moet op een specifieke plek staan die in de VBA editor Thisworkbook heet. Daar worden speciale macro's (event macro's) uitgevoerd die bij een bepaalde gebeurtenis gaan draaien (openen bestand, sluiten bestand, opslaan bestand)

zie plaatje waar je die vind in de VBA editor.

je moet even dubbelklikken op de tekst "Thisworkbook en daarna die code erin plakken.

 

[DaviddeV1990]

Bedankt. Het is me gelukt om alles toe te voegen aan mijn bestand. Echter, kan het zijn dat hij steeds de nieuwste weer verwijderd? Volgens mij verwijdert hij steeds de ... (2) terwijl dat juist de nieuwste is. Eerst moet de oude verwijderd worden en dan moet de nieuwe toegevoegd worden met precies dezelfde naam. Dit gebeurt nu niet toch?

 

[roeljongman]

Ja ik heb geen exact werkende oplossing gegeven alleen de bouwstenen die je zelf in de goede volgorde moet zetten zodat eerst de oude sheets worden verwijderd en daarna de nieuwe ingevoegd.

 

[DaviddeV1990]

Dat begrijp ik, maar ik krijg het wel voor elkaar om de worksheets met "(2)" te verwijderen of om de oude worksheets te verwijderen, maar de bij het eerste houd ik dan de oude (niet-geupdate) worksheets en bij het tweede krijg ik de nieuwe worksheets, maar dan met als toevoeging "(2)" terwijl ze precies dezelfde naam moeten hebben als de oude, dus zonder "(2)". Daar kom ik nu nog niet uit.

Voor de rest bedankt, want de rest werkt.