Datenimport aus geschlossenen Dateien?

[Peter Schuerer]

Hallo Zusammen,

XL2002.

Ich habe ca. 3 h gegoogelt, aber leider nichts passendes gefunden.

Ich soll die Daten einer Tabelle, aus mehreren Dateien, in einer anderen
Datei zusammenfassen.
Die einzelnen (geschlossenen Dateien) haben natürlich unterschiedliche
Namen und im Namen mal einen Unterstrich oder eine Leerstelle. Alle
Dateien stehen im gleichen Verzeichnis.

Also, aus den Dateien "Test 1.xls", "Test_2.xls", "Test 2_1.xls" ,"Test
3.xls", "Test_4_1.xls"..., die Werte aus den Tabellen "TestDaten" (alle
Tabellen haben den gleichen Aufbau) in die Datei "Auswertung.xls" einfügen.
Die Werte in den einzelnen Dateien gehen von A2 bis L..., die
Spaltenüberschriften stehen in A1:L1.
In Spalte A steht jeweils ein Datum.
Die Werte aus den einzelnen Dateien und der gleichen Tabelle sollen in
die Datei "Auswertung.xls" unten eingefügt werden und danach soll die
Tabelle nach Datum in Spalte A sortiert werden.

Ich hoffe man versteht mein Problem.

Danke und Gruß
Peter


---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

[Claus Busch]

Hallo Peter,

Post by Peter Schuerer
Ich soll die Daten einer Tabelle, aus mehreren Dateien, in einer anderen
Datei zusammenfassen.
Die einzelnen (geschlossenen Dateien) haben natürlich unterschiedliche
Namen und im Namen mal einen Unterstrich oder eine Leerstelle. Alle
Dateien stehen im gleichen Verzeichnis.

hier habe ich einen Link, der passen könnte:
http://www.office-loesung.de/ftopic166648_0_0_asc.php

Falls nicht, was spricht dagegen die Mappen im Hintergrund zu öffnen,
Daten zu kopieren, Mappen zu schließen?


Mit freundlichen Grüßen
Claus

[Peter Schuerer]

Hallo Claus,

Post by Claus Busch
http://www.office-loesung.de/ftopic166648_0_0_asc.php

Der Link ist super. Es gibt aber noch Probleme:

Public Sub HoleDaten_PH()
'Personalzeit aus P&H

Dim Pfad As String
Dim Dateiname As String
Dim Blatt As String

Pfad = "d:\eMail\Bach\Daten\"
'Der Name enthält eine Versionsnr., die sich ändert.
Dateiname = "PEP_Personal_2013_V12.xls"
Blatt = "DBPers"
'Die Daten werden immer ab A3 eingefügt, sollten aber in der ersten
leeren Zelle Spalte A eingefügt werden. Da mehrere Dateien.
If GetDataClosedWB(Pfad, _
Dateiname, _
Blatt, _
"A2:L6000", _
Worksheets("Tabelle1").Range("A3")) Then
MsgBox "Daten importiert"
End If
End Sub


Public Function GetDataClosedWB(SourcePath As String, _
SourceFile As String, _
sourceSheet As String, _
SourceRange As String, _
TargetRange As Range) As Boolean

'Holt einen Bereich aus einer _geschlossenen_ Arbeitsmappe
'Nur in VBA zu verwenden; nicht aus einer Tabellenzelle heraus
'© ***@mvps.org

Dim strQuelle As String
Dim Zeilen As Long
Dim Spalten As Byte

On Error GoTo InvalidInput

strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & _
sourceSheet & "'!" & _
Range(SourceRange).Cells(1, 1).Address(0, 0)

Zeilen = Range(SourceRange).Rows.Count
Spalten = Range(SourceRange).Columns.Count

With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
.Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
.Value = .Value
End With

GetDataClosedWB = True
Exit Function

InvalidInput:
MsgBox "Die Quelldatei oder der Quellbereich ist ungültig!", _
vbExclamation, "Get data from closed Workbook"
GetDataClosedWB = False
End Function

Da es mehrere Dateien sind, habe ich obiges Makro kopiert und
"HoleDaten" entsprechend erweitert.
Dann noch ein Makro welches die einzelnen "HoleDaten...." aufruft.
Funktioniert auch gut und schnell, aber die Daten werden immer ab "A3"
eingefügt.

Hoffe Du kannst helfen.

Danke und Gruß
Peter

---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

[Claus Busch]

Hallo Peter,

Post by Peter Schuerer
If GetDataClosedWB(Pfad, _
Dateiname, _
Blatt, _
"A2:L6000", _
Worksheets("Tabelle1").Range("A3")) Then

so auf die Schnelle und ungetestet:
If GetDataClosedWB(Pfad, _
Dateiname, _
Blatt, _
"A2:L6000", _
Worksheets("Tabelle1"). _
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)) Then

Du kannst auch gerne mal das Makro ausprobieren, das die Mappen öffnet,
Bereich übernimmt und Mappen schließt (Du musst nur den Pfad anpassen,
dann werden alle Dateien, die mti "Test" beginnen geöffnet und der
Bereich in Blatt "TestDaten" kopiert):

