Exceltabellen auslesen, in neuer Exceltabelle ergänzen

[medic]

Hallo!
Ich habe folgende Problematik:

- Ich erhalte wöchentlich aus mehreren Betrieben eine Exceltabelle (z.B. KW21.xls oder KW43.xls) mit Auslastungen aus verschiedenen (vergleichbaren) Abteilungen (z.B. Abteilung A bis F). Die Daten liegen dort in einem Arbeitsblatt, das immer denselben Namen (Arbeitsauslastung) trägt.
- Ich möchte für jeden Betrieb die einzelne Wochenmeldungen in eine "Mastertabelle Betrieb" einlesen, in der das neue bzw. die vorhandenen Arbeitsblätter ergänzt wird. Also Arbeitsblatt KW21 mit den Inhalten aus der Datei KW21.xls -> Benennung Arbeitsblatt = Dateiname ohne „.xls“.
- In dieser Mastertabelle möchte ich die Ergebnisse der Abteilungen, die sich vergleichen lassen, ebenfalls als zusätzliches Arbeitsblatt wochenweise aufführen. Z.B. Abteilung A mit den Summen aus den einzelnen Kalenderwochen, Abteilung B mit den Summen aus….
- und zu guter Letzt´ möchte ich durch eine "Mastermastertabelle (:-D)  die Arbeitsblätter der Abteilungen auslesen und die Ergebnisse zusammenführen.
- supergenial wäre es, wenn ich in den jeweiligen Mastertabellen z.B. durch Angabe in einer Zelle bestimmen könnte, welche Dateien in den Arbeitsblättern dargestellt werden sollen (z.B. KW 31 bis KW40), sprich welche Wochen für einen Vergleich genutzt werden sollen.

Ich hab schon viel gesurft und gegoogelt, aber meine VBA Kenntnisse sind völlig eingerostet (letzmalig ca. 1995 ein bisschen mit VBA rumgespielt).
Ich hoffe es war einigermaßen verständlich und es findet sich jemand, der ein paar Ideen hat. Gerne auch zu den einzelnen Punkten!

Viele Dank im Voraus!!!

[Officer]

Hallo medic,

da Deine Frage sehr umfangreich und die Lösung auch relativ komplex ist, wird eine Lösung im Forum schwer bis unmöglich werden.

Viele Grüße

Officer

[medic]

Hallo officer,
das habe ich fast befürchtet. Aber es war ein Versuch wert. Vielleicht hat ja jemand "Teillösungen". Eine große Hilfe wäre z.B. das Einlesen der Exceltabellen (KWs) in eine Exceltabelle. Das würde schonmal viel Zeit und Handarbeit sparen

Grüße

medic

[Officer]

Hallo medic,

gerne kann ich Dir anbieten, dass Du mir Deine beiden Dateien (Quell-Datei und Zieldatei) per E-Mail zukommen lässt und kurz beschreibst, welches Tabellenblatt aus der Quell-Datei ausgelesen und in die Zieldatei übertragen werden soll.

Die E-Mail findest Du in meinem Profil.

Viele Grüße

Question

[Officer]

Hallo medic,

ich habe den Import der Dateien gelöst und habe dir auch bereits die entsprechende E-Mail mit Lösung und zukommen lassen.

Vollständigkeitshalber hier auch noch den Code sowie die Beispieldatei.

Der Code zum importieren der gewünschten Arbeitsmappen sieht wie folgt aus:

Code: Visual Basic

1. Sub copy_KWsheet()
2.  
3. '** Kopieren des ersten Sheets aus der angegebenen KW-Arbeitsmappe
4. '** Dimensionierung der Variabeln
5. Dim strPath As String
6.  
7. '** Vorgaben definieren
8. Set wbact = ThisWorkbook
9. Set wsmas = ThisWorkbook.Sheets("Master")
10.  
11. '** Vorbereiten
12. With Application
13.   .ScreenUpdating = False
14.   .Calculation = xlCalculationManual
15. End With
16.  
17. '** Durchlaufen der Tabellen (KW) - Liste
18. For a = 9 To 26
19.  
20.   '** Nur ausführen, wenn ein Tabellenblattname vorhanden ist
21.  If wsmas.Cells(a, 2).Value <> "" Then
22.          
23.     '** Pfad zusammenbauen
24.    strPath = wsmas.Range("B6").Value & wsmas.Cells(a, 2).Value
25.    
26.     '** Blattnamen auslesen
27.    wsname = Left(wsmas.Cells(a, 2).Value, InStr(1, wsmas.Cells(a, 2).Value, ".") - 1)
28.    
29.     '** Prüfen, ob das Sheet bereits vorhanden ist, wenn ja, vorher löschen
30.    Dim blatt As Object
31.     For Each blatt In Sheets
32.       If blatt.Name = wsname Then
33.         Application.DisplayAlerts = False
34.         blatt.Delete
35.         Application.DisplayAlerts = True
36.       End If
37.     Next blatt
38.    
39.     '** Position des neuen Blattes ermitteln
40.    i = Worksheets.Count
41.    
42.     '** KW-Datei öffnen
43.    Workbooks.Open strPath
44.    
45.     '** Blatt kopieren
46.    With ActiveWorkbook
47.       .Sheets(1).Select
48.       .Sheets(1).Copy After:=Workbooks(wbact.Name).Sheets(i)
49.       .Close savechanges:=True
50.     End With
51.  
52.     '** Namen für neues Sheet setzen
53.    Workbooks(wbact.Name).Activate
54.     ActiveSheet.Name = wsname
55.  
56.   End If
57.  
58. Next a
59.  
60. '** Ursprungszustand herstellen
61. wsmas.Select
62. With Application
63.   .ScreenUpdating = True
64.   .Calculation = xlCalculationAutomatic
65. End With
66.  
67. '** Hinweis
68. MsgBox "Die Dateien wurden korrekt importiert!", vbInformation, "Hinweis"
69.  
70. End Sub
71.  

Diesen Code bitte in ein Modul kopieren.

Als Anlage beigefügt findest du auch die Beispieldatei.

[medic]

Hallo.
Ich hab nun ein wenig rumprobiert und das Ganze funktioniert irgendwie nicht so richtig. Ich hab in das Verzeichnis eine andere xls reinkopiert (kw11.xls) und auch den Namen in das Feld B9 eingetragen. Das funktionierte der Import nicht (laufzeitfehler '1004' die Datei würde in dem Verzeichnis nicht existieren). Die korrekte Schreibweise in B9 und Slash am Ende hab ich beachtet.

Ich würde gerne die Dateien über eine Verkettung generieren lassen. Hierzu müsste die Berechnung auf automatisch belassen werden.

Ich hoffe ich bin nicht zu lästig   

[Officer]

Hallo medic,

ich habe die Datei so abgeändert, dass die Berechnung nicht deaktiviert wird.
Also bei mir werden die Datein korrekt eingelesen, vorausgesetzt der der Pfad in Zelle B6 und die Dateinamen ab Zelle B9 werden korrekt eingetragen. Den geänderten Code findest Du in der Anlage.

Viele Grüße

Officer

[medic]

Also wenn ich die Testdateien nutze, dass funktioniert es. Wenn ich aber Test KW30_B.xls in Test KW30_C.xls ändere und dementsprechend in dem Excel-Sheet anpasse, kommt die Fehlermeldung wie oben.

Die zellen A29 bis B38 sind ja irrelevant, oder?

[admin]

Hallo medic,

ja, die Zellen A29:B39 sind irrelevant.

Es muss ausschließlich der korrekte Pfad und der korrekte Dateiname ab B9 eingetragen werden.

Viele Grüße

Officer

[medic]

Also das macht mich echt noch ganz kirre.

Ich habe nun noch mal alles neu runtergeladen und in einem nuenen Verzeichnis abgelegt. Den Pfad pber den explorer kopier (Vermeidung von Tipfehlern) und die Dateinamen benefalls kopiert.
Dann habe ich folgende Dateien angelegt: Test KW29_A.xls, Test KW29_B.xls und Test KW30_C.xls. Die ersten 2 (A & B) werden importiert. C nicht mehr: Laufzeitfehler '1004' Die Datei sei nicht vorhanden. Hab dann auch die Reihenfolge geändert. Erst A, dann C und dann B -> A wird importiert, danach Laufzeitfehler und die Datei existiert definitiv! Ich habe lediglich die B kopiert und unter C gespeichert.
Mit anderen Dateinamen z.B. kw29.xls kommt gleich die Fehlermeldung *esistzumverzweifeln*

[Officer]

Hallo medic,

bitte schicke mir per E-Mail deine Excel-Datei sowie Deine 3 zu importierenden Dateien zu. Ich werde sehen, woran es liegt.

Viele Grüße

Officer

[medic]

Jetzt funktioniert es!
Alles Klasse, alles super.
Wer lesen kann ist eindeutig im Vorteil!

Die Pfadangabe zu dem Jahr stimmte nicht und Excel blieb quasi im "Verzeichnis drüber" hängen. Hier befanden sich per Zufall noch die 2 Testdateien, die erfolgreich importiert wurden und danach fehlten ja die restlichen Dateien
Excel kam also nie im richtigen Verzeichnis an und mir ist nicht aufgefallen, dass bei der Fehlermeldung der Pfad nicht stimmte!.

Mit gesegntem Haupt gehe ich ehrführchtig auf die Knie und sage Danke!

[medic]

Eine Kleinigkeit habe ich noch: Es wird immer das erste Arbeitsblatt (links) ausgelesen bzw. importiert. Wo kann ich im Script das Arbeitsblatt benennen, wenn in der auszulesenden Datei mehrere Arbeitsblätter sind (es sollen nicht mehrere Abreitsblätter aus einer Datei ausgelesen werden!)?

Hallo medic,

ich habe den Import der Dateien gelöst und habe dir auch bereits die entsprechende E-Mail mit Lösung und zukommen lassen.

Vollständigkeitshalber hier auch noch den Code sowie die Beispieldatei.

Der Code zum importieren der gewünschten Arbeitsmappen sieht wie folgt aus:

Code: Visual Basic

1. Sub copy_KWsheet()
2.  
3. '** Kopieren des ersten Sheets aus der angegebenen KW-Arbeitsmappe
4. '** Dimensionierung der Variabeln
5. Dim strPath As String
6.  
7. '** Vorgaben definieren
8. Set wbact = ThisWorkbook
9. Set wsmas = ThisWorkbook.Sheets("Master")
10.  
11. '** Vorbereiten
12. With Application
13.   .ScreenUpdating = False
14.   .Calculation = xlCalculationManual
15. End With
16.  
17. '** Durchlaufen der Tabellen (KW) - Liste
18. For a = 9 To 26
19.  
20.   '** Nur ausführen, wenn ein Tabellenblattname vorhanden ist
21.  If wsmas.Cells(a, 2).Value <> "" Then
22.          
23.     '** Pfad zusammenbauen
24.    strPath = wsmas.Range("B6").Value & wsmas.Cells(a, 2).Value
25.    
26.     '** Blattnamen auslesen
27.    wsname = Left(wsmas.Cells(a, 2).Value, InStr(1, wsmas.Cells(a, 2).Value, ".") - 1)
28.    
29.     '** Prüfen, ob das Sheet bereits vorhanden ist, wenn ja, vorher löschen
30.    Dim blatt As Object
31.     For Each blatt In Sheets
32.       If blatt.Name = wsname Then
33.         Application.DisplayAlerts = False
34.         blatt.Delete
35.         Application.DisplayAlerts = True
36.       End If
37.     Next blatt
38.    
39.     '** Position des neuen Blattes ermitteln
40.    i = Worksheets.Count
41.    
42.     '** KW-Datei öffnen
43.    Workbooks.Open strPath
44.    
45.     '** Blatt kopieren
46.    With ActiveWorkbook
47.       .Sheets(1).Select
48.       .Sheets(1).Copy After:=Workbooks(wbact.Name).Sheets(i)
49.       .Close savechanges:=True
50.     End With
51.  
52.     '** Namen für neues Sheet setzen
53.    Workbooks(wbact.Name).Activate
54.     ActiveSheet.Name = wsname
55.  
56.   End If
57.  
58. Next a
59.  
60. '** Ursprungszustand herstellen
61. wsmas.Select
62. With Application
63.   .ScreenUpdating = True
64.   .Calculation = xlCalculationAutomatic
65. End With
66.  
67. '** Hinweis
68. MsgBox "Die Dateien wurden korrekt importiert!", vbInformation, "Hinweis"
69.  
70. End Sub
71.  

Diesen Code bitte in ein Modul kopieren.

Als Anlage beigefügt findest du auch die Beispieldatei.

[Officer]

Hallo medic,

schau bitte in Zeile 47 und 48 im Code. Dort stehe "Sheets(1)"

Wenn Du die 1 auf 3 abänderst, wird das dritte Tabellenblatt kopiert.

Ich hoffe, das hilft Dir weiter.

Gruß

Officer

[medic]

Ja Danke, das passt auch. Dachte, dass ich vieleicht auch den Namen des Arbeitsblattes eintragen kann. Aber so funktioniert es auch.

[maninweb]

Hallo medic...

den Namen der Tabelle kannst Du auch verwenden. Die Objektauflistung Sheets() erwartet entweder
einen Index als Angabe oder den Namen der Tabelle. Spielereien...

Code: Visual Basic

1.   Public Const cstSheetName  As String = "Tabelle2"
2.  
3.   Public Sub Test()
4.  
5.     MsgBox ThisWorkbook.Sheets(1).Name
6.    
7.     MsgBox ThisWorkbook.Sheets("Tabelle1").Name
8.    
9.     MsgBox ThisWorkbook.Sheets(cstSheetName).Name
10.    
11.     MsgBox ThisWorkbook.Sheets(cstSheetName).Index
12.    
13.   End Sub
14.  

Gruß