Das Archiv des Excel-Forums
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
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
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 SubSub 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 SubMakro 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
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
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:=TrueBitte enschuldige, dass ich das jetzt noch so nachschiebe.
Viele Grüße
Ingo
