Herbers Excel/VBA-Archiv

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

Spalte in andere Dateien kopieren + zurückkopieren

• Spalte in andere Dateien kopieren + zurückkopieren von Ingo vom 10.09.2019 16:04:27
• AW: Spalte in andere Dateien kopieren + zurückkopieren - von Piet am 12.09.2019 20:22:23
• AW: Spalte in andere Dateien kopieren + zurückkopieren - von Ingo am 13.09.2019 15:04:51
• AW: Spalte in andere Dateien kopieren + zurückkopieren - von Piet am 16.09.2019 22:40:53
• AW: Spalte in andere Dateien kopieren + zurückkopieren - von Ingo am 17.09.2019 14:13:25
• AW: Spalte in andere Dateien kopieren + zurückkopieren - von Piet am 14.09.2019 19:49:40
• AW: Spalte in andere Dateien kopieren + zurückkopieren - von Ingo am 16.09.2019 17:26:22

Betrifft: Spalte in andere Dateien kopieren + zurückkopieren von: Ingo
Geschrieben am: 10.09.2019 16:04:27

Hallo,
ich habe ein Anliegen, welches leider meine nicht-vorhandenen VBA-Kenntnisse bei weitem übersteigt. Ich habe mehrere Forenbeiträge gelesen, aber kann die Hinweise nicht recht umsetzen.

Problemstellung:
Ich habe 150 Datensätze in csv-Format (Komma-Trennung), alle sind identisch aufgebaut, mit einem Datenblatt (welches aber unterschiedlich heißt). Ich würde nun gerne aus jeder Datei zwei Spalten miteinander vergleichen (jeweils ca. 250 Zeilen) und für jede Zeile daraus einen Wert berechnen -steht ein Wert nur eine der beiden Zellen oder in beiden oder in keiner? Dadurch ergeben sich 4 Möglichkeiten (1 bis 4). Dazu habe ich eine Excel-Formel erstellt (s.u.)

Ziel:
Das ideale Ergebnis wäre, dass es eine Master-Datei gibt, die in dem gleichen Ordner, wie die 150 csv-Dateien liegt. Von dieser Masterdatei aus wird ein Makro ausgeführt, welches dazu führt, dass in der Masterdatei nun 150 Spalten nebeneinander erscheinen, jeweils mit einem Wert zwischen 1 und 4, entsprechend des Vergleichs.

Vorgehen:
Da habe ich nur eine schwammige Vorstellung, da ich auch gar nicht weiß, was Excel und VBA alles kann (wahrscheinlich mehr, als ich denke). Grundsätzlich hätte ich es in zwei Schritten gedacht:
1. In der Masterdatei gibt es eine Spalte, in der für alle benötigten Zeilen die Excel-Formeln stehen. Diese Spalte wird nun automatisch in jeden der 150 Datensätze in die erste freie Spalte nach den Daten kopiert – dadurch kann die Formel rechnen.
2. Dann wird diese Spalte in den 150 Datensätze wieder jeweils in die Masterdatei kopiert, diesmal aber als Wert. Jeweils eine Spalte pro Datensatz-Datei nebeneinander, so dass nun 150 Spalten nebeneinander sind. Vielleicht könnte man als erste Zeile noch den Dateinamen hinzufügen, damit die Zuordnung sichergestellt ist.

So könnte ich es mir vorstellen, aber vielleicht ist es für Excel-Experten offensichtlich, dass es ein viel besseres Vorgehen gibt. Da wäre ich komplett offen für neue Ideen, solange das gewünschte Ergebnis herauskommt.
Für eine Umwandlung von csv zu xlsx habe ich ein rudimentäres Makro gefunden, aber vielleicht kann man das ja direkt in die Prozedur integrieren.

Ich bin für jede Hilfe dankbar und hoffe, dass eine solche Frage nicht schon im Forum gestellt wurde – ich konnte leider nichts Passendes finden.

Vielen Dank!

Viele Grüße
Ingo

Excel-Formel: =WENN(UND(BS6="['space']";C6="['space']");1;WENN(UND(BS6="None";C6="['space']");2;WENN(UND(BS6="['space']";C6="None");3;4)))

Betrifft: AW: Spalte in andere Dateien kopieren + zurückkopieren von: 1712774.html
Geschrieben am: 12.09.2019 20:22:23

Hallo Ingo

mein Vorschlag ist, lade bitte die Masterdatei als Beispiel Datei hoch mit 2-3 Spalten mit einigen Verknüpfungen zu den CSV Dateien, dann können wir dir besser Rat und Hilfe geben. Kein Kollege wird die Datei für dich aufbauen, zumal wir auch nicht deinen Ordner Pfad kennen.

mfg Piet

Betrifft: AW: Spalte in andere Dateien kopieren + zurückkopieren von: 1712941.html
Geschrieben am: 13.09.2019 15:04:51

Hallo Piet,
vielen Dank für Deine Antwort.

Ich habe nun folgendes hochgeladen:
Beispieldatei.xlsx = so sehen alle Datensätze aus, nur mit unterschiedlichen Werten. In Spalte DO soll dann die Spalte mit den Excel-Formeln kopiert werden.
https://www.herber.de/bbs/user/132029.xlsx
Master.xlsx = Masterdatei. In Blatt „Tabelle2“ ist in Spalte DO die Excel-Formeln für jede Zeile hinterlegt. In Blatt "Tabelle1" werden die wieder zurückkopierten Spalten aus den einzelnen Datensätzen kopiert.
https://www.herber.de/bbs/user/132030.xlsx

Ich habe inzwischen weiter gesucht und bin etwas vorangekommen. Den Code habe ich relativ blind aus dem Netz kopiert, es kann also sein, dass er alles andere als elegant ist.

Für mich sind das drei Schritte (gerne auch als drei Makros):
1. csv in xlsx umwandeln
2. Die Spalte DO wird aus der Masterdatei in mehrere Datensätze kopiert (liegen im gleichen Verzeichnis).
3. Die Spalte DO aus den einzelnen Datensätzen wieder in die Masterdatei kopieren.

Was ich bisher getan habe:
Ein Makro für 1. > für die Umwandlung von csv zu xlsx:
Ein Makro für 3. > Spalte DO aus Datensätze in Master (ist transponiert, was für mich tatsächlich günstiger ist, aber das war nur Zufall, da der Code aus dem Netz ist)

Makro 1:

Sub umwandeln()
  
  Dim wb As Workbook
  Dim strFile As String, strDir As String
  
  strDir = "C:\Users\ingo.aberle\Desktop\Test\Daten\"
  strFile = Dir(strDir & "*.csv")
  
  Do While strFile <> ""
  
  Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=False)
  wb.SaveAs Replace(wb.FullName, ".csv", ".xlsx"), 51
  wb.Close True
  
  Set wb = Nothing
  strFile = Dir
  Loop
  
  End Sub

Makro 3:

Sub InMasterkopieren()
    Dim WB As Workbook
    Dim FName As String
    Dim Data, i As Long
    'Suche erste Datei
    FName = Dir(ThisWorkbook.Path & "\*.xls")
    'Datei gefunden?
    Do While FName <> ""
      'Ist es diese Datei?
      If FName = ThisWorkbook.Name Then GoTo NextFile
      'Datei öffnen
      Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & FName)
      'Daten lesen
      Data = WB.ActiveSheet.Range("DO1:DO255")
      'Datei zu
      WB.Close False
      'Daten von Zeilen in Spalten umwandeln
      Data = WorksheetFunction.Transpose(Data)
      'Daten in Tabelle schreiben
      i = i + 1
      Cells(i, 1).Resize(1, UBound(Data)) = Data
  NextFile:
      'Suche nächste Datei
      FName = Dir
    Loop
  End Sub

Was ich nicht hinbekomme:
Makro 2: Spalte DO wird aus der Masterdatei in mehrere Datensätze kopiert (liegen im gleichen Verzeichnis). In verschiedenen Foren finde ich nur Threads, die aus mehrere Dateien Daten in eine Datei zusammen kopieren wollen (wie Makro 3).
Makro 3: Dateiname in die erste Zelle (damit die Zuordnung möglich ist)

Ich hoffe, mein Anliegen ist durch die Dateien und die Beschreibung etwas klarer.

Für jeden Hinweis und Hilfe bin ich dankbar – und möchte natürlich auch meinen Teil zur Lösung beitragen.Vielen dank schon mal.

Viele Grüße
Ingo

Betrifft: AW: Spalte in andere Dateien kopieren + zurückkopieren von: 1713377.html
Geschrieben am: 16.09.2019 22:40:53

Hallo Ingo

freut mich das dir mein Makro gefaellt, ich habe es jetzt auf Transpose=True umgeschrieben. Teste es bitte mal.

Beachte bitte das ich im Makro zur Sicherheit die Zelle "IA1" mit CUT ausgeschnitten und nach "JA1" versetzt habe! - (255 Zeilen in Spalte DO!!)
Wenn du den Zeilenzaehler auf z=1 setzt wird die Formel sonst überschrieben! Zum testen steht z bei mir im Makro auf Start mit 2. Zeile!
Bei Transpose müssen wir ja auch über einen Zeilenzaehler nach unten statt Spaltenzaehler gehen.
PS - wenn ich mich zur Zeit lange nicht melde liegt es am Internet. War 2 Tage ausgefallen, wg. Strassenbauarbeiten!

mfg Piet

Option Explicit            '14..201   Piet    für Herber Forum
 'MsgBox Text für Überlauf
 Const ÜblTxt = "Spalten Überlauf in Masterliste! - Formel in 'IA1' wird überschrieben!"
 
 
 'Makro zum Formel in Spalte DO kopieren und Werte laden
 
 
 Sub Dateinamen_Auflisten()
   Dim temp As String, z As Integer
   Dim FormelDO As String, lzX As Long
   z = 2  'Start mit 2. Zeile Masterliste
  
   'On Error GoTo Fehler
   'Ordner Verzeichnis und Dateityp angeben
   temp = Dir(ThisWorkbook.Path & "\*.CSV*")
   Application.ScreenUpdating = False
 
   With ThisWorkbook.Worksheets("Masterliste")
       'alte Tabelle immer komplett löschen
       .UsedRange.Offset(3, 0).EntireRow.Delete
       'fertige Formel aus Zelle "IA" laden
        FormelDO = .Range("JA1").Formula
  
       Do While temp <> ""
          Workbooks.Open ThisWorkbook.Path & "\" & temp
          'LastZell in CSV Datei ermitteln (oder auf 255 setzen!)
          lzX = Workbooks(temp).Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row
          'Schutz vor Überschreiben der Formel in Zelle "IA1"
          If lzX >= 260 Then MsgBox lzX & "  " & ÜblTxt: Exit Do
          
          'Formel in Zelle DO2 kopieren und nach unten ziehen
          Workbooks(temp).Sheets(1).Range("DO2").Formula = FormelDO
          Workbooks(temp).Sheets(1).Range("DO2").Copy _
          Workbooks(temp).Sheets(1).Range("DO2:DO" & lzX)
            
          'CSV Spalte DO in Masterliste kopieren
          Workbooks(temp).Sheets(1).Range("DO2:DO" & lzX).Copy
         .Cells(z, 2).PasteSpecial xlPasteValues, Transpose:=True
          'CSV Dastei Namn in Zeile 1 schreiben
         .Cells(z, 1).Value = Workbooks(temp).Name
          Application.CutCopyMode = False
 
          'CSV Datei schliessen
          Workbooks(temp).Close False
          z = z + 1  'next Zeile setzen
          temp = Dir()
      Loop
   
       ThisWorkbook.Save  'Masterliste automatisch speichern
   End With
 Exit Sub
 
 Fehler: MsgBox "unerwarteter Laufzeit Fehler:  " & Err
 End Sub
 

Betrifft: AW: Spalte in andere Dateien kopieren + zurückkopieren von: 1713469.html
Geschrieben am: 17.09.2019 14:13:25

Hallo Piet,

ganz herzlichen Dank für Deine schnelle Unterstützung! Ich kann Dir gar nicht sagen, wie hilfreich das ist.

Leider komme ich berufsbedingt voraussichtlich erst am Freitag, vielleicht auch erst nächste Woche dazu, Dein Makro zu testen. Ich gebe Dir aber auf jeden Fall Feedback, sobald ich Zeit habe (vielleicht klappt es auch irgendwie vorher).

Viele Grüße
Ingo

Betrifft: AW: Spalte in andere Dateien kopieren + zurückkopieren von: 1713120.html
Geschrieben am: 14.09.2019 19:49:40

Hallo Ingo

anbei meine Masterdatei zum testen. Mit dem Beispiel 132030 konnte ich nichts anfangen, alles leere Tabellen.

Nach meiner Ansicht brauchst du die CSV Dateien NICHT in xls umwandeln, dieser Code liest auch CSV Dateien ein, sofern die Daten auf alle Spalten verteilt wurden. Nicht als zusammenhaengender Text in Spalte A stehen. Wenn du doch umwandeln musst bitte in meinem Makro den Dateityp von "CSV" auf "xls" umaendern.

Die Formel zum kopieren steht im Master Blatt in der Zelle "IA1". Da kannst du sie auch aendern. Solange es weniger als 230 Dateien sind wird sie nie überschrieben! Bei mehr erfolgt Abbruch. Würde mich freuen wenn der Code dir weiterhilft dein Problem zu lösen. Meine Variablen kannst du ja beliebig aendern.

https://www.herber.de/bbs/user/132039.xlsm - 132030 Masterdatei DO

mfg Piet - PS wenn alles klappt bitte den Thread schliessen, kein Haeckchen mehr setzen

Betrifft: AW: Spalte in andere Dateien kopieren + zurückkopieren von: 1713349.html
Geschrieben am: 16.09.2019 17:26:22

Hallo Piet,

ich bin mehr als begeistert, das Makro funktioniert wunderbar! Meine ewige Dankbarkeit ist Dir sicher!

Ich hätte noch eine letzte Bitte, falls das noch möglich wäre:
könnten die in die Masterdatei zurückkopierten Daten auch transponiert erscheinen, also nicht eine Spalte pro cvs-Datei, sondern eine Zeile?
Ich habe es mit

Transpose:=True

probiert, aber das hat nicht geklappt, es erscheint nur eine Zeile.

Bitte enschuldige, dass ich das jetzt noch so nachschiebe.

Viele Grüße
Ingo

Beiträge aus dem Excel-Forum zum Thema "Spalte in andere Dateien kopieren + zurückkopieren"