Power Query mit VBA aktualisieren

David · 2019-08-27T20:23:02+00:00

Hallo zusammen, folgender Sachverhalt: Datenblatt hat insgesamt 9 Tabellenblätter. Bei einem dieser Tabellenblätter ("Input") ist eine Power Query hinterlegt, welche ganz grob gesagt, verschiedene anderen Datenblätter durchläuft, Daten zusammenstellt und nach einigen Filtern sortiert. Diese Daten werden dann auf dem Tabellenblatt Input ausgegeben. Die Power Query funktioniert so, dass per Dropdown-Menü ein Wert ausgewählt wird, und dann die hinterlegten Datenblätter nach diesem Wert gefiltert und wie bereits geschrieben auf dem Blatt "Input" ausgegeben werden. Das Dropdown-Menü ist definiert mit Werten welche auf dem Tabellenblatt "Inputdaten" aufgelistet sind. Nun wird wie folgt vorgegangen: Es wird der erste Wert (40) aus dem Dropdown ausgewählt, Power Query aktualisiert sich automatisch. Anschließend nachdem alles geladen ist wird per Makro die Datei abgespeichert unter einem definierten Namen, geschlossen und die "Masterdatei" erneut geöffnet. Anschließend geht es mit dem zweiten Wert (2690) weiter und das geht so weit, bis der letze Wert (9990) erreicht ist. Es handelt sich insgesamt um 81 Werte. Wir haben uns nun überlegt ob wir das ganze mit aktualisieren und abspeichern nicht per VBA automatisch ablaufen lassen können. Sprich die Werte in Spalte A auf dem Blatt "Inputdaten" mit Cells(i,1) definieren und das dann das VBA-Tool jeden einzelnen Wert (40, 2690, ... 9990) durchläuft, die Tabelle bzw. die Power Query aktualisiert und sobald alles geladen ist abspeichern etc. Ich habe alle Codes bereits geschrieben, allerdings scheitert es daran, dass sich VBA die Werte aus "Inputdaten" ziehen soll und einen nach dem anderen nehmen soll und die Power Query aktualisieren. Kann mir damit hier eventuell jemand weiterhelfen? Vielen Dank! David

Hallo Torsten,
sorry das habe ich vergessen:

Sub BP_2020_erstellen()
'Arbeitsmappen und Blattschutz ausschalten
For Each Blatt In ActiveWorkbook.Sheets
Blatt.Unprotect Password:="ilc"
Next
ActiveWorkbook.Unprotect Password:="XXX"
'Speichern der Datei unter Name in E37
Sheets("Deckblatt").Select
Range("E37").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Dim NeuerName As String
NeuerName = Range("E37").Select
ActiveWorkbook.SaveAs "\\hier steht der Ordnerpfad" & NeuerName
' Arbeitsblatt Input, Input Werte, Check ausblenden
Sheets("Input").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Input Werte").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Check").Select
ActiveWindow.SelectedSheets.Visible = False
' Konto-Filter setzen
' Mitarbeiter Zellen sperren
Sheets("Mitarbeiter").Select
Range("E13:H81").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E13").Select
Sheets("Mitarbeiter").Select
Range("E13:H81").Select
'bereinigt pseude-leere Zellen im selektierten Zellbereich
Dim Zelle As Range, StatusCalc As Long
If ActiveSheet.Type = xlWorksheet Then
With Application
.ScreenUpdating = False
.EnableEvents = False
StatusCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each Zelle In Selection.Cells
If Not Zelle.HasFormula Then
If Not IsEmpty(Zelle) And Zelle.Value = "" Then
Zelle.ClearContents
End If
End If
Next
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = StatusCalc
End With
End If
Sheets("Mitarbeiter").Select
Range("E13:H81").Select
Selection.SpecialCells(xlCellTypeConstants, 23).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Locked = True
Selection.FormulaHidden = False
Sheets("Mitarbeiter").Select
Range("E13").Select
' Arbeitsmappe und Blätter schützen
For Each Blatt In ActiveWorkbook.Sheets
Blatt.Protect Password:="ilc", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
ActiveWorkbook.Protect Password:="XXX", Structure:=True, Windows:=False
'Deckblatt auswählen
Sheets("Deckblatt").Select
Range("A1").Select
'Datei speichern und schließen
ActiveWorkbook.Save
' Mater Datei wieder öffnen
Workbooks.Open "Hier steht der Pfad der Masterdatei"
End Sub

und der Code, welcher die Werte aus "Inputdaten" auswählen soll:

Sub auswählen()
Dim i As Integer
For i = 1 To 4
ActiveSheet.Cells(5 + i, 1).Select
Call BP_2020_erstellen
Next i
End Sub

Also ich hab mir mal erlaubt etwas rumzudocktern an deinem Code. Denke es sollte so funktionieren. Falls Fragen aufkommen zu den Aenderungen oder irgendwas sollte nicht funktionieren, melde dich einfach. Kann es ja nicht testen.


Option Explicit
Sub BP_2020_erstellen()
'Arbeitsmappen und Blattschutz ausschalten
Dim Blatt As Worksheet
For Each Blatt In ActiveWorkbook.Sheets
Blatt.Unprotect Password:="ilc"
Next
ActiveWorkbook.Unprotect Password:="XXX"
'Speichern der Datei unter Name in E37
Sheets("Deckblatt").Range("E37").Copy
Sheets("Deckblatt").Range("E37").PasteSpecial xlPasteValues
Dim NeuerName As String
NeuerName = Sheets("Deckblatt").Range("E37")
ActiveWorkbook.SaveAs "\\hier steht der Ordnerpfad\" & NeuerName
' Arbeitsblatt Input, Input Werte, Check ausblenden
Sheets("Input").Visible = False
Sheets("Input Werte").Visible = False
Sheets("Check").Visible = False
' Konto-Filter setzen
' Mitarbeiter Zellen sperren
Sheets("Mitarbeiter").Range("E13:H81").Copy
Sheets("Mitarbeiter").Range("E13").PasteSpecial xlPasteValues
'bereinigt pseude-leere Zellen im selektierten Zellbereich
Dim Zelle As Range, StatusCalc As Long
If ActiveSheet.Type = xlWorksheet Then
With Application
.ScreenUpdating = False
.EnableEvents = False
StatusCalc = .Calculation
.Calculation = xlCalculationManual
End With
For Each Zelle In Selection.Cells
If Not Zelle.HasFormula Then
If Not IsEmpty(Zelle) And Zelle.value = "" Then
Zelle.ClearContents
End If
End If
Next
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = StatusCalc
End With
End If
With Sheets("Mitarbeiter").Range("E13:H81").SpecialCells(xlCellTypeConstants, 23).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
.Locked = True
.FormulaHidden = False
End With
' Arbeitsmappe und Blätter schützen
For Each Blatt In ActiveWorkbook.Sheets
Blatt.Protect Password:="ilc", DrawingObjects:=True, Contents:=True, Scenarios:=True
Next
ActiveWorkbook.Protect Password:="XXX", Structure:=True, Windows:=False
'Deckblatt auswählen
Sheets("Deckblatt").Select
Range("A1").Select
'Datei speichern und schließen
ActiveWorkbook.Save
' Mater Datei wieder öffnen
Workbooks.Open "Hier steht der Pfad der Masterdatei"
End Sub

Gruss

Power Query mit VBA aktualisieren

Forumthread: Power Query mit VBA aktualisieren

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial

Power Query mit VBA automatisieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

Power Query mit VBA aktualisieren

Forumthread: Power Query mit VBA aktualisieren

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Power Query mit VBA automatisieren

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread