Liebe Forummitglieder
Ich bin ein Frischling hier und hoffe auf einige gute Tipps. Und zwar suche ich ein Makro für Folgendes:
1. In einem Ordner befinden sich eine Masterdatei sowie 100 einzelne Excelfiles, die jeweils im Arbeitsblatt 1 jeweils eine Tabelle enthalten.
2. Alle Tabellen sind genau gleich aufgebaut, aber haben unterschiedliche Daten drin.
3. Der Tabellenbereich geht jeweils von A9 bis A44.
4. Ich würde gerne ein Makro erstellen, das jede der einzelnen 100 Exceldateien öffnet, den Bereich A9 bis A44 markiert und dann jeweils UNTEREINANDER - ohne Leerzeilen dazwischen - in die Masterdatei "MASTER" kopiert.
5. Alle 100 Exceldateien sowie die Masterdatei "MASTER" sind im Verzeichnis "C:\Users\sunflower\Desktop\Makros" drin.
Ich bin nun auf der Suche nach einem Makro, das automatisch alle Dateien im Verzeichnis nacheinander öffnet (Dateinamen sind unterschiedlich), den Zellbereich A9 bis A44 kopiert und in die Masterdatei untereinander einfügt. Und dann automatisch wieder alle Dateien - ausser die Masterdatei - schliesst.
Ich arbeite mit Excel 2013.
Habt ihr ev. schon mal so ein Makro programmiert und wärt bereit, einem Neuling feste unter die Arme zu greifen?
Vielen Dank im voraus und sonnige Grüsse von sunflower!
Hi,
(10.06.2015, 22:24)Sunflower schrieb: [ -> ]Habt ihr ev. schon mal so ein Makro programmiert und wärt bereit, einem Neuling feste unter die Arme zu greifen?
Du könntest das auch per Formel lösen:
='[Daten 1.xlsx]Tabelle1'!$A$9
für den gesamten Bereich bis A44 erstellen
Jetzt noch das 1.xlsx verallgemeinern dann kannst Du den Formelblock 100x nach unten kopieren. Dann müssen die 100 Dateien nicht mal geöffnet werden.
[
attachment=2233]
[
attachment=2234]
[
attachment=2235]
[
attachment=2236]
Lieber Ralf
Vielen Dank für deine rasche Antwort! Ich bin nicht ganz sicher, ob ich deinen Lösungsvorschlag verstanden habe.
- Das Ding ist, dass die 100 Dateinamen ganz unterschiedlich sind und nicht numerisch abgeleitet werden können. Die Filenamen lauten folgendermassen "Name-Vorname_Dokumenttyp", wobei "Name-Vorname" jeweils anders lautet.
- Ich hatte vergessen zu erwähnen, dass es sich nicht nur um eine Spalte handelt, also A9 bis A44, sondern eine Tabelle, die auch noch von von T9 bis T44 reicht. Geht das dann trotzdem?
- Gibt es auch keine Probleme mit den Verknüpfungen, wenn man anschliessend mit Pivot-Tabellen arbeitet?
- Und noch ein weiteres Problem: Wenn ich meine Dokumente zu SharePoint migriere (von einem shared drive), was passiert dann mit den Verknüpfungen?
Fragen über Fragen..
Vielleicht weisst du da auch weiter? Falls nicht, kein Problem, ich möchte deine freie Zeit nicht allzu fest in Anspruch nehmen.
Gruss, sunflower
Hi,
(12.06.2015, 10:31)Sunflower schrieb: [ -> ]
- Das Ding ist, dass die 100 Dateinamen ganz unterschiedlich sind und nicht numerisch abgeleitet werden können. Die Filenamen lauten folgendermassen "Name-Vorname_Dokumenttyp", wobei "Name-Vorname" jeweils anders lautet.
- Ich hatte vergessen zu erwähnen, dass es sich nicht nur um eine Spalte handelt, also A9 bis A44, sondern eine Tabelle, die auch noch von von T9 bis T44 reicht. Geht das dann trotzdem?
- Gibt es auch keine Probleme mit den Verknüpfungen, wenn man anschliessend mit Pivot-Tabellen arbeitet?
- Und noch ein weiteres Problem: Wenn ich meine Dokumente zu SharePoint migriere (von einem shared drive), was passiert dann mit den Verknüpfungen?
Fragen über Fragen.. Vielleicht weisst du da auch weiter? Falls nicht, kein Problem, ich möchte deine freie Zeit nicht allzu fest in Anspruch nehmen.
zu den Fragen:
- Dann mußt Du jeden einzelnen Block markieren und mit suchen und ersetzen das "Datei1.xlsx" durch den Dateinamen ersetzen.
- Dann ziehst Du im Master die Linkspalte A so weit nach rechts, bis alle zu kopierenden Spalten enthalten sind (vor Schritt 1). Wenn es nur zwei Spalten sind, die A-Spalte nach rechts kopieren und mit suchen und ersetzen aus dem "$B" ein "$T" machen.
- Es gibt mMn keine Probleme.
- Es sollten dann halt alle Dateien bei Sharepoint liegen, da die Verlinkungen relativ sind.
Aber allgemein wird es mit einem Makro vermutlich einfacher sein. Da muß aber jemand anderes ran, das ist mir zu komplex.
Hallo Sunflower,
mit diesem Makro könntest Du alle Dateien eines Verzeichnisses einlesen. Ich habe reichlich kommentare drin, die die Funktion erklären. Ich gehe hier allerdings davon aus, dass der Bereich komplatt A9:T44 kopiert wird.
Code:
Option Explicit
Sub AlleEinlesen()
'Konstantendeklaration
'String
'fuer Pfad der Dateien - Beachte abschliessenden Backslash!
Const strPath$ = "D:\"
'Zielblatt in dieser Mappe
Const strSheet$ = "Tabelle1"
'Datenbereich in Quellmappe
Const strSRange$ = "A9:T44"
'Integer
'Zielspalte in dieser Mappe
Const iCol% = 1
'Variablendeklarationen
'Text
Dim strFile$
'Bildschirmflackern aus
Application.ScreenUpdating = False
'Verzeichnisinhalt auslesen
strFile = Dir(strPath & "*.xlsx")
'Schleife, solange ein Verzeichniseintrag gefunden wird
Do While strFile <> ""
'Quellmappe oeffnen
Workbooks.Open strPath & strFile
'Bereich kopieren und hier einfuegen
Range(strSRange).Copy ThisWorkbook.Sheets("strSheet").Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, iCol)
'Naechsten Verzeichniseintrag ermitteln
strFile = Dir()
'Quellmappe schliessen
ActiveWorkbook.Close False
'Ende Schleife, solange ein Verzeichniseintrag gefunden wird
Loop
'Bildschirmflackern ein
Application.ScreenUpdating = True
End Sub
Hi André,
(13.06.2015, 05:11)schauan schrieb: [ -> ]mit diesem Makro könntest Du alle Dateien eines Verzeichnisses einlesen. Ich habe reichlich kommentare drin, die die Funktion erklären. Ich gehe hier allerdings davon aus, dass der Bereich komplatt A9:T44 kopiert wird.
wenn ich das mit meinen hochgeladenen Dateien teste, dann kommt beim Einfügen des kopierten Bereiches der Fehler: Index außerhalb des gültigen Bereichs.
Ich habe dann zur Analyse den Copy-Teil abgetrennt:
PHP-Code:
Range(strSRange).Copy
ThisWorkbook.Sheets("strSheet").Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, iCol).Paste
Hallöchen,
ich hatte die Variablen nachträglich eingerabeitet
. Die Anführungszeichen um "strSheet" müssen weg.
Richtig ist also
Code:
Range(strSRange).Copy
ThisWorkbook.Sheets(strSheet).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, iCol).Paste
(13.06.2015, 11:57)schauan schrieb: [ -> ]ich hatte die Variablen nachträglich eingerabeitet . Die Anführungszeichen um "strSheet" müssen weg.
Richtig ist also
Code:
Range(strSRange).Copy
ThisWorkbook.Sheets(strSheet).Cells(Cells(Rows.Count, 1).End(xlUp).Row + 1, iCol).Paste
ich hatte es mir schon fast gedacht und auch vorhin schon getestet:
Fehlermeldung: Objekt unterstützt diese Eigenschaft oder Methode nicht!
Hallo Ralf,
entferne mal das Paste und füge vor dem Cells noch das Workbook ein.
Code:
Range(strSRange).Copy ThisWorkbook.Sheets(strSheet).Cells(ThisWorkbook.Sheets(strSheet).Cells(Rows.Count, 1).End(xlUp).Row + 1, iCol)
Hi Stefan,
(13.06.2015, 15:07)Steffl schrieb: [ -> ]entferne mal das Paste und füge vor dem Cells noch das Workbook ein.
Code:
Range(strSRange).Copy ThisWorkbook.Sheets(strSheet).Cells(ThisWorkbook.Sheets(strSheet).Cells(Rows.Count, 1).End(xlUp).Row + 1, iCol)
ich glaube, ich bin zu doof.
Jetzt kommt zwar keine Fehlermeldung mehr, dafür wird auch nix kopiert.
Hat das irgendwer schon mit den 4 Dateien ausprobiert?
Master.xlsb, Datei 1.xlsx, Datei 2.xlsx, Datei 3.xlsx
Dann bitte ein Mal das komplette funktionierende Makro posten.
Gut wäre es noch, wenn anstelle des fest verdrahteten Pfads
Const strPath$ = "D:\"
der Pfad der Master-Datei verwendet würde.