Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1708to1712
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Power Query mit VBA aktualisieren

Power Query mit VBA aktualisieren
27.08.2019 20:23:02
David
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

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

Betreff
Datum
Anwender
Anzeige
AW: Power Query mit VBA aktualisieren
28.08.2019 09:26:42
Torsten
Hallo,
wenn du uns mal deine geheimen

Ich habe alle Codes bereits geschrieben
zeigen wuerdest, koennte man vielleicht helfen und schauen, wo es genau haengt.
AW: Power Query mit VBA aktualisieren
28.08.2019 10:23:33
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

Anzeige
AW: Power Query mit VBA aktualisieren
28.08.2019 12:16:08
David
Die Fehlermeldung bzw. das Debuggen tritt i.d.R. an der Stelle
ActiveWorkbook.SaveAs "\\hier steht der Ordnerpfad" & NeuerName auf.
Habe NeuerName nun wie folgt neu definiert, da die Formel =verketten in Deckblatt E37 immer abhanden gekommen ist.
Sheets("Deckblatt").Select
Range("E37").Select
Range("E37") = "=CONCATENATE(""BP20_"",R[-19]C,""_"",R[-17]C)"
Range("E37").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Dim NeuerName As String
NeuerName = Range("E37").Select
ActiveWorkbook.SaveAs
"Hier steht der Pfad des Ordners" & NeuerName
Die Fehlermeldung ist:
Laufzeitfehler '1004':
Auf die Datei konnte nicht zugegriffen werden. Versuchen Sie die folgenden Lösungsvorschläge:
- überprüfen Sie, ob der angegebene Ordner vorhanden ist (Ist er definitiv)
- stellen Sie sicher, dass der Ordner nicht schreibgeschützt ist (Ist er nicht)
- vergewissern Sie sich, dass der Dateiname keines der folgenden Zeichen enthält:
? []: | oder *
- der Dateiname darf nicht länger als 218 Zeichen sein.
Meiner Meinung nach, kann ich keinen der Lösungsvorschläge anwenden.
Was ebenfalls komisch ist, es wird eine Datei abgespeichert nachdem der Code durchgelaufen ist, allerdings unter dem Namen "Wahr".
Was ggf. sein kann, da die Namensvergebung der Dateien ja offensichtlich irgendwie falsch ist, das der Fehler darin liegt, dass sich die Datei nur abspeichert und nicht schließt. Sprich ich lasse den Code durchlaufen, es kommt die Fehlermeldung, eine Datei wird abgespeichert und meiner Meinung nach versucht Excel dann eine neue Datei ebenfalls unter "Wahr" abzuspeichern.
Hoffe es ist alles verständlich geschrieben.
Anzeige
AW: Power Query mit VBA aktualisieren
28.08.2019 13:07:40
Torsten
lass das .Select weg bei der Zuweisung fuer NeuerName. Du willst doch den Wert der Zelle uebergeben. Sie nicht selektieren.
Und ueberhaupt. Ueberarbeite deinen Code mal richtig. Die ganze Selektiererei ist zu 99% unnuetz. Macht den Code nur langsamer.
Gruss Torsten
AW: Power Query mit VBA aktualisieren
28.08.2019 13:41:07
Torsten
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
Anzeige
AW: Power Query mit VBA aktualisieren
28.08.2019 13:47:08
David
Hallo Torsten,
nun habe ich das Problem, dass das Makro nicht wartet bis die Power Query durchgelaufen ist. Sprich der Code
Application.Run _
"'PERSONAL.xlsb'!Tabelle_Aktualisieren"
Sub Tabelle_Aktualisieren()
ActiveWorkbook.RefreshAll
End Sub
wartet nicht bis alle Abfragen der Power Query durch sind und speichert zwar die Dateien unter dem definierten Namen, allerdings mit den selben Inhalten.
Ich werde immer nach dem Starten des Codes gefragt: "Durch diese Aktion wird eine anstehende Datenaktualisierung abgebrochen. Fortfahren?"
Da kann ich nur OK für abrechen der Datenaktualisierung klicken oder Abbrechen.
Gibt es eine Möglichkeit dem Makro zu sagen, aktualisiere die Power Query, warte bis alles abgeschlossen ist und dann erst abspeichert usw?
Gruß,
David
Anzeige
AW: Power Query mit VBA aktualisieren
28.08.2019 14:15:57
Torsten
Sowieso kann ich nirgendwo irgendetwas sehen, wo ein power query aktualisiert wird. Wo ist der code dafuer?
AW: Power Query mit VBA aktualisieren
28.08.2019 14:15:58
Torsten
Sowieso kann ich nirgendwo irgendetwas sehen, wo ein power query aktualisiert wird. Wo ist der code dafuer?

109 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige