Pivotdatenquelle in unzähligen Exceldateien ändern

Kai · 2019-08-14T15:02:46+00:00

Hallo, ich habe hier schon viel Hilfe erhalten in den letzten Jahren ohne selbst Fragen stellen zu müssen (vielen dank dafür), aber nun ists wohl mal Zeit dafür. Mein Problem: es gibt ca 3000 Exceldateien aus Excel 2003 deren pfad zur pivotdatenquelle aus unerfindlichen gründen geändert und abweichend zu den restlichen 4000 dateien ist. der unterschied ist lediglich ein unterordner, der anders benannt ist, daher die kurzfristige lösung: symlink des ordners...ist aber natürlich nichts wirklich schönes, daher die frage ob ich nun mit einer art batch oder makro es so lösen kann, dass diese datenquelle in allen dateien geändert wird. jede datei einzeln zu öffnen und den pfad zu ändern ist bei der schieren menge der dateien einfach nicht umsetzbar

ja sorry, wenn infos fehlen, aber ich hab das weder erstellt, noch jemals was mit pivot zu tun gehabt. und neuere versionen sind nicht zur hand, nur 2003
also nochmal kurz umrissen:
-es gibt nen haufen excel-dateien
-in diesen gibt es ein tabellenblatt mit daten, die aus einer zentralen xls datei "aktualisiert" werden
-der pfad zu dieser lautet zb 'X:\überarbeitet\[Vorlage.xls]Prüfvorschrift'!$A:$A
-Problem daran ist, dass der ordner "überarbeitet" nur temporär existierte und dieser nun "aktuell" heißen soll und dies auch tut, da alle dateien nun dort liegen, nur leider eben nur bei ca der hälfte der excel dateien die diese verknüpfung haben. warum und wieso der ordner bei der einen hälfte einwandfrei übernommen wird, sprich variabel ist, auf den ordner, in dem die datei xy und auch die vorlage liegen und bei der anderen nicht, ist mir ein rätsel.
lege ich eine der dateien auf meinen desktop, sucht die datei, die funktioniert auf dem desktop nach der vorlage. mach ich das gleiche mit einer defekten datei, ändert sich nur x: in c: und der rest des pfades bleibt gleich
wenn ihr mir sagt wo und wie genau ich euch weitere infos geben soll, ist das kein problem, aber bitte setzt bei diesem pivot zeug nicht voraus, das ich sofort weiß, was ihr meint ;)

Hallo Kai Uwe,
ich hab mal ein entsprechendes Makro geschrieben.
Es funktioniert auch unter der neuesten Excel-Version (Office 365 in 2019)
LG
Franz

Sub Updatei_Piovot_Quelle()
Dim wkb As Workbook
Dim wksPivot As Worksheet
Dim objPivotTab As PivotTable
Dim strSource As String, strSourceData As String
Dim varPfad As Variant, strDatei As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Bitte Ordner mit den zu aktualisierenden Dateien auswählen"
If .Show = -1 Then
varPfad = .SelectedItems(1)
Else
Exit Sub
End If
End With
strSource = "Vorlage.xls" 'name der Quelldatei der Pivot-Tabellenberichte
strDatei = Dir(varPfad & Application.PathSeparator & "*.xls", vbNormal)
Application.ScreenUpdating = False
Do Until strDatei = ""
Application.StatusBar = "Aktualisiere Datei: " & strDatei
Select Case LCase(strDatei)
Case LCase(strSource), LCase(ThisWorkbook.Name)
'Bei diesen Dateinamen Pivot-Update nicht ausführen
Case Else
Set wkb = Application.Workbooks.Open(varPfad _
& Application.PathSeparator & strDatei, UpdateLinks:=False)
Set wksPivot = wkb.Worksheets(1)
wksPivot.Activate
If wksPivot.PivotTables.Count > 0 Then
Set objPivotTab = wksPivot.PivotTables(1)
strSourceData = objPivotTab.SourceData
'                MsgBox "PivotSource: " & strSourceData
'Überprüfen, ob Quelldatei in anderem Verzeichnis
If Left(strSourceData, InStr(strSourceData, "]"))  _
"'" & Mid(varPfad, InStrRev(varPfad, Application.PathSeparator) + 1) _
& Application.PathSeparator & "[" & strSource & "]" Then
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:="'" & varPfad & Application.PathSeparator _
& "[" & strSource & "]Prüfvorschrift'!C1"
'                    ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:="'" & varPfad & Application.PathSeparator _
& "[" & strSource & "]Prüfvorschrift'!A1:D7" 'für meine Test-Dateien
Application.DisplayAlerts = False
wkb.Save
Application.DisplayAlerts = True
End If
End If
wkb.Close savechanges:=False
End Select
strDatei = Dir
Loop
Application.ScreenUpdating = True
Application.StatusBar = False
MsgBox "Fertig", vbOKCancel, "Pivottabs - Quelle aktualisieren"
End Sub

Pivotdatenquelle in unzähligen Exceldateien ändern

Forumthread: Pivotdatenquelle in unzähligen Exceldateien ändern

13
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Pivotdatenquelle in unzähligen Exceldateien ändern

Forumthread: Pivotdatenquelle in unzähligen Exceldateien ändern

13 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

13
Beiträge zum Forumthread

Beiträge zu diesem Forumthread