Herbers Excel-Forum - das Archiv

2 kleine Kopierfragen

Bild

Betrifft: 2 kleine Kopierfragen
von: Boris

Geschrieben am: 15.12.2006 12:23:08
Hallo,
1) muss eine Datei geöffnet sein, damit man Daten kopieren kann?
Workbooks("C:\..\Quelldatei.xls").Open
Workbooks("C:\..\Quelldatei.xls").Sheets(1).Range("A1").Copy ThisWorkbook.Sheets(1).Range("A1")
Workbooks("C:\..\Quelldatei.xls").Close
würde ich gerne duch das ersetzen:
Workbooks("C:\..\Quelldatei.xls").Sheets(1).Range("A1").Copy ThisWorkbook.Sheets(1).Range("A1")
Funktioniert aber nicht..?
2) Kann man folgendes auch eleganter lösen?
wksQuelle.Range("A2:Z" & LetzteZeile).Copy
wksZiel.Cells(FreieZeile, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Ich möchte nur die Zellinhalte kopieren, ohne Formatierung. "PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=False" hat mir der Macro Recorder ausgespuckt. Allerdings wird dadurch auch der Zielbereich automatisch markiert, was ich gerne vermeiden würde...
Gruß,
Boris
Bild

Betrifft: AW: 2 kleine Kopierfragen
von: Klaus M.vdT.

Geschrieben am: 15.12.2006 13:15:17
Hallo Boris,
zu 2)
Der Zielbereich wird nicht wirklich markiert, es handelt sich um den "CutCopyMode" der auch beim händischen kopieren zu sehen ist. Diesen kannst du gezielt wieder abschalten.
Probier mal so:
wksQuelle.Range("A2:Z" & LetzteZeile).Copy
wksZiel.Cells(FreieZeile, 2).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

zu 1):
keine Ahnung, darum noch offen.
Gruß,
Klaus M.vdT.
Bild

Betrifft: AW: 2 kleine Kopierfragen
von: fcs

Geschrieben am: 15.12.2006 14:38:12
Hallo Boris,
zu 1.
das öffnen der Datei aus der kopiert werden soll kann man umgehen, indem man in die Zielzelle per Makro eine entsprechende Formel einträgt und anschließend die Formel durch den Zellwert ersetzt. Statt der Variablen kann man natürlich auch die entsprechenden Texte für Pfad und Quelldatei in der Anweisung einfügen und die Variablen weglassen.
Den Namen für das 1. Tablenblatt ggf. anpassen. Falls der Name wechselt, dann haste Pech gehabt und diese Methode funktioniert nicht.
Pfad = "C:\Daten" ' oder auch = ThisWorkbook.Path
Quelldatei = "Quelldatei.xls"
ThisWorkbook.Sheets(1).Range("A1").FormulaLocal = "='" & Pfad & "\[" & Quelldatei & "]Tabelle1'!$A$1"
ThisWorkbook.Sheets(1).Range("A1").Value = ThisWorkbook.Sheets(1).Range("A1").Value
zu 2.: direkte Zuweisung der Werte im Zielbereich
wksZiel.Cells(FreieZeile, 2).Range("A1:Z" & LetzteZeile - 1).Value = wksQuelle.Range("A2:Z" & LetzteZeile).Value

Gruß
Franz
Bild

Betrifft: AW: 2 kleine Kopierfragen
von: Boris

Geschrieben am: 15.12.2006 15:59:35
Vielen Dank,
funzt beides prächtig. Auch das mit dem application.cutcopymode ist gut zu wissen. Noch ein paar weitere Fragen:
zu 2)
"wksZiel.Cells(2, 2).Range("A1:C3").Value" entspricht also der angebenen "Range-Größe" relativ zu Zelle(2,2), also range("B2:D4")? Richtig?
Könnte man die linke und rechte Seite der "=-Formel" auch vertauschen? Also:
wksQuelle.Range("A2:Z" & LetzteZeile).Value = wksZiel.Cells(FreieZeile, 2).Range("A1:Z" & LetzteZeile - 1).Value
zu 1)
a) Deinen Ausführungen zufolge ist das hier ohne ein Öffnen der Datei also nicht möglich: "Kopiere Sheets 1-5 einer Quelldatei ans Ende dieser Zieldatei"?
b) Ich öffne mit folgendem Code nacheinander mehrere xl-Dateien und übertrage Daten mit Deiner Formal aus 2):
...
Mappe = Dir(strPfad & "*.xls")
Do While Mappe <> ""
Workbooks.Open Mappe, UpdateLinks:=0
Set wksQuelle = Workbooks(Mappe).Sheets("RR")
LetzteZeile = wksQuelle.Cells(Rows.Count, 1).End(xlUp).Row
FreieZeile = wksZiel.Cells(Rows.Count, 1).End(xlUp).Row + 1
wksZiel.Cells(FreieZeile, 2).Range("A1:Z" & LetzteZeile - 1).Value = wksQuelle.Range("A2:Z" & LetzteZeile).Value
Workbooks(Mappe).Close savechanges:=False
Mappe = Dir
Loop
Jetzt müsste es doch funktionieren, das entsprechend deiner Formula-Methode ohne jeweiliges Öffnen der Dateien zu lösen, oder könnte es da Probleme geben? Oder ist gegen meine Lösung nichts einzuwenden?
Gruß, Boris
Bild

Betrifft: AW: 2 kleine Kopierfragen
von: fcs

Geschrieben am: 15.12.2006 20:35:53
Hallo Boris

zu 2)
"wksZiel.Cells(2, 2).Range("A1:C3").Value" entspricht also der angebenen "Range-Größe" relativ zu Zelle(2,2), also range("B2:D4")? Richtig?

Das ist richtig!

Könnte man die linke und rechte Seite der "=-Formel" auch vertauschen? Also:
wksQuelle.Range("A2:Z" & LetzteZeile).Value = wksZiel.Cells(FreieZeile, 2).Range("A1:Z" & LetzteZeile - 1).Value

Im Prinzip ja, aber du muss jetzt natürlich aufpassen, welche Werte du den Variablen LetzteZeile und FreieZeile zuweist. Die Werte werden dann aus wksZiel nach wksQuelle geschrieben.

zu 1)
a) Deinen Ausführungen zufolge ist das hier ohne ein Öffnen der Datei also nicht möglich: "Kopiere Sheets 1-5 einer Quelldatei ans Ende dieser Zieldatei"?

Kopieren im eigentlichen Sinne funktioniert nur bei geöffneten Dateien.
Man kann natürlich in einer Schleife sämtliche Zellen der Tabellen nacheinander abarbeiten. Problem es muss in der Tabelle eine Spalte vorhanden sein, in der jede Zeile Daten enthält, damit man mit Funktion ANZAHL2 die Zeilenzahl in der Tabelle ermitteln kann. Lösungsvorschlag siehe unten.

b) Ich öffne mit folgendem Code nacheinander mehrere xl-Dateien und übertrage Daten mit Deiner Formal aus 2):
...
Jetzt müsste es doch funktionieren, das entsprechend deiner Formula-Methode ohne jeweiliges Öffnen der Dateien zu lösen, oder könnte es da Probleme geben? Oder ist gegen meine Lösung nichts einzuwenden?

Der Code ist so prinzipiell ok, jedoch würde ich die Aktualiserung des Bildschirms abschalten und bei Workbooks.Open den Pfad mit reinnehmen und die Datei als ReadOnly öffnen.
Allerdingsverstehe ich nicht, warum du FreieZeile in Spalte 1 von wksQuelle ermittelst und die Daten dann beginnend in Spalte 2 einträgst. Beim Testen werden bei mir so die Daten aus den 5 Dateien alle in den gleichen Zellbereich geschrieben. Oder ist das absichtlich so gewollt?
Application.ScreenUpdating = False
Do While MAPPE <> ""
Workbooks.Open strPfad & MAPPE, UpdateLinks:=0, ReadOnly:=True
Set wksQuelle = Workbooks(MAPPE).Sheets("RR")
LetzteZeile = wksQuelle.Cells(Rows.Count, 1).End(xlUp).Row
FreieZeile = wksZiel.Cells(Rows.Count, 1).End(xlUp).Row + 1
wksZiel.Cells(FreieZeile, 2).Range("A1:Z" & LetzteZeile - 1).Value = wksQuelle.Range("A2:Z" & LetzteZeile).Value
Workbooks(MAPPE).Close savechanges:=False
MAPPE = Dir
Loop
Application.ScreenUpdating = True

Hier ein Beispiel wie du die Daten aus den RR-Tabellen der Dateien ohne Öffnen der Dateien einlesen könntest. Allerdings ist diese Methode relativ langsam, sollte also nur bei kleineren Datenmengen verwendet werden oder wenn das Öffnen der Quelldatei sehr viel Zeit erfordert.
Sub TestFormel()
'Einstellungen zum testen
Dim MAPPE, strPfad As String, wksZiel As Worksheet, SpalteQ As Integer, ZeileQ As Long
Dim FreieZeile As Long, LetzteZeile As Long, I As Long
strPfad = "C:\Test\Daten\"
MAPPE = Dir(strPfad & "*.xls")
Set wksZiel = ActiveWorkbook.Worksheets("Tabelle1")
Application.ScreenUpdating = False
Do While MAPPE <> ""
FreieZeile = wksZiel.Cells(Rows.Count, 1).End(xlUp).Row + 1
'ZeilenZahl in geschlossener Quell-Datei Tabelle "RR" ermitteln, funktioniert nur, _
wenn in Spalte A in jeder Zeile ein Eintrag vorhanden ist
wksZiel.Cells(FreieZeile, 2).FormulaLocal = "=ANZAHL2('" & strPfad & "[" & MAPPE & "]RR'!A:A)"
LetzteZeile = wksZiel.Cells(FreieZeile, 2).Value
I = 0
For ZeileQ = 2 To LetzteZeile
For SpalteQ = 1 To 26 '(A bis Z)
wksZiel.Cells(FreieZeile + I, SpalteQ + 1).FormulaR1C1 = "='" & strPfad & "[" & MAPPE & "]RR'!R" & ZeileQ & "C" & SpalteQ
wksZiel.Cells(FreieZeile + I, SpalteQ + 1).Value = wksZiel.Cells(FreieZeile + I, SpalteQ + 1).Value
Next
I = I + 1
Next
MAPPE = Dir
Loop
Application.ScreenUpdating = True
End Sub

Gruß
Franz
Bild

Betrifft: AW: 2 kleine Kopierfragen
von: Boris

Geschrieben am: 19.12.2006 09:44:41
Hallo Franz,
vielen Dank für Deine Ausführungen, waren alle sehr hilfreich für mich. Das Auslesen der Daten ohne Öffnen der Dateien kommt in meinem Anwendungsfall nur zur Anwendung, wenn ich einzelne Zellen bzw. kleine Bereiche auslese. Innerhalb der "Do While Mappe <> "", Loop"-Schleife ist es "ökonomischer", die Dateien zu öffnen. In meinem Beispiel wird in Spalte A der Zieldatei noch zusätzlich der Dateiname geschrieben, so daß es bei mir nicht zu einer "Überschreibung" führt:))
Zu der Mappen-Schleife habe ich noch eine Frage, da ich Sie hier aus dem Forum übernommen habe, und nicht sicher bin, was da genau passiert:
Sub AktualisiereRR()
Dim strRwPfadAbsolut
Dim Mappe As String
Dim i As Long
Dim LetzteZeile As Long
Dim FreieZeile As Long
Dim LW As String
Dim wksZiel As Worksheet
Dim wksQuelle As Worksheet
Set wksZiel = ThisWorkbook.Sheets("RR Raw")
'''Löschen der alten Datensätze (Datensätze beginnen ab Zeile 6):
LetzteZeile = wksZiel.Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To LetzteZeile
wksZiel.Rows(i).ClearContents
Next i
LW = Left(ThisWorkbook.Path, 3) & "\"
ChDrive LW
'''strRwPfadRelativ ist als gobale Const definiert:
strRwPfadAbsolut = ThisWorkbook.Path & strRWPfadRelativ
ChDir strRwPfadAbsolut
Mappe = Dir(strRwPfadAbsolut & "*.xls")
Do While Mappe <> ""
Workbooks.Open Mappe, UpdateLinks:=0
Set wksQuelle = Workbooks(Mappe).Sheets("RR")
LetzteZeile = wksQuelle.Cells(Rows.Count, 1).End(xlUp).Row
FreieZeile = wksZiel.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Ermittlung der ID aus dem Dateinamen und kopieren in Spalte A
For i = FreieZeile To FreieZeile + LetzteZeile - 2
wksZiel.Cells(i, 1) = Left(Workbooks(Mappe).Name, 2)
Next i
'Kopieren der restlichen Spalten
wksZiel.Cells(FreieZeile, 2).Range("A1:Z" & LetzteZeile - 1).Value = wksQuelle.Range("A2:Z" & LetzteZeile).Value
Workbooks(Mappe).Close savechanges:=False
Mappe = Dir
Loop
End Sub

Hierzu nun folgende Fragen:
1) Sollte man die beiden "for i"-Schleifen ersetzen, z.B. über eine variable Range()-Anweisung?
2) Die ChDrive und ChDir Anweisungen können weggelassen werden, wenn ich (so wie Du vorgeschlagen) Workbooks.Open strRwPfadAbsolut & Mappe verwende? (Habe mich immer gefragt, wofür man diese ChDrive und ChDir Anweisung braucht...)
3) Was macht "Mappe = Dir(strPfad & "*.xls")" und "Mappe = Dir"... ist mir nicht ganz klar. Ist Mappe ein Str-Array mit den einzelnen Dateien?
4) Im angegebenen Verzeichnis liegen numerierte xl-Dateien im Format "#.xls", z.B.: 1, 3, 6, 7, 10, 15, 21, 23, usw. Die Dateien werden über den Mappen-Befehl nun in "Binär-Reihenfolge" geöffnet, also: 1, 10, 15, 21, 23, 3, 6, 7. Wie schaff ich es, dass sie "dezimal" geöffnet werden?
Nun ja, mal wieder Fragen über Fragen...
Gruß, Boris
 Bild