Sub DatenKopieren()
Dim objFSO As Object
Dim objOrdner As Object
Dim objDatei As Object
Dim FERow As Range

Const Pfad = "F:\Test\"

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOrdner = objFSO.GetFolder(Pfad)

On Error Resume Next
Application.ScreenUpdating = False
For Each objDatei In objOrdner.Files
If Left(objDatei.Name, 4) = "Test" Then
Workbooks.Open Pfad & objDatei.Name
With ActiveWorkbook.Sheets("TestDaten")
.Range("A2:L" & .UsedRange.Rows.Count).Copy _
ThisWorkbook.Sheets("Tabelle1") _
.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
ActiveWorkbook.Close savechanges:=False
End With
End If
Next
Application.ScreenUpdating = True
End Sub


Mit freundlichen Grüßen
Claus

[Peter Schuerer]

Post by Peter Schuerer
If GetDataClosedWB(Pfad, _
Dateiname, _
Blatt, _
"A2:L6000", _
Worksheets("Tabelle1"). _
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)) Then

Funktioniert wunderbar.
Noch ein kleines Problem: statt "A2:L6000" hätte ich gern so etwas,
"A2:L" & Cells(Rows.Count, 1).End(xlUp). Funktioniert aber so nicht.

Post by Peter Schuerer
Du kannst auch gerne mal das Makro ausprobieren, das die Mappen öffnet,

Das Makro probiere ich auch gleich aus.

Danke und Gruß
Peter

---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

[Claus Busch]

Hallo Peter,

Post by Peter Schuerer
Noch ein kleines Problem: statt "A2:L6000" hätte ich gern so etwas,
"A2:L" & Cells(Rows.Count, 1).End(xlUp). Funktioniert aber so nicht.

das geht bei der geschlossenen Datei nicht. Aber in der Funktion wird
doch der SourceRange exakt berechnet und es werden nur für den exakten
Bereich die Daten genommen.


Mit freundlichen Grüßen
Claus

[Peter Schuerer]

Hallo Claus,

Aber in der Funktion wird doch der SourceRange exakt berechnet und es werden nur für den exakten
Bereich die Daten genommen.

Du hast wieder einmal Recht. Leider gibt es noch ein kleines Problem.

In der Funktion steht:
With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
.Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
.Value = .Value
End With

Dieses .Value = .Value macht aus dem Inhalt der Zellen einen Wert und
zerschießt das Datum in Spalte A.

02.11.2013
02.12.2013
02.12.2013
2/13/2013
2/13/2013
2/14/2013
2/14/2013

Ich habe das .Value = .Value auskommentiert, mit .Copy und .PasteSpecial
den Bereich kopiert und nur die Werte eingefügt.
Damit wird zwar das Datum in Spalte A richtig angezeigt, aber der
eingefügte Bereich geht (A3 + Strg Pfeil runter) bis A6001.

Gruß
Peter

---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

[Claus Busch]

Hallo Peter,

Post by Peter Schuerer
Dieses .Value = .Value macht aus dem Inhalt der Zellen einen Wert und
zerschießt das Datum in Spalte A.

nein, das Datum ist noch korrekt. Nur das Format stimmt nicht mehr.
Ändere den Code mal um:

Public Sub HoleDaten_PH()
'Personalzeit aus P&H

Dim Pfad As String
Dim Dateiname As String
Dim Blatt As String
Dim LRow As Long

Pfad = "F:\Test\"
'Der Name enthält eine Versionsnr., die sich ändert.
Dateiname = "Test2_1.xls"
Blatt = "TestDaten"

If GetDataClosedWB(Pfad, _
Dateiname, _
Blatt, _
"A2:L2000", _
Worksheets("Tabelle1") _
.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)) Then
MsgBox "Daten importiert"
End If
With Sheets("Tabelle1")
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:A" & LRow).NumberFormat = "DD/MM/YYYY"
End With
End Sub


Mit freundlichen Grüßen
Claus

[Beate Goebel]

Peter Schuerer schrieb am 24 Okt 2013

Hast Du Ahnung von Datenbankabfragen? (Access?)

Post by Peter Schuerer
Also, aus den Dateien "Test 1.xls", "Test_2.xls", "Test 2_1.xls"
,"Test 3.xls", "Test_4_1.xls"..., die Werte aus den Tabellen
"TestDaten" (alle Tabellen haben den gleichen Aufbau) in die Datei
"Auswertung.xls" einfügen. Die Werte in den einzelnen Dateien
gehen von A2 bis L..., die Spaltenüberschriften stehen in A1:L1.
In Spalte A steht jeweils ein Datum.

Kann das Datum als Verbindungsfeld für die Dateien funktionieren?

Post by Peter Schuerer
Die Werte aus den einzelnen Dateien und der gleichen Tabelle
sollen in die Datei "Auswertung.xls" unten eingefügt werden und
danach soll die Tabelle nach Datum in Spalte A sortiert werden.

Schau Dir mal in Excel den Query an: Menüpunkt Daten, dort externe
Daten, aus anderen Quellen: MS Query.

Eine Datei laden und Durchklicken bis Du beim Query bist. Die Dateien
dazu laden und ein Feld wählen, über das die Tabellen zusammen hängen
(Schlüsselfeld).
Und dann ein DB-Abfrage machen, wie in Access zB.

Vorsicht: Das Tool ist von IBM. Also gilt GroßrechnerSQL:
Wildcart "*" wird zu "%", "?" wird zu "_" (Unterstrich).
Textabfragen sind mit "Wie" einzuleiten.

HTH
Beate

[Beate Goebel]

Beate Goebel schrieb am 24 Okt 2013

Post by Beate Goebel

Post by Peter Schuerer
Die Werte aus den einzelnen Dateien und der gleichen Tabelle
sollen in die Datei "Auswertung.xls" unten eingefügt werden und
danach soll die Tabelle nach Datum in Spalte A sortiert werden.

Schau Dir mal in Excel den Query an: Menüpunkt Daten, dort externe
Daten, aus anderen Quellen: MS Query.

https://office.microsoft.com/de-de/excel-help/verwenden-von-microsoft-query-zum-abrufen-externer-daten-HA010099664.aspx

Beate

[Peter Schuerer]

Hallo Beate,

Post by Beate Goebel
Peter Schuerer schrieb am 24 Okt 2013
Hast Du Ahnung von Datenbankabfragen? (Access?)

Leider nein :-(
Weiß auch nicht ob es so funktionieren würde. Die einzelnen Dateien habe
im Namen eine Versionsnummer die sich ständig ändert.

Danke und Gruß
Peter

---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

[Beate Goebel]

Peter Schuerer schrieb am 25 Okt 2013

Post by Peter Schuerer

Post by Beate Goebel
Peter Schuerer schrieb am 24 Okt 2013
Hast Du Ahnung von Datenbankabfragen? (Access?)

Leider nein :-(
Weiß auch nicht ob es so funktionieren würde. Die einzelnen
Dateien habe im Namen eine Versionsnummer die sich ständig ändert.

Einer DB-Abfrage ist der Dateiname egal. Da kommt es nur auf den Inhalt
und die Verbindung mittels Schlüsselfeld an.

Schade, wäre die schnellste und sicherste Methode.

Beate

[Jörg Eisenträger]

Ich soll die Daten einer Tabelle, aus mehreren (geschlossenen) Dateien, in einer anderen
Datei zusammenfassen.

Hallo Peter,

gemäß der hier veröffentlichten Beschreibung:

https://groups.google.com/forum/?hl=de#!topic/microsoft.public.de.excel/yOKNpuueGXM

habe ich vor einiger Zeit mal eine Datei erstellt, die genau das macht.
Du kannst Dir diese Datei (eine *.xlsm) von meiner Website holen:
http://joergei.de/mso/excel_werte_aus_vielen_dateien.zip

In den Spalten A - C werden die Pfade, Dateinamen und Blattnamen
aufgelistet. (Für Pfade und Dateinamen ist das Tool ClipName sehr gut
geeignet.)

In die Zeile 6 werden horizontal die Zelladressen der auszulesenden
Zellen eingetragen, in Zeile 5 kannst Du noch angeben, was sie bedeuten.

Das Makro (Schaltfläche) holt Dir dann die Werte.

Auf diese Art und Weise hast Du alle interessierenden Werte zunächst in
einer einzigen Datei. Die Weiterverarbeitung ist dann Deine Sache.


Gruß
Jörg

[Peter Schuerer]

Hallo Jörg,

Post by Jörg Eisenträger
In die Zeile 6 werden horizontal die Zelladressen der auszulesenden
Zellen eingetragen, in Zeile 5 kannst Du noch angeben, was sie bedeuten.

Es funktioniert zwar, aber ich brauche die Daten nicht in Zeilen sondern
in Spalten.
Außerdem sind es pro Datei weit über 400 Zeilen die eingelesen werden
müssen.

Danke und Gruß
Peter


---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

[Jörg Eisenträger]

Post by Peter Schuerer
Es funktioniert zwar, aber ich brauche die Daten nicht in Zeilen sondern
in Spalten.

Eine Konvertierung von Zeilen in Spalten sollte wohl kein Problem sein.

Post by Peter Schuerer
Außerdem sind es pro Datei weit über 400 Zeilen die eingelesen werden
müssen.

Ja, da hast Du bei XL2000 schlechte Karten. XL2010 hat über 16000
Spalten, das hätte Dir gereicht.

Der Vorteil meiner Lösung besteht halt darin, dass die Dateinamen und
Pfade sowie die auszulesenden Zellen x-beliebig sein können und nichts
im Code vorgegeben werden muss.

Natürlich könnte man bei Bedarf das Makro auch so umschreiben, dass die
Dateien waagerecht und die Werte senkrecht stehen. Das mache ich jetzt
aber nicht.

Allen ein schönes Wochenende.


Gruß
Jörg