Makro Suchen Ersetzen

Computer: Software & Programmierung Office Software
#1

Hallo,
ich habe ein Makro durch Aufzeichnung erstellt, da ich mich in der VBA Programmierung nicht so auskenne.
So sieht es aus:
Sub Makro6() ’ ’
Makro6 Makro ’ ’
Sheets(Array(„Legende“, „1981“, „1980“, „1979“, „1978“, „1977“, „1976“, „1975“, „1974“, _ „1973“, „1972“, „1971“, „1970“, „1969“, „1968“, „1967“, „1966“, „1965“, „1964“, „1963“, _ „1962“, „1961“, „1960“, „1959“, „1958“)).Select
Sheets(„1981“).Activate
Sheets(Array(„1957“, „1956“, „1955“, „1954“, „1953“, „1952“, „1951“, „1950“, „1949“, _ „1948“, „1947“, „1946“, „1945“, „1944“, „1943“, „1942“, „1941“, „1940“, „1939“, „1938“, _ „1937“, „1936“, „1935“, „1934“)).Select Replace:=False
Columns(„F:F“).Select
Selection.Replace What:=„g:“, Replacement:=„h:“, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False
Sheets(„Legende“).Select
Range(„B12“).Select End Sub
Es ersetzt in den angegebenen Tabellen den laufwerksbuchstaben der Hyperlinks in Spalte F:.
Die Laufwerksbuchstaben sind hier vorgegeben (g: und h:smile:.

Ich möchte nun erreichen, dass das Makro die Laufwerksbuchstaben aus den Zellen b12 btw. b13 des Tabellenblattes Legende ausliest und das Ersetzen erfolgt.

Kann man das machen?

#2

Hallo Tommy,

versuch’s mal so:

Option Explicit

Sub Makro6()
 Sheets(Array("1981", "1980", "1979", "1978", "1977", "1976", "1975", "1974", \_
 "1973", "1972", "1971", "1970", "1969", "1968", "1967", "1966", "1965", "1964", "1963", \_
 "1962", "1961", "1960", "1959", "1958")).Select
 Columns("F:F").Select
 Selection.Replace What:=Sheets("Legende").Range("B12").Value, Replacement:=Sheets("Legende").Range("B13"), LookAt:=xlPart, \_
 SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, \_
 ReplaceFormat:=False
End Sub

Gruß, Andreas

#3

Nachgeragt: Makro Suchen Ersetzen

Ich möchte nun erreichen, dass das Makro die Laufwerksbuchstaben aus den Zellen b12 btw. b13 des Tabellenblattes Legende ausliest und das Ersetzen erfolgt.

Ich stelle mir gerade die Frage, warum du in deinen Hyperlinks feste Laufwerkbuchstaben einträgst wenn du sie später anpassen möchtest.
Es ist durchaus möglich der Funktion =hyperlink() den Laufwerkbuchstaben aus b12 / B13 zuzuweisen so das sich die Links anpassen sobald der Inhalt in b12 / B13 sich ändert.

Hinweise für dynamische Hyperlinks gibt es reichlich
Wenn du uns hier deine Formel zeigst, lässt sich die recht einfach anpassen.

Gruß Holger

#4

Hallo Andreas,
vielen lieben Dank, es klappt und ich habe wieder was dazugelernt.

Kann man jetzt noch erreichen, dass in jedem Arbeitsblatt die Zelle a1 aktiv ist. Die soll sein, damit die Markierung der Spalte F verschwindet und man die Tabelle ordentlich sieht.

Dankeeeeeeeeeeeeee nochmal!!!
Tommy

#5

Hallo Holger,
das Makro zum Erzeugen der Hyperlinks sieht so aus:
Sub Link()
Dim objFolder As Object, strName As String
Set objFolder = CreateObject(„shell.application“).BrowseForFolder(0, „Bitte gib den Pfad an, in dem sich die Bilddateien befinden.“, 1)
If Not objFolder Is Nothing Then
With ActiveSheet
strName = objFolder.Self.Path & „“ & objFolder.Self.Name & „_“
.Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 5).Formula = _
„=HYPERLINK(“"" & strName & „“"&TEXT(ROW()-1,"„000"“)&"".jpg"",""" & objFolder.Self.Name & „_“"&TEXT(ROW()-1,"„000"“))"
strName = Mid(strName, 3)
End With
With ActiveSheet
.Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 5).HorizontalAlignment = xlCenter
End With
End If
End Sub

