Hyperlink, kopierbar in andere Dateien?

Hallo zusammen,
ich benötige bitte eure Hilfe.
Ziel: Einen Hyperlink in einer Excel Datei erstellen, welcher auch in andere Dateien kopiert werden kann.
Ist es möglich, den u. a. hyperlink in VBA umzusetzen, sodass dieser in andere Dateien kopiert werden kann? Dieser soll dann eine bestimmte Datei öffnen und in eine vorgegebene Zelle springen. In Excel direkt klappt es, nur kopieren kann ich ihn nicht. In VBA bekomme ich es nicht hin :-(
Hintergrund: Der Benutzer soll einen Datenblock (D5 bis L20000) aus einer Vorlage kopieren (in welcher der hyperlink erzeugt wurde) und in seine Datei reinkopieren und dort soll der hyperlink auch funktionieren. Der Benutzer soll keine Hilsspalten für den hyperlin mitkopieren.
Formel in Excel: =HYPERLINK("Pfad"&$C$1&".xlsx#"&$C$1&"!a"&ZEILE(),$C$1)
Pfad: Ist immer fest
Dateiname: Steht in Zelle C1
Tabellenblattname: Steht in Zelle C1 (identisch mit dem Dateinamen)
!a"Zeile(): Ermittelt die Zeile in welche in der anderen Datei gesprungen werden soll. Steht die Formel in der Zeile 10, soll auch in der anderen Datei in die Zeile 10 gesprungen werden.
Zellenwert: In jeder Zelle soll der Wert aus Zelle C1 als Name stehen mit individuellen hyperlink-Zeilenziel im Hintergrund
Formelbereich: I5 bis I20000
Es ist nicht nötig, dass geprüft wird, ob irgendeine Spalte/Zelle einen Wert beinhaltet. Er soll stur bis Zeile 20000 den hyperlink erstellen. Überflüssige Zeilen lösche ich später.
Ich habe auch schon versucht, den kompletten Pfad als Formel in die Zelle zu schreiben, diesen runterkopiert, durch Werte ersetzt und dann mit ActiveSheet.Hyperlinks.Add etc. durch einen hyperlink zu ersetzen. Dieses hatte auch
geklappt, doch entweder hatte ich dann den kompletten Pfad dort stehen (was ich nicht möchte) oder nur in der ersten Zelle den richtigen Namen (inkl. funktionierenden hyperlink) und alle anderen Zellen dadrunter waren leer
Nach mehreren fehlgeschlagenen Versuchen hab ich den ganzen VBA code (bzw. das entstandene Chaos) wieder gelöscht (vor Wut) und bin erst jetzt darauf gekommen hier mal zu fragen.
Ich hoffe ihr versteht, was ich gerne möchte und meine.
Gruß
Jan

Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Jan,
wenn in der Zieldatei die Info zum Namen der Exceldatei im Hyperlink nicht in Zelle C1 steht,
dann muss die Hyperlink-Formel nach dem Kopieren per Makro mit festem Formel-Text neu generiert werden.
Nachfolgend 2 Makro-Varienten die man Einsetzen kann, abhängig davon, wann das Makro gestartet wird bzw. wo es gespeichert ist.
Gruß
Franz

Sub CopyData_von_Vorlage_to_Zielmappe()
' GetData_von_Vorlage Makro
'   Makro ist in der VorlageDatei gespeichert und wird von dieser aus aus gestartet, _
die Ziel-Arbeitsmappe wird während der Makroausführung gewählt
Dim strDateiVorlage As String
Dim rngZelleEinfuegen As Range
Dim strLinkPfad As String, strLinkDatei As String
Dim rngCopy As Range
Dim wkbAktiv As Workbook, wksEinfuegen As Worksheet
Dim wkbVorlage As Workbook, wksVorlage As Worksheet
strLinkPfad = "C:\Users\Admin\Documents\"
Set wkbVorlage = ThisWorkbook
'Vorlage-Blatt setzen
Set wksVorlage = wkbVorlage.Worksheets(1) 'or Set wksVorlage = wkbVorlage.Worksheets("Tab  _
XYZ")
'Name der Linkdatei einlesen
strLinkDatei = wksVorlage.Range("C1").Text
For Each wkbAktiv In Application.Workbooks
If Application.Windows(wkbAktiv.Name).Visible = True Then
Select Case LCase(wkbAktiv.Name)
Case LCase(wkbVorlage.Name), "personal.xlsb"
'do nothing - die Mappen nicht zur Auswahl anbieten
Case Else
wkbAktiv.Activate
Select Case MsgBox("in dieser Arbeitsmappe einfügen?", _
vbQuestion + vbYesNoCancel, _
"Ziel-Arbeitsmappe auswählen")
Case vbNo
'nächste Mappe anzeigen
Case vbYes
'Tabellenblatt in Zieldatei setzen
Set wksEinfuegen = wkbAktiv.Worksheets(1)
'oder Blatt vorgeben
'                    Set wksEinfuegen = ActiveSheet
'Einfügezelle setzen - entweder variabel oder bestimmte Zelle fest vorgeben
Set rngZelleEinfuegen = wksEinfuegen.Range("D5")
' oder variabel
'Set rngZelleEinfuegen = ActiveCell
'zu kopierenden Zellbereich in Vorlage variabel setzen entsprechend dem _
benutzen Zellbereich
With wksVorlage
Set rngCopy = .Range("D5:L" & .UsedRange.Row + .UsedRange.Rows.Count -  _
1)
End With
rngCopy.Copy Destination:=rngZelleEinfuegen
Application.CutCopyMode = False
'Formeln für Hyperlinks 5 Spalten rechts von der Einfügezelle einfügen
rngZelleEinfuegen.Offset(0, 5).Resize(rngCopy.Rows.Count, 1).FormulaR1C1 =  _
_
"=HYPERLINK(""" & strLinkPfad & strLinkDatei & ".xlsx#" & strLinkDatei  _
_
& "!A"" & ROW(),""" & strLinkDatei & """)"
Set rngCopy = Nothing
Exit Sub
Case vbCancel
wkbVorlage.Activate
Exit Sub
End Select
End Select
End If
Next
MsgBox "Es wurde keine Arbeitsmappe gewählt!" & vbLf _
& "Makro wird abgebrochen", vbInformation + vbOKOnly, "Ziel-Arbeitsmappe auswählen"
wkbVorlage.Activate
End Sub
Sub GetData_von_Vorlage()
' GetData_von_Vorlage Makro
'   Makro wird gestartet wenn die Datei in der eingefügt werden soll die aktive Arbeitsmappe  _
ist.
' Dies Vorlagedatei wird dann kurzzetg schreibgeschützt geöffnet
Dim strDateiVorlage As String
Dim rngZelleEinfuegen As Range
Dim strLinkPfad As String, strLinkDatei As String
Dim rngCopy As Range
Dim wkbAktiv As Workbook, wksEinfuegen As Worksheet
Dim wkbVorlage As Workbook, wksVorlage As Worksheet
strDateiVorlage = "C:\Users\Public\NeuTest\JanVorlage.xlsx"
strLinkPfad = "C:\Users\Admin\Documents\"
'Aktive Datei und Tabellenblatt in Variablen merken
Set wkbAktiv = ActiveWorkbook
Set wksEinfuegen = ActiveSheet
'Einfügezelle setzen - entweder variable oder bestimmte Zelle fest vorgeben
Set rngZelleEinfuegen = wksEinfuegen.Range("D5")
' oder variable Set rngZelleEinfuegen = ActiveCell
'Vorlage-Datei schreibgeschützt öffnen
Set wkbVorlage = Application.Workbooks.Open(Filename:=strDateiVorlage, ReadOnly:=True)
'Vorlage-Blatt setzen
Set wksVorlage = wkbVorlage.Worksheets(1) 'or Set wksVorlage = wkbVorlage.Worksheets("Tab  _
XYZ")
'Name der Linkdatei einlesen
strLinkDatei = wksVorlage.Range("C1").Text
'zu kopierenden Zellbereich variabel setzen entsprechend dem benutzen Zellbereich
With wksVorlage
Set rngCopy = .Range("D5:L" & .UsedRange.Row + .UsedRange.Rows.Count - 1)
End With
rngCopy.Copy Destination:=rngZelleEinfuegen
Application.CutCopyMode = False
'Formeln für Hyperlinks 5 Spalten rechts von der Einfügezelle einfügen
rngZelleEinfuegen.Offset(0, 5).Resize(rngCopy.Rows.Count, 1).FormulaR1C1 = _
"=HYPERLINK(""" & strLinkPfad & strLinkDatei & ".xlsx#" & strLinkDatei _
& "!A"" & ROW(),""" & strLinkDatei & """)"
Set rngCopy = Nothing
wkbVorlage.Close savechanges:=False
wkbAktiv.Activate
End Sub

Beliebteste Forumthreads (12 Monate)

Hyperlink, kopierbar in andere Dateien?

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

301 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Hyperlink, kopierbar in andere Dateien?

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

301 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread