Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1572to1576
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Verknüpfungen aktualisieren über ein Makro

Verknüpfungen aktualisieren über ein Makro
10.08.2017 10:18:20
Makroschnacker
Guten Morgen liebes Forum,
ich möchte in einer Exceldatei die Verknüpfungen automatisch über ein Makro aktualisieren. Den Code, wie das allgemein funktionieren sollte, habe ich bereits:
ActiveWorkbook.ChangeLink Name:= _
"\\...\Monatsreport\2017\0617\blabla" _
, NewName:= _
"\\...\Monatsreport\2017\0717\blabla" _
, Type:=xlExcelLinks
Bei den Dateien handelt es sich um Monatsberichte. Im Ordnersystem wird dabei die Datei des Vormonats in den aktuellen Monatsordner kopiert und anschließend aktualisiert. Die Verknüpfungen müssen also von den Dateien des Vormonats auf die aktuellen Dateien abgeändert werden. Das Problem ist nun, dass ich dafür Variablen in die Dateinamen einfügen muss, was bisher leider nur zu Fehlermeldungen führt. Ich ziehe hierfür Datumswerte aus Zellen der Exceldatei und füge diese wie folgt mit Variablen in den Code ein:
Set datum = ThisWorkbook.Sheets("Tabelle1").Range("A3")
Set datumvormonat = ThisWorkbook.Sheets("Tabelle1").Range("A4")
jahr = Right$(datum, 4)
jahrvormonat = Right$(datumvormonat, 4)
jahrmitmonat = Right$(datum, 7)
jahrmitmonatvormonat = Right$(datumvormonat, 7)
monat = Left$(jahrmitmonat, 2)
vormonat = Left$(jahrmitmonat, 2)
jahrkurz = Right$(datum, 2)
jahrkurzvormonat = Right$(datumvormonat, 2)
Tag = Left$(datum, 2)
tagvormonat = Left$(datumvormonat, 2)
pfadvormonat = jahrvormonat & "\" & vormonat & jahrkurzvormonat & "\Einlagen\Monatsreport\"
dateinamevormonat = pfadvormonat1 & jahrvormonat & "-" & vormonat & "-" & tagvormonat & " Zinsen" & ".xlsm"
pfad = jahr & "\" & monat & jahrkurz & "\Einlagen\Monatsreport\"
dateiname = pfad1 & jahr & "-" & monat & "-" & Tag & " Zinsen" & ".xlsm"
ActiveWorkbook.ChangeLink Name:= _
"\\...\Monatsreport\" & dateinamevormonat _
, NewName:= _
"\\...\Monatsreport\" & dateiname _
, Type:=xlExcelLinks
Kann mir hier im Forum jemand weiterhelfen, wo ich meinen Denkfehler habe? Würde mich sehr über eine Antwort freuen. Vielen Dank!
Gruß Makroschnacker

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Verknüpfungen aktualisieren über ein Makro
11.08.2017 11:10:43
ChrisL
Hi
Sub t()
Dim Datum As Date, DatumVormonat As Date
Dim Pfad As String, PfadVormonat As String
Dim fix1 As String, fix2 As String
' Dummy Daten
Datum = CDate("1.1.17")
DatumVormonat = CDate("1.12.16")
fix1 = "\\...\Monatsreport\"
fix2 = "\Einlagen\Monatsreport\"
Pfad = fix1 & Year(Datum) & "\" & Format(Datum, "MMYY") & fix2
PfadVormonat = fix1 & Year(DatumVormonat) & "\" & Format(DatumVormonat, "MMYY") & fix2
MsgBox "Ersatz: " & Chr(10) & PfadVormonat & Chr(10) & "durch:" & Chr(10) & Pfad
End Sub

cu
Chris
Anzeige
AW: Verknüpfungen aktualisieren über ein Makro
11.08.2017 11:56:10
fcs
Hallo,
in deinem Makro berechnest du die Variable "vormonat" falsch, denn du rechnest mit "jahrmitmonat".
In den Dateianmen sollte kein Pfad eingerechnet werden.
Unten im Makro "Test" eingebaut.
Probleme dürften beim Jahreswechsel Dezember auf Januar entstehen, denn hier müssen ja große Teile des Pfades angepasst werden.
Bei mir wird bei den Verknüpften links -auch beim Makrorekorder- immer der komplette Pfad angezeigt. nie der relative Pfad mit \\...\usw.
Ich hab mal eine Variante gebaut, die die datums-abhängigen Teile der Verzeichnisse von rechts nach links austauscht. Makro "Test_kurz"
Gruß
Franz
Sub Test()
'Deklaration Variabalen
Dim datum As Range, datumvormonat As Range
Dim jahr, jahrvormonat, jahrmitmonat, jahrmitmonatvormonat, monat, vormonat
Dim jahrkurz, jahrkurzvormonat, Tag, tagvormonat
Dim pfadvormonat, dateinamevormonat, pfad, dateiname
Set datum = ThisWorkbook.Sheets("Tabelle1").Range("A3")
Set datumvormonat = ThisWorkbook.Sheets("Tabelle1").Range("A4")
jahr = Right$(datum, 4)
jahrvormonat = Right$(datumvormonat, 4)
jahrmitmonat = Right$(datum, 7)
jahrmitmonatvormonat = Right$(datumvormonat, 7)
monat = Left$(jahrmitmonat, 2)
vormonat = Left$(jahrmitmonatvormonat, 2)       'Korrektur
jahrkurz = Right$(datum, 2)
jahrkurzvormonat = Right$(datumvormonat, 2)
Tag = Left$(datum, 2)
tagvormonat = Left$(datumvormonat, 2)
pfadvormonat = jahrvormonat & "\" & vormonat & jahrkurzvormonat & "\Einlagen\Monatsreport\"
dateinamevormonat = jahrvormonat & "-" & vormonat & "-" & _
tagvormonat & " Zinsen" & ".xlsm"            'Korrektur
pfad = jahr & "\" & monat & jahrkurz & "\Einlagen\Monatsreport\"
dateiname = jahr & "-" & monat & "-" & Tag & " Zinsen" & ".xlsm"    'Korrektur
ActiveWorkbook.ChangeLink Name:="\\...\Monatsreport\" & dateinamevormonat, _
NewName:="\\...\Monatsreport\" & dateiname, _
Type:=xlExcelLinks
End Sub
Sub Test_Kurz()
'Deklaration Variabalen
Dim datum As Range, datumvormonat As Range
Dim arrSource, varSource
Dim linkOld, linkNeu, arrNeu, intNeu%, intDatei%
Dim sSep As String
sSep = Application.PathSeparator
Set datum = ThisWorkbook.Sheets("Tabelle1").Range("A3")
Set datumvormonat = ThisWorkbook.Sheets("Tabelle1").Range("A4")
arrSource = ActiveWorkbook.LinkSources(Type:=xlExcelLinks)
For Each varSource In arrSource
linkOld = varSource
'Link am Verzeichnistrennzeichen zerlegen
arrNeu = Split(linkOld, sSep)
intDatei = UBound(arrNeu)
If LCase(arrNeu(intDatei)) = LCase(Format(datumvormonat, "YYYY-MM-DD") & " Zinsen.xlsm")  _
Then
'Jahr-, Monats- und Datumswerte im Pfad/Dateinamen ersetzen
For intNeu = intDatei To LBound(arrNeu) Step -1
If arrNeu(intNeu) = "..." Then Exit For
Select Case intNeu
Case intDatei 'Dateiname
arrNeu(intNeu) = Format(datum, "YYYY-MM-DD") & Mid(arrNeu(intNeu), 11)
Case intDatei - 1 'Monatsreport
'Verzeichnis unverändert übernehmen
Case intDatei - 2 'Einlagen
'Verzeichnis unverändert übernehmen
Case intDatei - 3 'MMJJ - Monat + Jahr kurz
arrNeu(intNeu) = Format(datum, "MMYY")
Case intDatei - 4 'JJJJ - Jahr
arrNeu(intNeu) = Format(datum, "YYYY")
Case Else
'Verzeichnisse unverändert übernehmen
End Select
Next intNeu
'neuen Link zusammensetzen
linkNeu = arrNeu(LBound(arrNeu))
For intNeu = LBound(arrNeu) + 1 To intDatei
linkNeu = linkNeu & sSep & arrNeu(intNeu)
Next intNeu
ActiveWorkbook.ChangeLink Name:=linkOld, NewName:=linkNeu, Type:=xlExcelLinks
End If
Next varSource
End Sub

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige