Datum in Formelbezugspfad

Question

Hallo miteinander,

nachdem ich hier schon ein paar mal echt super Tipps bekommen habe wende ich mich mal wieder vertrauensvoll an die Profis.

Mein Probelm ist folgendes:

Ich muss monatlich Zahlen des aktuellen Monats mit den Zahlen des Vormonats in Excel vergleichen.

Die Ausgangsdatei (des aktuellen Monats, i.d.F. 04) wird in einem Monatsordner (immer gleiche Namensgebung für Datei und Tabellenblatt) gespeichert, bspw.:
'U:\Ordner\Tätigkeit\Monatsordner 201004\Dateiname.xlsx

Die Zahl in dieser Ausgangsdatei soll mit der Zahl aus dem Vormat verglichen werden, die in folgendem Ordner liegt:
'U:\Ordner\Tätigkeit\Monatsordner 201003\Dateiname.xlsx

Das einzige was sich ändert ist der Monat (bei Jahreswechsel das Jahr). Der Dateinamen, Tabellenblatt und Position der zu vergleichenden Zahl bleiben gleich.

Der Bezug auf die Zelle (L7) des Vormonats lautet:
='U:\Ordner\Tätigkeit\Monatsordner 201003\[Dateiname.xlsx]Tabellenblatt'!L7

Ich habe nun versucht über "&" und Teil() - Formeln die Ordnerbenennung (JJJJMM) abzuändern und den Bezug so herzustellen, aber das funktioniert nicht.

Hat jemand einen Tipp oder Trick?

Herzlichen Dank schon mal.

Grüße

Markus

Answer 1

Hallo,

hast du auch die Funktion Hyperlink genutzt?

Bsp:
=HYPERLINK("U:\Ordner\Tätigkeit\Monatsordner "&TEIL(C8;34;6)+1&"\Dateiname.xlsx")

In C8 steht dabei dein ursprünglicher Link.
Hier wird dann um ein Monat erhöht - aus 201003 wird 201004.

Gruß

M.O.

Answer 2

Servus M.O.,

herzlichen Dank für die schnelle Antwort. leider hat es mein Problem noch nicht gelöst, evtl. habe ich mich mißverständlich ausgedrückt.

Ich möchte, dass der Wert ausZelle (L7) des Vormonats aus
(='U:\Ordner\Tätigkeit\Monatsordner 201003\[Dateiname.xlsx]Tabellenblatt'!L7)
in meiner aktuellen Datei ('U:\Ordner\Tätigkeit\Monatsordner 201004\Dateiname.xlsx) erscheint so dass ich mit diesem Wert arbeiten kann.

Über den Hyperlink kann ich zwar die Vormonatsdatei öffnen aber direkt der Wert wird nicht übernommen, oder?

Gruß

Markus

Answer 3

Hallo Markus,

habe dich falsch verstanden, liegt wohl an der Hitze :-).

Wenn du den Bezug zu einer Zelle variabel gestalten willst, musst du die Funktion INDIREKT benutzen. Diese funktioniert aber nur bei geöffneten Dateien. Ist die Datei geschlossen, so erhälst du eine Fehlermeldung.
Eine Lösung ist meines Wissens in einem solchen Fall nur per VBA möglich.

Gruß

M.O.

Answer 4

Hallo M.O.,

herzlichen Dank für Deine Mühen.

Zwei Dateien gleichen Namens zu öffnen ist in Excel nicht möglich, es sei denn man macht ein neues Excel auf. Da kann ich bei der jetzt verwendeten Copy Paste Version bleiben.

Ein Tipp für eine Lösung in VBA hast Du nicht, oder?

Falls Nein, alle VBA Profis bitte helfen.

Viele sonnige Grüße
Markus

Answer 5

Hallo Markus,

für eine VBA-Lösung bräuchte man ein paar mehr Informationen:
In welcher Zelle ist die Verknüpfung zur Datei des Vormonats hinterlegt? Oder soll der Vormonat aus dem Namen der aktuellen Datei ermittelt werden?
In welche Zelle soll die Verknüpfung eingefügt werden?