Wenn ich die Excel Datei nun auf ein anderes Laufwerk kopiere, oder der USB Stick einen abweichenden Laufwerksbuchstaben hat, funtionieren die Hyperlinks nicht mehr, deshalb will ich es über das zusätzliche (vielleicht nicht erforderliche?) Makro anpassen.

Wenn du eine bessere Lösung hast, dann Danke im Voraus.

LG, Tommy.

#6

Hi Tommy,

ungetestet: Schreib mal vor dem End Sub noch ein Range(„A1“).Select

Gruß, Andreas

#7

Hallo Andreas,
Range(„A1“).Select funktioniert. Er springt auf diese Zelle.
Habe meine Frage falsch formuliert.
Es soll die Zelle A1 aktiviert werden, was ja klappt.
Kann man zur besseren Übersicht diese Zelle auch anzeigen lassen?

Übrigens ist es toll, so lernen zu dürfen. Bring mir erheblich mehr, als ewig die Befehle zu suchen.
Ich notiere mir alles und wende es in anderen Anwendungen an. Danke für eure Geduld.

#8

was meinst du denn mit „anzeigen“? In einer MsgBox, oder wie stellst du dir das vor?

Gruß, Andreas

#9

Hallo Andreas, ich meine, dass man die Spalte A in der Tabelle sehen kann. Durch den Range Befehl ist A1 zwar markiert, aber nicht immer sichtbar, manchmal steht am liken Rand Spalte E, oder D.
Gruß Tommy

#10

Hallo Andreas,
ich habe doch weiter oben mein Makro zur Erstellung der Hyperlinks geschrieben.
„=HYPERLINK(“"" & strName & „“"&TEXT(ROW()-1,"„000"“)&"".jpg"",""" & objFolder.Self.Name & „_“"&TEXT(ROW()-1,"„000"“))"
hier wird er ja gebildet.

Gibt es eine Möglichkeit, ihn relativ/dynamisch zu machen?
Dann würde die ganze Sache mit den Laufwerken wegfallen, obwohl es wahnsinnig Lerneffekt hat.

Tommy.

#11

Hi Tommy,

dann mach aus dem

Range("A1").Select

ein

Range("A1").Activate

.

Gruß, Andreas

#12

'morgen Tommy,

dazu müsstest du mal verraten, wie die Variablen strName und objFolder zustande kommen. Zeig mal den kompletten Makro, der die Hyperlinks erzeugt.

Gruß, Andreas

#13

Sub Link()
Dim objFolder As Object, strName As String
Set objFolder = CreateObject(„shell.application“).BrowseForFolder(0, „Bitte gib den Pfad an, in dem sich die Bilddateien befinden.“, 1)
If Not objFolder Is Nothing Then
With ActiveSheet
strName = objFolder.Self.Path & „“ & objFolder.Self.Name & „_“
.Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 5).Formula = _
„=HYPERLINK(“"" & strName & „“"&TEXT(ROW()-1,"„000"“)&"".jpg"",""" & objFolder.Self.Name & „_“"&TEXT(ROW()-1,"„000"“))"
strName = Mid(strName, 3)
End With
With ActiveSheet
.Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 5).HorizontalAlignment = xlCenter
End With
End If
End Sub
So sieht er aus.
Funktioniert auch einwandfrei.

#14

Hallo Tommy,
also. ganz automatisch oder dynamisch wird das nicht gehen. Excel kann schließlich nicht wissen, welchen Laufwerkbuschtaben der Stick hat.
Aber: Wenn du die Hyperlinks mit dem folgenden Code erzeugst, dann können anderer Benutzer den Laufwerkbuchstaben (ein Großbuchstabe, ohne folgenden Doppelpunkt) in Zelle G1 eintragen. Die Hyperlinks greifen auf diese Zelle zu.

Sub Link()
 Dim objFolder As Object, strName As String
 Set objFolder = CreateObject("shell.application").BrowseForFolder(0, "Bitte gib den Pfad an, in dem sich die Bilddateien befinden.", 1)
 If Not objFolder Is Nothing Then
 With ActiveSheet
 strName = objFolder.Self.Path & "\" & objFolder.Self.Name & "\_"
 .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 5).Formula = \_
 "=HYPERLINK($G$1&""" & Mid(strName, 2) & """&TEXT(ROW()-1,""000"")&"".jpg"",""" & objFolder.Self.Name & "\_""&TEXT(ROW()-1,""000""))"
 .Range(.Cells(2, 1), .Cells(.Rows.Count, 1).End(xlUp)).Offset(, 5).HorizontalAlignment = xlCenter
 End With
 End If
End Sub

Gruß, Andreas

#15

Hallo Andreas,
das klappt ja wunderbar.

Den benutzer werde ich das makro nicht ausführen lassen, es diente für mich, um die Links in der tabelle (ca. 30 Arbeitsblätter erzeugen zu können.
Habe eine kleine Änderung reingebracht:
"=HYPERLINK(Legende!$B$13&…
Dadurch braucht der User nur an einer Stelle den Laufwerksbuchstaben eingeben.

Werde jetzt noch versuchen, wie es mit Schreibschutz der Zellen in den Datentabellen funktioniert und noch ein bisschen an deinem ersten Makro spielen, um es hinzubekommen, dass in allen Tabellen der Cursor auf A1 steht (was ja schon klappt) und A1 auch zu sehen ist.

Vielen lieben Dank,

Tommy

#16

Da fällt mir noch ein, warum ich auf das automatische Erkennen des Laufwerks kam.

Wenn ich die Hyperlinks manuell einfüge, sind sie unabhängig vom laufwerk. Aus diesem Grund dachte ich, dass man per VBA solch einen Link erzeugen kann.

Mit deinem makro und der Eingabe des Laufwerks ist es aber auch wunderbar.

Danke nochmal.

Tommy.

#17

Hi Tommy,

, um es hinzubekommen, dass in
allen Tabellen der Cursor auf A1 steht (was ja schon klappt)
und A1 auch zu sehen ist.

Schau in mein Posting von 7:17 Uhr.

Vielen lieben Dank,

Tommy

Gruß, Andreas

#18

Wenn ich die Hyperlinks manuell einfüge, sind sie unabhängig
vom laufwerk.:

Hmm, bei mir nicht. Das muss ich heute Abend noch mal probieren.

Tommy.

Andreas

#19

Hi Tommy,

also ich schaff das nicht, einen Hyperlink zu erzeugen, der nicht auf ein festgelegtes Laufwerk verweist. Kannst du noch mal genauer erklären, wie du das gemacht hast.

Danke und Gruß,
Andreas

#20

Wenn ich in Excel einen Hyperlink „von Hand“ einfüge, steht in der Zelle "file///C:…
Wenn ich nun die Exceldatei und die anzuzeigende auf ein anderes Laufwerk speichere, ändert sich C: automatisch in den aktuellen Laufwerksbuchstaben.

Nur bei so vielen Daten, die ich hatte, war mir das zu mühsehlig.

Übrigens, dein Makro ist die reinste Sahne, klappt alles hervorragend.

Danke nochmal.

Wie und wo kann man am schnellsten VBA lernen?

Tommy.