Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Daten-Aktualisierung mehrerer Excel-Duko

Betrifft: Daten-Aktualisierung mehrerer Excel-Duko von: websnake75
Geschrieben am: 07.11.2020 14:06:13

Hallo,

ich habe folgendes Problem:

In einem Ordner befinden sich mehrere Excel-Dokumente, welche alle per Abfrage (Power Query) Daten abrufen. Jedes Excel Dokument steht für ein Projekt. Kurz gesagt, die Anzahl der Excel-Dokumente variiert in meinem Ordner.

Im Moment sind es 30 Dateien. Ich möchte nun, wenn es irgendwie geht, z.B. per VBA mit einem Knopfdruck alle Dateien öffnen - dann die Abfrage aktualisieren - und wieder schließen und speichern.

Da ich mich mit VBA nicht auskenne, fällt mir nur ein Weg mittels Makro-Aufzeichnung ein. Ich müsste dann natürlich, wenn Projekte beendet sind, diese aus dem Makro rausnehmen und neue Projektdateien einpflegen.

Hier nun die Frage in die Runde der Fachleute, hat jemand eine bessere Idee?

Viele Grüße
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Hajo_Zi
Geschrieben am: 07.11.2020 14:12:54

Hallo Roland,

das öffnen und aktualisieren hast Du ja schon füge es in den Code ein.
Lese denm Ordner aus.
http://hajo-excel.de/gepackt/vba2007/ordner_auslesen_hyperlink2007_laufend.zip


Falls Link nicht ausgeführt wird:
1. Link kopieren
2. rechte Maustaste neues Fenter.
3. Umschaltstaste drücken und Klick auf den Link
4. STRG+ Link mit Maus aktivieren

GrußformelHomepage

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 07.11.2020 14:43:02

Wow Hajo das ging aber schnell, danke.
Eine Frage dazu:
Wie sollte ich die Ordner strukturieren?
Im Moment ist das so:

1. Z:/Ordner-Stammdaten
2. Z:/Ordner-Mitarbeiter
Die 30 Projekte-Dokumente liegen direkt unter Z:/

Sollten die Projekte Dokumente lieber in einen extra Ordner oder können die auf Z:/ liegenbleiben?
Und werden die Ordner Stammdaten und Mitarbeiter durch Dein VBA auch mit einbezogen? Das sollen sie nämlich nicht.

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Hajo_Zi
Geschrieben am: 07.11.2020 14:48:22

Hallo Roland,

Du mußt keinen Unterordner auslesen. Es werden aber alle DAteien in dem Ordner geöffnet.

Gruß Hajo

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 07.11.2020 15:50:35

Hallo Hajo,



also dsie Dateien aus dem Ordner werden gelistet aber leider schaffe ich es nicht, ihn zum aktualisieren der Dateien zu bringen.



Kannst Du mir sagen, was ich falsch mache?



Gruß Ronald



Hier einmal mein Beispiel

https://www.herber.de/bbs/user/141391.xlsm

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Hajo_Zi
Geschrieben am: 07.11.2020 15:54:20

Hallo Roland,

Du hast das öffnen nicht eingearbeit.
Der Code nach
For Each FI In EachFil
bis Next Fi
kann gelöscht werden und da kommt Dein Code zum öffnen aktualisieren hin. Beachte Au FI steht die Datei.

Gruß Hajo

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 07.11.2020 16:45:38

Hallo Hajo,
nach langem Suchen habe ich nun die Stelle für den Code gefunden, denke ich aber für das Öffnen hat er mir Folgendes aufgezeichnet:
    ChDir "Z:\HDFB GmbH"
    Workbooks.Open Filename:= _
        "Z:\HDFB GmbH\700000 - Uni Kita HGW Rohbau - Kostenstellenstand - v05.xlsx"

Das ist aber nur ein Projekt. Er soll das bei allen 30 machen.
Insgesamt sieht jetzt die Stelle so aus:
With Worksheets("Dateiverzeichnis")
        Loletzte = IIf(IsEmpty(.Cells(.Rows.Count, 1)), .Cells(Rows.Count, 1).End(xlUp).Row, . _
Rows.Count) + 1
        ' Dateien auslesen
        ' Dateiname schreiben
        If Chk_Datei_schreiben Or Chk_Hyperlink Then
                ChDir "Z:\HDFB GmbH"
                Workbooks.Open FileName:= _
                "Z:\HDFB GmbH\700000 - Uni Kita HGW Rohbau - Kostenstellenstand - v05.xlsx"
                ActiveWorkbook.RefreshAll
                Sheets("Auswertung-Std").Select
                Range("A3").Select
                ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
                Sheets("Übersicht").Select
                ActiveWorkbook.Save
                ActiveWindow.Close
            Next FI
            If StTyp = "*" And Chk_Datei = True Then
                With .Cells(Loletzte - 1, 6)
                    .Formula = "=SUM(D" & LoJ & ":D" & Loletzte - 1 & ")"
                    .NumberFormat = "#,##0"
                End With
            End If
        Else
Das wird doch so aber nicht funktionieren. Kannst Du bitte nochmal helfen?

Gruß Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Hajo_Zi
Geschrieben am: 07.11.2020 16:52:41

Hallo Roland,

Gut meine Hinweis zu FI möchtest Du nicht beachten.
Ich bin dann raus.

Gruß Hajo

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 07.11.2020 17:00:49

Hajo,
sehr gerne würde ich den beachten aber ich verstehe gar nicht was AU FI usw. bedeutet. Ich habe doch angegeben, dass keine VBA-Kenntnisse vorhanden sind.

Es tut mir leid, wenn ich Dich verärgert habe.

Gruß Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 07.11.2020 18:22:32

Hallo Ronald,

teste mal:

Option Explicit

Public Sub UpdateFiles()
    
    Const FOLDER_PATH As String = "Z:\HDFB GmbH\"
    
    Dim strFilename As String
    Dim objWorkbook As Workbook
    
    Application.ScreenUpdating = False
    
    strFilename = Dir$(FOLDER_PATH & "*.xlsx")
    
    Do Until strFilename = vbNullString
        
        Set objWorkbook = Workbooks.Open(Filoename:=FOLDER_PATH & strFilename, UpdateLinks:=3)
        
        Call objWorkbook.RefreshAll
        Call objWorkbook.Worksheets("Auswertung-Std").PivotTables("PivotTable1").PivotCache.Refresh
        Call objWorkbook.Close(SaveChanges:=True)
        
        Set objWorkbook = Nothing
        
        strFilename = Dir$
        
    Loop
    
    Application.ScreenUpdating = True
    
End Sub

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 08.11.2020 13:19:55

Hallo Nepumuk,

vielen Dank für Deine Hilfe. Wie ist Dein Code anzuwenden, ich meine ist der eigenständig oder muss er in Hajos eingebaut werden und wenn ja, wo muss er hin?

Kannst Du mir das vielleicht bitte noch mitteilen?

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 08.11.2020 13:22:25

Hallo Ronald,

das ist der einzige Code den du benötigst.

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 08.11.2020 13:40:24

Hallo Nepumuk,
ich habe getestet und nachdem ich in Deinem Code aus Filoename - Filename gemacht habe sehe ich auch, dass er Datei für Datei wie gewünscht duchgeht. Nur die Daten sind dann nicht aktualisiert.

Wenn ich in einer Projekt-Datei manuell auf Daten und Alle aktualisieren klicke, dann klappt es.

Hast Du eine Idee, woran das liegt? Die Deaktivierung externer Datenverbindungen ist abgeschaltet.

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 08.11.2020 13:44:02

Hallo Ronald,

kann ich ohne eine solche Mappe nicht sagen.

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 08.11.2020 13:47:07

Hallo Nepumuk,

reicht eine Mappe, oder brauchst Du alle Dateien und Ordner und wie kann ich Dir das senden?

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 08.11.2020 14:30:08

Hallo Ronald,

das wird nichts bringen, ich habe ja die Datenquelle nicht. Ändere mal den Code so:

Call objWorkbook.RefreshAll
DoEvents
Call objWorkbook.Worksheets("Auswertung-Std").PivotTables("PivotTable1").PivotCache.Refresh
DoEvents
Application.CalculateFullRebuild
DoEvents
Call objWorkbook.Close(SaveChanges:=True)

Vielleicht genügt das ja.

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 08.11.2020 18:16:00

Hallo Nepumuk,

leider hat es auch nicht funktioniert. Aber ich könnte Dir den Ordner inkl. Datenquellen schicken. Dann einen Ordner als Laufwerk Z freigeben und Du hättest die gleichen Voraussetzungen wie ich.

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 09.11.2020 08:43:30

Hallo Ronald,

schick mir die Dateien an:

Und gib dann hier laut wenn du sie versendet hast, sonst muss ich mich immer wieder bei WEB.de einloggen.

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 09.11.2020 08:57:38

Hallo Nepumuk,

die Daten habe ich per WeTransfer bereitgestellt.
Danke, dass Du Dir das ansiehst. Ich bin sehr gespannt.

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 09.11.2020 09:21:00

Hallo Ronald,

eine leere Mappe mit meinem Makro habe ich nicht gebraucht.

Mir ist aber noch etwas eingefallen. Teste mal:

Option Explicit

Private lastrFiles() As String
Private lialngIndex As Long
Private lobjWorkbook As Workbook

