Hyperlink, kopierbar in andere Dateien?

Forumthread: Hyperlink, kopierbar in andere Dateien?

17.07.2017 17:53:36

Jan

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

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread

Betreff

Datum

Anwender

AW: Hyperlink, kopierbar in andere Dateien?

21.07.2017 08:46:49

fcs

AW: Hyperlink, kopierbar in andere Dateien?

21.07.2017 08:46:49

fcs

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

Infobox / Tutorial

Hyperlink in Excel kopieren und verwenden

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um einen Hyperlink in Excel zu erstellen und diesen in andere Dateien zu kopieren, folge diesen Schritten:

Hyperlink erstellen: Öffne Excel und gehe zu der Zelle, in der du den Hyperlink einfügen möchtest. Verwende die Formel:
```
=HYPERLINK("Pfad"&$C$1&".xlsx#"&$C$1&"!A"&ZEILE(), $C$1)
```
Dabei ersetzt du Pfad durch den tatsächlichen Ordnerpfad.

VBA-Makro verwenden: Um den Hyperlink in eine andere Datei zu kopieren, kannst du ein VBA-Makro schreiben. Hier ist ein Beispiel:

Sub CopyHyperlink()
   Dim rngCopy As Range
   Set rngCopy = ThisWorkbook.Sheets("Sheet1").Range("D5:D20000")
   rngCopy.Copy
   ' Füge hier den Code zum Einfügen in die Zieldatei ein
End Sub

Zieldatei auswählen: Stelle sicher, dass die Zieldatei geöffnet ist, bevor du die Hyperlinks einfügst.
Hyperlink in die Zieldatei einfügen: Verwende die .PasteSpecial Methode, um die Hyperlinks einzufügen, damit sie funktionsfähig bleiben.

Häufige Fehler und Lösungen

Hyperlink funktioniert nicht nach dem Kopieren: Stelle sicher, dass der Pfad der Datei korrekt ist und dass die Zieldatei den gleichen Verzeichniszugriff hat. Überprüfe auch, ob die Formel richtig erstellt wurde.
VBA läuft nicht: Vergewissere dich, dass Makros in deiner Excel-Anwendung aktiviert sind. Gehe zu Datei > Optionen > Trust Center > Einstellungen für das Trust Center und aktiviere die Makros.
Die Blätter können von Excel nicht in die Zielarbeitsmappe: Dies kann passieren, wenn die Zieldatei nicht geöffnet ist oder der Pfad nicht korrekt angegeben wurde. Stelle sicher, dass du die Datei öffnest, bevor du versuchst, die Daten zu kopieren.

Alternative Methoden

Excel Link zu Tabellenblatt in anderer Datei: Anstatt ein Makro zu verwenden, kannst du direkt einen Hyperlink zu einer Tabelle in einer anderen Datei erstellen, indem du die Funktion =HYPERLINK() manuell eingibst.
Link einer Excel Datei kopieren: Du kannst den gesamten Zellbereich mit dem Hyperlink markieren, kopieren und in eine andere Datei einfügen. Achte darauf, dass du die Option „Nur Werte“ wählst, falls du nur den Text ohne die Verlinkung möchtest.

Praktische Beispiele

Hyperlink auf Zelle in anderem Tabellenblatt:

=HYPERLINK("[Dateiname.xlsx]Tabellenblattname!A1", "Gehe zu Zelle A1")

Hyperlink zu einer externen Datei:

=HYPERLINK("C:\Users\DeinBenutzername\Documents\Datei.xlsx", "Öffne Datei")

Excel zelle verlinken anderes Blatt:
- Um eine Zelle in einem anderen Tabellenblatt zu verlinken, verwende:
```
=HYPERLINK("#'Tabellenblattname'!A1", "Link zu A1")
```

Tipps für Profis

Hyperlink kopieren und einfügen: Wenn du Hyperlinks zwischen verschiedenen Arbeitsmappen kopieren möchtest, stelle sicher, dass die Pfade der Dateien identisch sind, um die Funktionalität zu gewährleisten.
Verwendung von VBA zur Automatisierung: Du kannst VBA-Makros verwenden, um Hyperlinks automatisch zu generieren und in anderen Dateien zu speichern.
Ordnerpfad als Link kopieren: Wenn du häufig auf bestimmte Ordner zugreifen musst, erwäge, einen Hyperlink direkt zu diesem Ordner zu erstellen, indem du den Pfad in die HYPERLINK-Funktion eingibst.

FAQ: Häufige Fragen

1. Wie kann ich einen Hyperlink in Excel kopieren? Du kannst einen Hyperlink in Excel kopieren, indem du die Zelle mit dem Hyperlink markierst, mit der rechten Maustaste klickst und „Kopieren“ wählst. Füge ihn dann in die gewünschte Zelle ein.

2. Kann ich einen Hyperlink zu einer bestimmten Zelle in einer anderen Datei erstellen? Ja, du kannst einen Hyperlink zu einer bestimmten Zelle in einer anderen Datei erstellen, indem du die HYPERLINK-Funktion mit dem entsprechenden Dateipfad und Zellverweis verwendest.

3. Wie erstelle ich einen Hyperlink zu einem Ordnerpfad? Um einen Ordnerpfad als Link in Excel zu kopieren, verwende die HYPERLINK-Funktion mit dem Format:

=HYPERLINK("C:\Users\DeinBenutzername\Documents\", "Öffne Ordner")

Beliebteste Forumthreads (12 Monate)

Hyperlink, kopierbar in andere Dateien?

Forumthread: Hyperlink, kopierbar in andere Dateien?

1 Beitrag zum Forumthread Beitrag zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

Hyperlink in Excel kopieren und verwenden

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

1
Beitrag zum Forumthread

Beitrag zu diesem Forumthread