Excel/VBA "automatischer" Dateipfad

[sojamilch]

Hallo liebe Community!

Ich bin im Moment dabei ein Reporting zu erstellen, welches die letzten 12 Monate behinhaltet und monatlich aktualisiert werden soll. Dafür erhalte ich jeden Monat eine neue Exceldatei mit den Daten, welche dann in dem Report zusammengefasst werden. Dadurch beziehen sich die Kalkulationen in dem Report auf diese 12 unterschiedlichen Exceldateien. Da es nun aber sehr viele Kalkulationen sind, die in dem Report durchgeführt werden, wäre es sehr auffwendig jeden Monat die neue Datei manuell in die Funktionen einzugeben. Deshalb wollte ich fragen, ob es eine Möglichkeit gibt, die neue Datei automatisch in die Funktionen einzubetten.

Ich habe zunächst versucht ein Makro aufzunehmen und dann anzupassen.

Sub Makro()
Range("F4").Select
ActiveCell.FormulaR1C1 = _

        "=COUNTA('non_activated-2019-01.xlsx'!R2C1:R1048576C1)"
End Sub

So wüde bspw. eine der Funktionen aussehen.

Mein Ziel wäre es nun 'non_activated-2019-01.xlsx' durch eine Variable zu ersetzen, die dann auf die von mir gewünschte Datei Bezug nimmt, also in etwa so:

Sub Makro()
Dim Datei As String
Datei = C:\Dateipfad\Dateiname
 ActiveCell.FormulaR1C1 = _

        "=COUNTA(Datei!R2C1:R1048576C1)"
End Sub

Dabei ist wäre es auch kein Problem die Variable "Datei" monatlich manuell auf die korrekte Datei anzupassen. Hauptsächlich geht es darum, dass die Funktionen auf die von mir bestimmte Datei Bezug nehmen.

Ich wäre für jede Hilfe sehr dankbar :)

Viele Grüße

[Fennek]

Hallo,

machbar ist das sicher, ob es hier im Forum machbar ist mit den beschränkten Kommunikationsmöglichkeiten, ist offen.

Lade ein möglichst kleine Beispieldatei hoch, die das notwendige Verschieben der Spalten nachvollziehbar macht.

mfg

[sojamilch]

Also hier die Dateien:

Mappe1 ist das Reporting. Wie zu sehen ist, nimmt die Spalte "Jan 19" Bezug auf die Spalte in Mappe 3. Nun erhält man Mappe 2 im Februar und das Ziel ist es die in der "Feb 19" Spalte die selben Funktionen, wie in "Jan 19" zu haben, nur, dass sie sich nun auf Mappe 2 beziehen und nicht auf Mappe 3. Dieses soll jedoch so geschehen, dass man nicht per Hand den Dateipfad in den einzelenen Zellen anpassen muss, da im Original Reporting extrem viele solcher Kalkulationen sind.

[Ergil]

Hallo,

so wie ich das verstehe, willst du also auf eine variable Datei einen Bezug erstellen?
Wie genau dies funktionieren soll, weiß ich nicht, aber ich hätte eine alternative für dich:

Öffne über Power Query die Quelldatei, welche die variable sein soll, auf einem anderen Tabellenblatt der Datei, nimmst du also deine Formel auf das von Power Query generierte Tabellenblatt.
Du kannst dann anschließend die Quelldatei über Power Query beliebig oft ändern.

Wäre das eine Idee?

Ich mach das so (oder alternativ mit einem Makro, mit der selben Funktion) damit die Daten immer in einer lokalen Datei verarbeitet werden. Hat den Vorteil, wenn die Quelldaten auf einem Server liegen, werden Berechnungen länger wegen der Zugriffzeiten auf den Server oder das Netzwerk.

MfG

Ergil

[sojamilch]

Hallo Ergil,

dies würde aber zur Folge haben, dass sich die Report Datei stetig vergrößern würde oder nicht? Also für ein 12 monatiges rollierendes Reportiing würde dies bedeuten, dass alle 12 Tabellen in der Datei enthalten sein müsste, falls ich deinen Ansatz richtig verstanden habe?

Viele Grüße

[Fennek]

Hallo,

per Hand konnte ich den Wechsel des Dateinamens nicht auf eine Spalte begrenzen, mit VBA schon.

ICh hatte die Daten-Dateien in "Dennis Daten1.xlsx" und "Dennis Daten2.xlsx" umbenannt. Dann geht dieser Makro:

Code:

Sub T_1()
alt = "Daten1"
neu = "Daten2"

For i = 3 To 6
    Cells(i, "E").FormulaLocal = Replace(Cells(i, "F").FormulaLocal, alt, neu)
Next i
End Sub

Auf der Festplatte geht es, aber Zählenwenn erfordert, dass die Datei geöffnet ist.

Teste es mal in deiner Umgebung.

mfg

[sojamilch]

Hallo Fennek!

Das Skript klappt!

Vielen Dank und viele Grüße

[sojamilch]

Hallo

es ist doch noch ein Problem aufgetreten. Nämlich habe ich heute ein neues Datenblatt erhalten, welches ich dann in meinem Report analysieren wollte, jedoch berechnet mir Excel nur für die erste Funktion den korrekten Wert, für alle anderen (in dem hochgeladenen Beispiel wären es die drei weiteren Funktionen) erhalte ich den #Wert! Fehler. Ich verstehe nicht ganz wieso das passiert, da Excel mich beim ausführen des Makros bei jeder Zelle nochmal fragt auf welche Datei sich die Referenz bezieht und ich diese dann korrekt eingebe.

Viele Grüße

[schauan]

Hallöchen,

hast Du mal die Datenquelle analysiert, ob es

- doch am Dateinamen liegen könnte
- ob die Datei das verknüpfte Blatt enthält (manchmal reicht da schon ein unauffälliges Leerzeichen im Blattnamen
- ob eventuell schon in der Datenquelle dieser Fehler enthalten ist