Aus Excel Tabelle Ordner mit Datei erstellen - inkl. Hyperlink und Verknüpfung

Question

Hallo zusammen,

ich versuche mir gerade eine Art CRM Programm mit Excel zu bauen.

In der ersten Excel-Tabelle habe ich eine Übersicht mit laufender Nummer, Name, Plz und Ort und einer zusätzlichen Spalte mit Wiedervorlage. Wird in eine neue Zeile ein Name eingetragen erstellt ein VBA einen neuen Ordner. Dieser erhält den Namen der lf.Nr. mit „Aktivitäten“ und „Schriftverkehr“.

In den Ordner „Aktivitäten“„ wird eine Excel-Tabelle namens „Akt.xlsm“ eingefügt. In diese sollen alle Details eingetragen werden. U.a. auch ein Wiedervorlagedatum. In der Zelle I11 ist eine Formel hinterlegt, die das nächste Datum aus anzeigt. Die Excel-Datei ist im Übergeordneten Ordner „Inhalt“ als Vorlage hinterlegt.

Nun meine Fragen:

1. Wie bekomme ich den Inhalt der Zelle I11 aus der Excel-Tabelle namens „„Akt.xlsm“„ aus dem passend erstellen Ordner in die passende Zeile (Spalte X) der Übersicht-Tabelle.

2. Wie bekomme ich einen Hyperlink aus der Excel-Tabelle „„Akt.xlsm“„ zum passend erstellten Ordner Schriftverkehr.

Ich hoffe, das war nicht zu verwirrend erklärt.

Den Ordneraufbau habe ich folgend aufgebaut:

->Ordner CRM
-> Ordner Inhalt (enthält Vorlage Excel-Datei „Akt.xlsm“)
-> Ordner Kunden (hier wird je Name ein Ordner erstellt (Ordnerbezeichnung = lfd. Nr.)
->Ordner „Aktivitäten“ - beinhaltet Datei „Akt.xlsm“
->Ordner „Schriftverkehr“„ - dort sollen Schriftstücke gespeichert werden



Vielen Dank schon mal für eure Hilfe

Gruß
Florian

Answer 1

Hier noch die Testdateien:

Übersichtsdatei


Akt.xlsm

Answer 2

Hallo,

hat wirklich keiner einen Lösungsansatz?

VG Florian

Answer 3

Hallo Florian,

zu deiner Frage 1: Wie hast du dir das vorgestellt? Falls das Datum automatisch in die Übersichtsdatei geschrieben werden soll, müsste man wissen, in welchem Verzeichnis diese liegt.
Außerdem würde ich in der Mappe "Akt" noch die laufende Nummer zu dem Namen hinterlegen. Damit wird auch die Zuordnung des Wiedervorlagedatums zum Namen einfacher.

zu deiner Frage 2:
Man könnte z.B. beim Öffnen der Datei Akt prüfen, ob in einer Zelle ein Hyperlink vorhanden ist. Das könnte z.B. so aussehen:

Private Sub Workbook_Open()

Dim pfadneu As String

'Achtung! Zelle für Hyperlink anpassen!
'Prüfen in Zelle E1 bereits etwas geschrieben ist und falls ja, Programm verlassen
If Worksheets(1).Range("E1") <> "" Then Exit Sub

'Pfad zum Verzeichnis Schriftverkehr erstellen
pfadneu = ThisWorkbook.Path & "\Schriftverkehr\"

'Prüfen ob neuer Pfad existiert
If Dir(pfadneu, vbDirectory) <> "" Then

'falls ja, Hyperlink schreiben
With Worksheets(1)
.Hyperlinks.Add Anchor:=.Range("E1"), _
Address:=pfadneu, _
TextToDisplay:="Schriftverkehr"
End With

Else

'Falls nein, Fehlermeldung
MsgBox "Der Pfad " & pfadneu & " existiert nicht", 48, "Fehler"

End If

End Sub

Ich hoffe, ich habe deine Frage und den Verzeichnisaufbau richtig verstanden. Das Makro gehört in das VBA-Projekt der Arbeitsmappe. Die Bezüge musst du noch anpassen.

Gruß

M.O.

Answer 4

Hallo M.O.,

vielen Dank für Deinen Lösungsansatz.

Zu Frage 1:

1. Wie bekomme ich den Inhalt der Zelle I11 aus der Excel-Tabelle namens „„Akt.xlsm“„ aus dem passend erstellen Ordner in die passende Zeile (Spalte X) der Übersicht-Tabelle.

Ich versuchs gerade (vermutlich ziemlich umständlich) so zu lösen:

In Hilfsspalten lege ich mir die Teile der Ordner-Adresse zurecht

Zelle S1 '=
Zelle T1 '\\S-file01\ds\CRM\ (ist immer gleich)

in der nächsten Spalte den letzten Teil des der Adresse
Zelle U1 \Aktivitäten\[Akt.xlsm]Tabelle1'!$F$1 (ist immer gleich)

In Zelle A3 steht die laufende Nummer, die ja gleichzeitig auch der Orndername ist. (ist die einzige variable in der Adresse)

Nun verknüpfe ich die Zellen mit &""& =S1&""&T1&""&A3&""&U1

Ergebnis: = '\\S-file01\ds\CRM\5\Aktivitäten\[Akt.xlsm]Tabelle1'!$F$1

Nun habe ich optisch zumindest schon mal den "Link"

Wenn ich nun die Formel in eine andere Zelle als Wert einfüge, nach dem letzten Zeichen den Cursor setzte und bestätige, erscheint tatsächlich die richtige Verknüpfung. Nun muss ich es eben manuell machen. Kann ich dieses umwandeln in einen Wert automatisieren, so dass daraus tatsächlich die richtige Zell-Verknüpfung erscheint?


Zu Frage 2: Hyperlink zum Ordner

Grundgedanke war, alle Schreiben, Notizen etc. in den Ordner "Schriftverkehr" zu speichern und diese per Hyperlink in die Tabelle Akt.xlsm einzufügen.

Für den Hyperlink könnte ich doch den Lösungsansatz aus Frage 1 dann verwenden, oder?

VG Florian

Answer 5

Hallo Florian,

du hast ja in deiner Übersichtsdatei ein Makro, mit dem du die Unterverzeichnisse anlegst und die Tabelle Akt.xlsm in das Unterverzeichnis kopierst.
Dort fügst ja auch einen Hyperlink zur Akte.xlsm ein.

Ergänze das Makro (im With-Block) mit folgender Zeile:

.Cells(Zelle.Row, 24).Formula = "='" & strAktivitäten & "[Akt-1.xlsm]Tabelle1'!$I$11"

und in die Spalte X wird eine Verknüpfung zur Akte.xlsm eingefügt (kein Hyperlink).

Um die Hyperlinks in die Tabelle "Akt.xlsm" einzufügen, würde ich den Pfad der aktuellen beim Öffnen auslesen, in eine (ausgeblendete) Zelle schreiben und die Hpyerlinks (auf die einzelnen Dokumente?) per HYPERLINK-Formel generieren.

Mit diesem Makro wird z.B. der Pfad zum Ordner Schriftverkehr in die Zelle I1 geschrieben:

Private Sub Workbook_Open()

Dim pfadneu As String

'Achtung! Zelle für Hyperlink anpassen!
'Prüfen in Zelle E1 bereits etwas geschrieben ist und falls ja, Programm verlassen
If Worksheets(1).Range("I1") <> "" Then Exit Sub

'Pfad zum Verzeichnis Schriftverkehr erstellen
Range("I1") = ThisWorkbook.Path & "\Schriftverkehr\"

End Sub

Eine entsprechende Hyperlink-Formel könnte so aussehen:
=WENN(ISTLEER(F11);"";HYPERLINK(I1&F11;F11))
Diese kannst du soweit wie benötigt nach unten ziehen.

Gruß

M.O.

Answer 6

Hallo M.O.,

tut mir leid, dass ich mich jetzt erst melde. Musste noch die Makros ein wenig anpassen. Dauert bei mir immer etwas länger. ;)