Public Sub UpdateFiles()
    
    Const FOLDER_PATH As String = "Z:\HDFB GmbH\"
    
    Dim strFilename As String
    Dim ialngIndex As Long
    
    Application.ScreenUpdating = False
    
    Erase lastrFiles
    lialngIndex = 0
    Set lobjWorkbook = Nothing
    
    strFilename = Dir$(FOLDER_PATH & "*.xlsx")
    
    Do Until strFilename = vbNullString
        
        Redim Preserve lastrFiles(ialngIndex)
        
        lastrFiles(ialngIndex) = FOLDER_PATH & strFilename
        
        strFilename = Dir$
        
    Loop
    
    Call OpenWorkbook
    
    Application.ScreenUpdating = True
    
End Sub

Private Sub OpenWorkbook()
    If lialngIndex <= UBound(lastrFiles) Then
        Set lobjWorkbook = Workbooks.Open(Filename:=lastrFiles(lialngIndex), UpdateLinks:=3)
        Call RefreshLinks
    Else
        Call MsgBox("Fertig.", vbInformation, "Information")
    End If
End Sub

Private Sub RefreshLinks()
    Call Application.OnTime(EarliestTime:=Now + TimeSerial(0, 0, 3), Procedure:="RefreshPivot", Schedule:=True)
    Call lobjWorkbook.RefreshAll
End Sub

Private Sub RefreshPivot()
    Call Application.OnTime(EarliestTime:=Now + TimeSerial(0, 0, 3), Procedure:="CloseWorkbook", Schedule:=True)
    Call lobjWorkbook.Worksheets("Auswertung-Std").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

Private Sub CloseWorkbook()
    Call lobjWorkbook.Close(SaveChanges:=True)
    Set lobjWorkbook = Nothing
    lialngIndex = lialngIndex + 1
    Call OpenWorkbook
End Sub

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 09.11.2020 09:32:10

Hallo Nepumuk,
der sagt Laufzeitfehler '91'
Debugging: Call lobjWorkbook.Close(SaveChanges:=True)

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 09.11.2020 09:44:14

Hallo Ronald,

ich habe das bei mir mit einer leeren Mappe getestet, da hat es funktioniert. hast du eventuell während das Programm lief eine der geöffneten Mappen manuell geschlossen? Du musst schon warten bis die MsgBox "Fertig" kommt.

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 09.11.2020 09:52:11

Hallo Nepumuk,
der Fehler kam als ich den VBA-Editor auf hatte und den Code da ausgeführt habe.

Nun habe ich alles zu gemacht und dann auf Makros und Ausführen. Dann ging kurz die letzte der 30 Dateien auf, die MsgBox "Fertig" kam aber aktualisiert hatte er nichts.

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Nepumuk
Geschrieben am: 09.11.2020 09:54:35

Hallo Ronald,

dann habe ich auch keine Idee mehr.

Gruß
Nepumuk

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 09.11.2020 09:59:41

Ok Nepumuk,

schade aber trotzdem super lieben Dank, dass Du das versucht hast zu lösen.

Gruß
Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Günther
Geschrieben am: 07.11.2020 23:20:44

Moin
[Zitat]Im Moment sind es 30 Dateien. Ich möchte nun, wenn es irgendwie geht, z.B. per VBA mit einem Knopfdruck alle Dateien öffnen - dann die Abfrage aktualisieren - und wieder schließen und speichern.
[/Zitat]
Und nun erzähle mir doch einmal, wozu das gut sein soll. Schließlich verwendest du doch nach deiner Aussage Power Query…
 
Gruß
Günther  |  mein Excel-Blog

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: websnake75
Geschrieben am: 08.11.2020 12:40:02

Hallo Günther,



das ist dazu gut, dass jedes Projekt von einem anderen Mitarbeiter betreut wird, der Zugriff auf diese zentral abgelegten Dokumente hat. Dann muss jeder Projekt-Mitarbeiter noch weitere Zahlen und andere Angaben zusätzlich zu den bestehenden abgefragten Daten machen. Die Projekt-Mitarbeiter können aber mangels vollwertiger Excel-Version die Abfragen nicht aktualisieren, dass geht nur von 2 Rechnern mit Office Pro. Und deshalb sollen die Projektdateien zentral per VBA aktualisiert werden. Ich hoffe, die Angaben tragen zur Aufklärung bei.



Gruß

Ronald

Betrifft: AW: Daten-Aktualisierung mehrerer Excel-Duko
von: Günther
Geschrieben am: 08.11.2020 13:32:57

Okay, so halbwegs verstehe ich das.
Mich irritierte deine Frage, weil PQ ja durchaus die Möglichkeit hat, beim öffnen alle eingebundenen Verknüpfungen zu aktualisieren.
 
Gruß
Günther  |  mein Excel-Blog

Beiträge aus dem Excel-Forum zum Thema "Daten-Aktualisierung mehrerer Excel-Duko"