Makro um Tabellen untereinander zu kopieren

[Sunflower]

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!

[Rabe]

Hi,

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.


  Master.xlsx (Größe: 12,71 KB / Downloads: 10)

  Daten 1.xlsx (Größe: 7,72 KB / Downloads: 8)

  Daten 2.xlsx (Größe: 7,71 KB / Downloads: 6)

  Daten 3.xlsx (Größe: 7,71 KB / Downloads: 5)

[Sunflower]

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

[Rabe]

Hi,

1. 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.
   
2. 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?
   
3. Gibt es auch keine Probleme mit den Verknüpfungen, wenn man anschliessend mit Pivot-Tabellen arbeitet?
   
4. 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:

1. Dann mußt Du jeden einzelnen Block markieren und mit suchen und ersetzen das "Datei1.xlsx" durch den Dateinamen ersetzen.
   
2. 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.
   
3. Es gibt mMn keine Probleme.
   
4. 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.

[schauan]

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

[Rabe]

Hi André,

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 


[schauan]

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

[Rabe]

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!

[Steffl]

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)

[Rabe]

Hi Stefan,

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.