von
.Cells(Zelle.Row, 24).Formula = "='" & strAktivitäten & "[Akt-1.xlsm]Tabelle1'!$I$11"

in
.Cells(Zelle.Row, 24).Formula = "='" & strAktivitäten & "\[Akt.xlsm]Tabelle1'!$I$11""

Dann hat es gepasst.


Lediglich beim Pfad "Schriftverkehr" habe ich noch das Problem, dass es mir beim Pfad den Ordner "Aktivitäten" noch bringt. Der sollte allerdings dort durch den Ordner "Schriftverkehr" ersetzen werden.

momentan sieht der link dann so aus:
\\S-file01\ds\CRM\Firma\1\Aktivitäten\Schriftverkehr\

und so sollte er aussehen:
\\S-file01\ds\CRM\Firma\1\Schriftverkehr\

('Pfad zum Verzeichnis Schriftverkehr erstellen
Range("I1") = ThisWorkbook.Path & "\Schriftverkehr\")


Die Formel für den Hyperlink ist klasse. Vielen Dank.

Vielen Dank auch für die Erklärungen.

Gruß Florian

Answer 7

Hallo M.O.,

ein weiteres Problem habe ich noch mit "Diese Arbeitsmappe".

Dort habe ich die Deklaration zum telefonieren mit drin:

Declare Function tapiRequestMakeCall Lib "Tapi32.dll" _
(ByVal DestAddress As String, ByVal AppName As String, _
ByVal CalledParty As String, ByVal Comment As String) As Long

verträgt sich scheinbar nicht mir dem Private Sub Workbook_Open()

dort heißt es immer:

Fehler beim Kompilieren:

konstanten, Zeichenfolgen fester Länge, .... nicht zugelassen.

Kann ich den Fehler irgendwie verhindern?

Dank.

Gruß Florian

Answer 8

Hallo Florian,

ich hatte deinen Verzeichnisaufbau nicht ganz durchschaut :-(.

Versuch es mal damit:

Range("I1") = Left(ThisWorkbook.Path, Len(ThisWorkbook.Path) - 11) & "Schriftverkehr\"

Damit sollte dein Pfad für die Hyperlinks richtig angezeigt werden.

Gruß

M.O.

Answer 9

Hallo Florian,

verchiebe mal die Deklaration sowie das Makro "Telefonieren" in ein allgemeines Modul.
Dann sollte es funktionieren.

Gruß

M.O.

Answer 10

Hallo M.O.

vielen vielen Dank.

Es funktioniert.

Eine letzte Frage hat sich noch aufgetan.

Wie ist es umsetzbar, aus der Übersicht aus der Zeile Name, Plz und Ort in die Akt.xlsm zu übernehmen (kopieren etc.)?

Dank schon mal im voraus.

VG Florian