Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1472to1476
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

Sequenz aus Dateipfad ersetzen

Sequenz aus Dateipfad ersetzen
03.02.2016 09:05:18
Mia
Hallo liebe Excel Freunde,
ich bräuchte bitte dringend eure Hilfe! Das Problem ist folgendes: Ich habe eine Excel Datei, die mit Verknüpfungen arbeitet. Um die aktuellen Daten einzulesen arbeite ich mit der Funktion „suche und ersetze“. Ich möchte die Aktualisierung der Verknüpfung gerne mittels VBA Code automatisieren nach Abfrage des gewählten Datums in Zelle A1.Dazu habe ich versucht mit dem Makro Recorder zu arbeiten. Allerdings funktioniert es leider nicht so einfach wie ich dachte, da es nur die Datums-Sequenzen (Monat und Jahr) im Pfad sind die gesucht und ersetzt werden sollen. Meine Excel Kenntnisse sind leider nicht so gut. Ich habe mal eine Beispieldatei erstellt
https://www.herber.de/bbs/user/103247.xlsm
Vielleicht habt ihr eine Lösung?
Beste Grüße, Mia

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Sequenz aus Dateipfad ersetzen
03.02.2016 10:15:34
fcs
Hallo Mia,
hier dein Makro angepasst für das Ersetzen.
Du musst aber sicherstellen, dass die Dateien in dem neuen Jahr/Monat alle vorhanden sind bevor du das Ersetzen startest.
Ansonsten musst du mehrere 100 mal die Abbrechen-Schaltfläche anklicken bzw. die ESC-Taste drücken.
Gruß
Franz
Sub SucheErsetzeDatumJahr()
Dim vDate As Date
Dim sYear As String
Dim sMonth As String
Dim sOld As String, sNew As String
Dim sFormula As String, iPos As Integer
Dim strSep As String
strSep = "\" 'Application.PathSeparator 'Pfad-Trennzeichen in Formeln - ggf. Anpassen
vDate = Sheets(1).Cells(1, 1).Value
sYear = Format(Year(vDate), "0000")
sMonth = Format(Month(vDate), "00")
'alte Formel
sFormula = Range("B5").FormulaR1C1
'neuer Formelteil
sNew = strSep & sYear & strSep & sMonth & strSep & "[" & sYear & sMonth
'Position des "[" in der alten Formel
iPos = InStr(1, sFormula, "[")
'alter Formelteil
sOld = Mid(sFormula, iPos - 9, 16)
'Formelteil ersetzen
ActiveSheet.Cells.Replace What:=sOld, Replacement:=sNew, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
False, ReplaceFormat:=False
End Sub

Anzeige
AW: Sequenz aus Dateipfad ersetzen
03.02.2016 10:32:00
Luschi
Hallo Franz,
wenn man in Deiner Variante noch das Application.DisplayAlerts = False bzw. True einbaut,
klappt diese Möglichkeit ebenfalls sehr gut.
Das hift besonders dann, wenn es den Ersetzngspfad oder die Datei nicht gibt;
sonst muß man 155 mal die Hinweismeldung wegklicken.
Gruß von Luschi
aus klein-Paris

AW: Sequenz aus Dateipfad ersetzen
03.02.2016 11:20:55
fcs
Hallo Luschi,
danke für den Hinweis.
Ich war mir nicht sicher, ob man die Abfrage bei fehlender Datei tatsächlich unterdrücken kann.
Und die Zeit zum Probieren hatte ich jetzt auch nicht.
Mir reichte es als ich beim Testen dann die ESC-Tasste zum glühen geracht hab.
Gruß
Franz

Anzeige
AW: Sequenz aus Dateipfad ersetzen
03.02.2016 10:16:21
Luschi
Hallo Mia,
bezogen auf Dein Beispiel klappt das bei mir so:

Sub machMal()
Dim wb As Workbook, ws As Worksheet
Dim rg1 As Range, rg2 As Range
Dim v1 As Variant, v2 As Variant
Dim newFormula As String
Dim d1 As Date
'aktuelle Arbeitsmappe
Set wb = ThisWorkbook
'Arbeitstabelle
Set ws = Tabelle1
'ider
Set ws = wb.Worksheets("Sheet1")
'Datum in Zelld 'Sheet1!A1'
d1 = ws.Range("A1").Value
'Excel-Geschwindigkeitsbrensen lösen
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.Cursor = xlWait
'Zellbereich mit den Formeln definieren
Set rg1 = ws.Range("B5:AF9")
'jede Zelle durchlaufen
For Each rg2 In rg1
'Formel der Zelle in 1 Array schreiben
v1 = Split(rg2.Formula, "\", -1, 1)
v2 = Split(v1(5), ".", -1, vbTextCompare)
v1(3) = Year(d1)
v1(4) = Month(d1)
v2(0) = v1(3) & v1(4) & Format(rg2.Column - 1, "00")
'neue Formel as Textkette (String)
newFormula = v1(0) & "\" & v1(1) & "\" & v1(2) & "\" & v1(3) & "\" & _
v1(4) & "\[" & v2(0) & "." & v2(1)
'neue Formel in Zelle schreiben
rg2.Formula = newFormula
Next rg2
'Excel-Standardeinstellungen wieder herstellen
Application.DisplayAlerts = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = False
Application.Cursor = xlWait
Application.ScreenUpdating = False
'Objekvariablen zerstören
Set rg1 = Nothing
Set rg2 = Nothing
Set ws = Nothing
Set wb = Nothing
'Arrays löschen
Erase v1: Erase v2
MsgBox "F e r t i g!", vbSystemModal + 48, "Hinweis..."
End Sub
Gruß von Luschi
aus klein-Paris

Anzeige
AW: Sequenz aus Dateipfad ersetzen
03.02.2016 12:10:31
Mia
Hallo Luschi,
vielen Dank. In der Test Datei klappt es wunderbar.
Leider hat das Makro in der Real Datei ein Problem: Laufzeitfehler '9': Index außerhalb des gültigen Bereichs.
Ich hatte eine kleine Anpassung für "'Zellbereich mit den Formel" vorgenommen.
Aber Franz Version läuft auch in der Real Datei.
Vielen herzlichen Dank an euch für die schnelle und kompetente Hilfe!
Viele Grüße
Mia

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige