VBA ordner wählen / Excel 2010

[nusre]

Hallo zusammen,

bräuchte erneut Hilfe.

Im Netzwerk gibt es einen Ordner mit 2019. Dann jeweils für jeden Monat ein weiteres Ordner mit 01 02 03 usw.
In dem Ordner (z.b. aktuell für Februar) werden täglich Excel Dateien hinterlegt mit einem Tagesbericht.

Zum Beispiel 01022019.xlsx - 02022019.xlsx - 03022019.xlsx

Jedoch sind es keine Tabellen sondern Rohdaten, bei der die erste Zelle immer A9 ist. Die erste Zeile beinhaltet das Datum und forlaufend mit den dazugehörigen Daten. Hänge eine Beispiel Datei als Anhang.

Jetzt habe ich eine Datei, bei der ich mit VBA einfach zur Auswahl habe, dass ich die Datei an dem Tag wähle und es fügt die in meine Excel Datei fortlaufend ein.

Also ich öffne meine eigene Datei (mit der ich Pivot Tabellen erstellen möchte) > wähle die Datei für den Tag aus > die Daten ab A9:O9 bis letzte Zeile werden eingelesen > werden in meine Datei und ans Ende der vorliegenden vom Vortag einfach hinzugefügt.

Mit Power Query/ Microsoft Query wäre eine möglichkeit gegeben, jedoch ist es aus verschiedenen Gründen nicht möglich, da wir den Zusatz als Add On nicht installieren dürfen. Wenn es nur eine einzige Datei wäre und jeden Tag von der selben Datei ausgelesen werden müsste, wäre die Handhabung auch etwas leichter, jedoch ist es wie gesagt, jeden Tag eine andere Datei, die Ausgelesen werden soll. Wichtig ist dabei auch, dass es ans Ende meiner Datei / nach der letzten Zeile (bestimmter Sheet) einfügt und kein neues Sheet erstellt.

Mit Excel kenne ich mich eigentlich gut aus aber VBA ist da eine andere Sache.
Hat einer Vorschläge, wie man es hinbekommt oder vielleicht andere möglichkeiten?

Danke schon im Voraus

 

[Maaspuck]

Hi,

gehe ich recht in der Annahme, dass Du eine automatisierte Lösung haben möchtest, die stets alle seit dem letzten Aufruf noch nicht eingelesenen Tagesfiles einliest und den Inhalt an Deine bestehende Tabelle anhängt? Du schreibst das nicht explizit, daher kann ich nur spekulieren.

Sollte dies der Fall sein, so sollte ein Makro doch recht einfach feststellen können, welches das zuletzt eingelesenen Datum ist und daraufhin alle weiteren Dateien nacheinander einliest. Da die Dateinamen und die Verzeichnisse einer strikten Logik folgen, kannst Du die Verzeichnisnamen und Dateinamen sogar quasi "berechnen".

Gruß

 

[nusre]

Hallo,

deine Annahme ist richtig. Das wäre eines der Möglichkeiten. Macros wiederholen doch nur das was du aufzeichnest. Woher soll Macro wissen welche Datei ich am nächsten Tag öffnen möchte? Oder liege ich da falsch?

 

[Maaspuck]

das Makro öffnet die Zieldatei und scannt die Tabelle von oben nach unten, bis es auf eine leere Zeile trifft. In der ersten Spalte der vorletzten Zeile steht ja dann das Datum, welches zuletzt eingelesen wurde. Dann ermittelt das Makro das aktuelle Datum und kann dank der hervorragenden Datenstruktur automatisch alle zwischenzeitlich bis zum aktuellen Datum erstellten Rohdateien einlesen. Das setzt natürlich voraus, dass Du wirklich alle Dateien einlesen möchtest.
Solltest Du nur einige von Dir ausgewählte Dateien einlesen wollen, wird es natürlich etwas umständlicher, da Du dies dem Makro mitteilen musst. Da könnte man sich natürlich mehrere Strategien vorstellen:

a) das Makro scannt die Verzeichnisse und erstellt eine Auflistung aller Dateien in einem Fenster und Du kannst die gewünschten Dateien auflisten. Das kann natürlich beliebig aufwändig werden, ist aber grundsätzlich denkbar.

b) etwas einfacher dürfte sein, in einem ersten Makro die Verzeichnisse rekursiv zu scannen und alle Dateien in einem weiteren Tabellenblatt aufzulisten (z.B. Spalte B). Dann könntest Du mit einem "X" in Spalte A alle Zeilen mit Dateien markieren, die Du einlesen möchtest. Danach startest Du dann ein zweites Makro, welches den Import dann durchführt.

 

[nusre]

Hallo Maaspuck,

wie genau meinst du das mit Verzeichnisse rekursiv zu scannen? Das wäre eigentlich die Lösung. Ich Scanne den Ordner Inhalt und importiere die Daten von der jeweiligen Datei. Wie stelle ich das bei dem Makro ein, dass er den Inhalt des Ordners einscannt?

 

[Maaspuck]

Glücklicherweise muss man das Rad ja nicht zweimal erfinden. "excel vba verzeichnis auslesen" bei google eingegeben und der erste link war gleich perfekt.

https://www.makro-excel.de/2017/01/...sses-mit-vba-auslesen-und-in-excel-schreiben/

Da ist genau erklärt, was gemacht werden muss. Ich habe die Beispieldatei heruntergeladen, im Code kurz das Stammverzeichnis angepasst und es funktioniert perfekt.

Du musst dann letztendlich in einer Schleife nur noch die aufgeführten Dateien nacheinander öffnen und die Daten importieren.

Viel Erfolg

 

[nusre]

Hallo zusammen,

habe hier ein Code der auch super funktioniert, jedoch muss eine Kleinigkeit geändert werden. Bei den Dateien von der die Daten gelesen werden, ist die erste Zelle immer A9:Q9 bis unbekannt. Wo genau und was muss geändert werden?

Sub kopieren()

Dim firstRowHeaders As Boolean
Dim fso As Object
Dim dir As Object
Dim filename As Variant
Dim wb As Workbook
Dim s As Sheet1
Dim thisSheet As Sheet1
Dim lastUsedRow As Range
Dim file As String

On Error GoTo ErrMsg

Application.ScreenUpdating = False
firstRowHeaders = True 'Change from True to False if there are no
headers in the first row

Set fso = CreateObject("Scripting.FileSystemObject")

'PLEASE NOTE: Change <<Full path to your Excel files folder>> to
the path to the folder containing your Excel files to merge
Set dir = fso.Getfolder("C:\Users\Nusre\Desktop\Test\")

Set thisSheet = ThisWorkbook.ActiveSheet

For Each filename In dir.Files
'Open the spreadsheet in ReadOnly mode
Set wb = Application.Workbooks.Open(filename, ReadOnly:=True)

'Copy the used range (i.e. cells with data) from the opened
spreadsheet
If firstRowHeaders And i > 0 Then 'Only include headers from
the first spreadsheet
Dim mr As Integer
mr = wb.ActiveSheet.UsedRange.Rows.Count
wb.ActiveSheet.UsedRange.Offset(1, 0).Resize(mr - 1).Copy
Else
wb.ActiveSheet.UsedRange.Copy
End If

'Paste after the last used cell in the master spreadsheet
If Application.Version < "12.0" Then 'Excel 2007 introduced
more rows
Set lastUsedRow = thisSheet.Range("A65536").End(xlUp)
Else
Set lastUsedRow = thisSheet.Range("A1048576").End(xlUp)
End If

'Only offset by 1 if there are current rows with data in them
If thisSheet.UsedRange.Rows.Count > 1 Or
Application.CountA(thisSheet.Rows(1)) Then
Set lastUsedRow = lastUsedRow.Offset(1, 0)
End If
lastUsedRow.PasteSpecial
Application.CutCopyMode = False
Next filename

ThisWorkbook.Save
Set wb = Nothing

#If Mac Then
'Do nothing. Closing workbooks fails on Mac for some reason
#Else
'Close the workbooks except this one
For Each filename In dir.Files
file = Right(filename, Len(filename) - InStrRev(filename,
Application.PathSeparator, , 1))
Workbooks(file).Close SaveChanges:=False
Next filename
#End If

Application.ScreenUpdating = True
ErrMsg:
If Err.Number <> 0 Then
MsgBox "There was an error. Please try again. [" &
Err.Description & "]"
End If
End Sub

Bedanke mich schon mal im voraus an die Experten

 

[cumulonimbus8]

ist die erste Zelle immer A9:Q9 bis unbekannt.

A9:Q9 ist ein Range und keine Zelle.

Set lastUsedRow = thisSheet.Range("Annn").End(xlUp)
ist eine (Code)Zeile die zumindest eine Zeile (Row) definiert. Und zwar für Spalte A. Kann das A9 werden..? 9 wäre demnach die letzte benutzte Zeile wenn A9 verwendet wird.

Set lastUsedRow = lastUsedRow.Offset(1, 0)
greift erneut auf eine Zeile zurück. und könnte auf A9 ändern wenn vorher A8 zurückgemeldet wurde. Offset(1,0) ≈ ›Verschiebung‹ um 1 Zeile nach unten und 0 Spalten nach rechts.

CN8

 

[nusre]

Hi,

meinte Natürlich A9 ist die erste Zelle und die Spalte Q9 bis nach unten, die immer variiert, da die Daten täglich einen anderen Umfang haben. Das mit der letzten Zeile habe ich verstanden. Wo muss ich aber was ändern, damit es die Daten erst ab A9 ausliest?

 

[cumulonimbus8]

Einfache Frage:
A9 ist der Anker ab dem bis zu Spalte Q eine nicht bekannte Anzahl von »Zeilen« (ein sog. Range von A9 bis Q-noch-herauszufinden) an ein Ziel zu kopieren ist?
Woran kann ganz sicher diese Anzhal an Zeilen erkannt werden, ist das von A9 bis Q? ein festes Gefüge von dem aus mir auch Spalte A die bestimmte Zeilenanzahl liefert?

Ach, ja dieser Bereich (›Range‹) soll in eine ganze Reihe an Mappen kopiert werden..? → For Each filename In dir.Files

Ich würde eine solche Aufgabe (unter bestimmten eigenen Ansprüchen) anders programmieren…

CN8

 

[nusre]

Wie?