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,

Am Thu, 24 Oct 2013 20:47:45 +0200 schrieb 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
--
Win XP Prof SP3 / Vista Ultimate SP2
Office 2003 SP3 /2007 Ultimate SP3

[Jörg Eisenträger]

On Thu, 24 Oct 2013 20:47:45 +0200, Peter Schuerer <pit...@gmx.net>
wrote:

>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
--
"One of the best ways to boost your Excel efficiency is also one of the best ways to shoot yourself in the foot."
(Susan Harkins auf www.techrepublic.com/blog/10things/10-mistakes-to-avoid-when-working-with-multiple-worksheets/1961 )

[Peter Schuerer]

Hallo Claus,

Am 24.10.2013 21:21, schrieb Claus Busch:
>
> hier habe ich einen Link, der passen könnte:
> 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
'© t.r...@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.

[Peter Schuerer]

Hallo Beate,

Am 24.10.2013 21:25, schrieb 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.

[Claus Busch]

Hallo Peter,

Am Fri, 25 Oct 2013 12:43:28 +0200 schrieb 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

[Peter Schuerer]

Hallo Jörg,

Am 24.10.2013 22:36, schrieb 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.

[Peter Schuerer]

Am 25.10.2013 12:57, schrieb Claus Busch:

> 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

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.

> Du kannst auch gerne mal das Makro ausprobieren, das die Mappen öffnet,

Das Makro probiere ich auch gleich aus.

[Claus Busch]

Hallo Peter,

Am Fri, 25 Oct 2013 16:25:42 +0200 schrieb 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.

[Peter Schuerer]

Hallo Claus,

Am 25.10.2013 16:37, schrieb Claus Busch:

> 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.

[Claus Busch]

Hallo Peter,

Am Fri, 25 Oct 2013 18:30:20 +0200 schrieb 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

[Jörg Eisenträger]

>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.

>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.