Gruß

M.O.

Answer 6

Hallo,

Die Werte aus dem Vormonat sollen in den Spalten "R" und "T" beginnend in Zeile 6 bis Zeile 91 eingefügt/angezeigt werden.

Die Datei heisst jeden Monat gleich, nur der Ordner wird jeden Monat kopiert umbenannt auf den aktuellen Monat, das aktuelle Excel-Sheet aus Access in den Spalten G bis L neu befüllt.

Ein Vergleich in Access scheidet aus, da in der aktuellen Version noch händisch korrigiert werden muss. Eine Rücktransferierung der Daten in Access aufgrund des Sheet Aufbaus ausscheidet.

Im Prinzip müsste der Bezug zum Vormonatsfeld etwa in der Art aussehen:
(='U:\Ordner\Tätigkeit\Monatsordner (201004 -1)\[Dateiname.xlsx]Tabellenblatt'!L6)
Wobei die Zelle L6 des Vormonats in Zelle T6 des aktuelle Monats ausgegeben werden sollte.

Der Vormonat sollte also aus dem Pfad der aktuellen Datei (wie ein paar Zeilen weiter oben dargestellt) "abgeleitet" werden.

Hoffe das hilft einigermaßen weiter ansonsten einfach melden ich schau dass ich es dann etwas verständlicher und ausführlicher darstellen kann.

VG

Markus

Answer 7

Hallo Markus,

mal sehen, was ich machen kann.

Gruß

M.O.

Answer 8

Hallo Markus,

doch noch eine Nachfrage:
T6 des aktuellen Monats = L6 des Vormonats
und welcher Bezug zum Vormonat soll in R6 hergestellt werden?

Gruß

M.O.

Answer 9

Hallo,

hat etwas gedauert, tut mir leid.

R6 des aktuellen Monats = G6 des Vormonats

Das ganze dient dazu die Entwicklung einzelner Positionen zwischen den Monaten darzustellen.

Danke Dir und geniesse bitte auch das super Wetter draussen.

Viele Grüße

Markus

Answer 10

Hallo Markus,

nachdem ich noch etwas das Wetter genossen habe, hier ein Code (allerdings nicht groß getestet):

Sub Verknuepfung()

Dim wbpfad, npfad, rpfad As String
Dim j, m, zeile As Long

'Pfad der aktuellen Datei wird eingelesen
wbpfad = ThisWorkbook.Path

'Jahr wird aus Pfad ermittelt
j = Mid(wbpfad, 34, 4)

'Monat wird aus Pfad ermittelt
m = Right(wbpfad, 2)

'Pfad ohne Jahr und Monat wird ermittelt
rpfad = Left(wbpfad, 33)

'Vormonat wird ermittelt
m = m - 1

'Prüfung ob Mappe mit Vorjahresdatum geöffnet werden muss
If m = 0 Then
m = 12
j = j - 1
End If

'neuer Pfad wird erstellt
npfad = rpfad & j
'falls Monat einstellig, führende Null einfügen
If m < 10 Then
npfad = npfad & "0" & m
Else
npfad = npfad & m
End If

'Verknüpfungen werden eingefügt
'Spalte R = 18, Spalte T = 20

For zeile = 6 To 91
Cells(zeile, 18).FormulaLocal = "='" & npfad & "\[Dateiname.xlsx]Tabellenblatt'!L" & zeile
Cells(zeile, 20).FormulaLocal = "='" & npfad & "\[Dateiname.xlsx]Tabellenblatt'!G" & zeile

Next zeile

End Sub

Du musst den Code in ein Standardmodul einfügen. Falls du dich mit VBA nicht gut auskennst, dann schau mal
hier nach.
Wichtig: Die Arbeitsmappe muss abgespeichert sein.
Probier das Makro erst einmal in einer Testdatei aus.

Gruß
M.O.