Bestimmtes Objekt automatisch in Excel-Datei einfügen

[dachboden20]

Hallo Community, ich benötige Hilfe, vielleicht kann wer helfen oder ihr sagt mir: "No Way".

Ich bin in einem Stahlhandelsunternehmen für die Werkszeugnisverwaltung zuständig und habe selbige bereits von Anfang an betreut und optimiert, sowaeit es mir möglich ist. Zur Versendung an unsere Kunden habe folgende Excel Datei erstellt, wie folgt:



Was bisher super klappt:
Im Feld C1 gebe ich eine Lieferscheinnummer ein, anhand dieser wird dann Feld E1 und C3 ausgefüllt.
In Zelle A6 gebe ich eine Artikelnummer ein, anhand dieser wird mir dann per Index/Vergleich Zell B6 und C6 ausgefüllt.

Nun ist es derzeit so, dass ich die Zellen D6 und E6 immer manuell ausfüllen muss und dann noch manuell die zugehörige Datei einfügen muss um es dann per E-Mail zu versenden.
Kurz zur Erklärung. Auf unserem Lieferschein für den Kunden steht der Dateiname mit drauf, und in der Datei (PDF) dann die Chargen- oder Schmelznummer.

Gibt es eine Möglichkeit, dass ich in Zelle D6 nur noch den Dateinamen manuell eingebe, sodass dann in Zelle E6 per Formel auf der Festplatte in einem Ordner nach dem Dateinamen gesucht wird und die PDF-Datei als Objekt eingefügt wird? Sollte dann so ausshehen: (nur als Beispiel)




Vielleicht kann jemand helfen?

 

[lechef]

Das geht grundsätzlich schon, allerdings nicht per Formel. Dazu brauch es ein stück VBA Code.

ActiveSheet.OLEObjects.Add(Filename:="C:\test.txt", Link:=False, _
DisplayAsIcon:=True, IconFileName:="C:\WINDOWS\system32\packager.dll", _
IconIndex:=0, IconLabel:="").Select

Damit würdest du die Datei "C:\test.txt" als "Symbol" - ich denke das hast du vor - einbinden. Natürlich kann man Dateiname, Icon etc. variabel gestalten.

Mit einem vorangestellten "Range("E6").Select" oder "Cells(6,5).Select" platzierst du das ganze dann auch in der Zelle E6.

Schonmal mit VBA gearbeiteT?

 

[dachboden20]

Danke für die schnelle Hilfe, ich werde mich daran versuchen. VBA-Kenntnisse habe ich nur bedingt.

Edit: Bekomme es nicht hin, da fehlen mir wirklich die VBA-Kenntnisse... Wie stelle ich den Bezug zur Zelle D6 her?

 

[lechef]

Das kommt jetzt drauf an was du in D6 genau eingibst. Wenn es der komplette Pfad + Dateiname ist, dann so:

(jetzt mal nur den relevanten Teil betrachtet)

Add(Filename:=D6, Link:=False

So wie ich dein Beispiel aber verstehe, liegen die Dateien immer im gleichen Ordner, haben immer die Endung .pdf, du gibts aber nur den Namen selbst ein. Dann so:

Add(Filename:="D:\Pfad\zu\den\Dateien\" & D6 & ".pdf", Link:=False

Mit der Eingabe "144521" in D6 würdest du somit D:\Pfad\zu\den\Dateien\144521.pdf einfügen.


Allerdings brauchst du noch einen sinnvollen Trigger, wann das Stück Code ausgeführt wird. Ich würde das ganze erstmal als Sub in einem Modul einfügen:

Sub datei_anfuegen()

Cells(6, 5).Select

ActiveSheet.OLEObjects.Add(Filename:="D:\Pfad\zu\den\Dateien\" & D6 & ".pdf", Link:=False, _
DisplayAsIcon:=True, IconFileName:="C:\WINDOWS\system32\packager.dll", _
IconIndex:=0, IconLabel:="").Select


End Sub

Und dann im Code für das Worksheet:

Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$D$6" Then
    Call datei_anfuegen
End If

End Sub

Wenn in D6 etwas geändert wird, läuft der Code.

Nachteil: Fehlerhafte Eingabe resultiert in Fehlermeldung - funzt aber ansonsten.

 

[dachboden20]

Vielen Dank für deine Mühe, kannst du mir kurz zeigen, wie ich das alles zusammenbringe in einem zusammenhängenden Code!? Habe gerade hin und her versucht, aber es funktioniert einfach nicht!?

 

[lechef]

Klar. Helfen dir diese beiden Bildchen:

 

[dachboden20]

Gut, das funktioniert soweit :-)

Allerdings noch weitere Probleme:

1. Der Inhalt von Zelle D6 ist variabel, das heißt ich gebe dort einen Wert ein, und der Code soll mir die zugehörige Datei mit gleichem Namen im angegebenen Ordner suchen und in E6 wiedergeben.

2. Ich habe meine Excel wie folgt aufgebaut:

Spalte A: Eingabefeld
Spalte B+C: Ausgabefeld in Bezug auf A
Spalte D: Eingabefeld
Spalte E: Ausgabefeld in Bezug auf D (mit deiner Hilfe ;-) )

Wie bereits oben erwähnt beziehe ich mich immer auf Lieferscheine, da ist es oft so, dass ich mehrere Positionen habe, also in der Excel auch mehrere Zeilen mit verschiedenen Artikeln übernehmen muss. Ich habe das bisher so gelöst, dass wenn ich in Spalte E die Tabulator-Taste drücke, sich eine neue Zeile mit der gleichen Formatierung anfügt. Funktioniert das mit dem VBA Code auch irgendwie?

 

[cumulonimbus8]

Ich denke mal laut…

Zunächst mal - was du da anstrengst ist klar Job für eine Datenbank und nicht für eine Tabellenkalkulation.

Zur Versendung an unsere Kunden

Hier ist mir dein Gedankengang unklar.
Ich mache selbst in direktem Versand von Mails aus Excel heraus [es hat andere Gründe, dass da keine Datenbank hintersteht; ich wünschte es wäre so…] Gewisse Daten stehen in Spalten, andere werden generiert und Dateien werden von VBA erfasst und angehängt. Nur platziere ich die nicht in diesem »Excel-Datensatz«, da würde wenn der pure Dateiname reichen.
Wobei ich davon ausgehe, dass du dem Kunden irgendwas, aber nicht diese Excel-Tabelle schickst.

Daher nun meine Frage, ob du bei deinem Prozess nicht irgendwas vereinfachen, verbessern könntest.

CN8

 

[dachboden20]

Hallo, und nochmals vielen Dank für deine Mühe!

Ich habe aus einem anderem Forum auch einen Vorschlag bekommen und den Code mit Hilfe deiner Anregungen geändert. Hier mal die aktuelle Version:

Sub aaa()
  Const strPfad = "Z:\temp\" 'anpassen
  If Dir(strPfad & Range("D6"), vbNormal) = "" Then
    MsgBox "Die Datei gibt es nicht!", vbInformation, "Gebe bekannt..."
  Else
    Range("E6").Select
    ActiveSheet.OLEObjects.Add(Filename:=strPfad & Range("D6"), Link:=False, _
    DisplayAsIcon:=True, IconFileName:="T:\PFT\pft.ico", _
  IconIndex:=0, IconLabel:="").Select
  End If
End Sub

Hier wird in einem Ordner nach einem bestimmten Dateinamen gesucht, den ich in Zelle D6 eingegeben habe und diese Datei wird in Zelle E6 eingebunden.

Nun nochmal die offenen Fragen:

1. Ist es möglich, dass ich nicht immer die Dateiendung mit in Zelle D6 eingeben muss?
2. Möchte ich diesen Code auch in die folgenden Zeilen mit übernehmen, insofern die Spalte A (oder D, denn das sind beiden Spalten, wo ich etwas manuell eintrage) einen Inhalt hat. Ist das auch irgendwie möglich?

 

[lechef]

Zu deinen Fragen:

1.) Klar, sofern die Dateiendung immer die gleiche ist (ich nehme an .pdf), dann kannst du das auch hart im Code verdrahten. Siehe mein Beispiel. Du müsstest Zeile 7 in deinem Code so anpassen:

ActiveSheet.OLEObjects.Add(Filename:=strPfad & Range("D6") & ".pdf", Link:=False, _

2.) Um sicherzugehen dass ich es richtig verstehe: Die Zeilen funktionieren alle nach dem gleichen Prinzip? Du hast nur je nach Fall eben mal 1, 2, oder 10 Zeilen?
Dazu gibt es jetzt mehrere Möglichkeiten. Man kann den Code paramtrieren, oder man kopiert den code und macht mehrere Subs, in denen eben nur die relevanten Teile geändert werden (also statt "D6" dann ein "D7" etc.).
Letzteres ist vielleicht für den Anfänger leichter nachvollziehbar und in diesem kleinen Rahmen auch noch okay, aber programmtechnisch natürlich unschön.

Zuerstmal müsstest du deine Sub entsprechend anpassen:

Sub aaa()
  Dim i As Integer
  i = ActiveCell.Row
  Const strPfad = "Z:\temp\" 'anpassen
  If Dir(strPfad & Cells(i,4).Value & ".pdf", vbNormal) = "" Then
    MsgBox "Die Datei gibt es nicht!", vbInformation, "Gebe bekannt..."
  Else
    Cells(i,5).Select
    ActiveSheet.OLEObjects.Add(Filename:=strPfad & Cells(i,4).Value & ".pdf", Link:=False, _
    DisplayAsIcon:=True, IconFileName:="T:\PFT\pft.ico", _
  IconIndex:=0, IconLabel:="").Select
  End If
End Sub

Zeile 2 definiert die Zählvariable i, in Zeile 3 bekommt sie den Wert der aktuellen Zeile zugewiesen. Idee ist: Du tippst die Lieferscheinnummer ein, dann soll der Code laufen. Fallstrick: Wenn du mit Enter die Eingabe bestätigst, dann bist du ein Zeile unter der Zeile, die du eigentlich haben willst. Im zweifelsfall also noch ein "-1" in die Zeile 3 setzen. Solltest du mit Pfeiltaste rechts arbeiten, bleibst du in der gleichen Zeile, und der Code funzt so. Ist etwas doof, aber auf die schnelle habe ich keine bessere Idee.
Zeile 5, 8 und 9 habe ich entsprechend angepasst. Statt Range wird Cells verwendet (Zeile, Spalte) - damit kann man besser Zählen. Ich glaube übrigens, dass du in Zeile 5 auch noch das / & ".pdf" / anfügen musst, gehört noch zur ersten Frage. Kann ich jedoch gerade nicht testen.


Den Trigger musst du auch noch bearbeiten

Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address = "$D$6" or Target.Address = "$D$7" or Target.Address = "$D$8" Then
    Call datei_anfuegen
End If
 
End Sub[code]

-> Du siehst wo die Reise hingeht. Die möglichen Zellen angeben.



Eleganter wäre es, wenn man das Makro aber nur einmal bei Bedarf laufen lässt.
Also erstmal alle Zeilen und befüllen, und dann von Hand starten (per Button z.B.). Das Makro läuft dann durch die relevanten Zeilen und fügt die Anhänge ein. Damit hätte man auch das Problem erschlagen mit dem Zeilensprung bei Eingabe mit Enter.

Wäre das eher was?

 

[dachboden20]

Ich könnte sicher für die Zeilen, die ich habe, immer einzeln den Code kopieren. Es handelt sich um selten mehr als 20 Zeilen, die ich fülle. Aber schöner wäre sicher die Variante, die du zuletzt ansprichst. Ich gebe alles an Daten ein, drücke auf den Makrobutton, und ab geht die Post. ;-)

 

[lechef]

Dann probier es mal mit diesem Code:

Sub aaa()
  
  Dim i As Integer
  i=6
  Const strPfad = "Z:\temp" 'anpassen
  

  Do While Cells(i,1).value <> ""

    If Dir(strPfad & Cells(i,4).Value & ".pdf", vbNormal) = "" Then
      MsgBox "Die Datei gibt es nicht!", vbInformation, "Gebe bekannt..."
    Else
      Cells(i,5).Select
      ActiveSheet.OLEObjects.Add(Filename:=strPfad & Cells(i,4).Value & ".pdf", Link:=False, _
      DisplayAsIcon:=True, IconFileName:="T:\PFT\pft.ico", _
      IconIndex:=0, IconLabel:="").Select
    End If
  
    i=i+1

  Loop

End Sub

Damit rennt er in Zeile 6 (i=6) los, und arbeitet Zeile für Zeile ab. Und zwar so lange, wie in der ersten Spalte etwas drinsteht. Ggfs. musst du die Abbruchbedingung anpassen, ich kenne die Struktur der Tabelle ja nicht.
Wenn nach den Artikelzeilen erstmal ne Leerzeile kommt, und auch zwischendrin keine Leerzeile ist, passt es.

Den Trigger im Code des Arbeitsplatzes kannst du erstmal komplett löschen. Erstmal schauen, dass das Makro sauber läuft. Kannst ja übers Menü ausführen.

 

[dachboden20]

sieht gut aus, und jetzt das ganze so, dass auch in Unterordnern von "Z:\temp" gesucht wird??? :-)

 

[cumulonimbus8]

Mal eine Gegenfrage:
Was soll passieren wenn die Datei dem Namen nach mehrmals in dieser Ordnerstruktur existiert?

Ich habe zwar was geschrieben was rekursiv mittels «FSO» Ordner abklappert, aber der Code (den ich gerade gefunden habe, es müsste aber ein besserer existieren) taugt nicht ihn direkt hier anzugeben.
Mit DIR() kommt man hier nicht weiter.

Für diesen Moment,
CN8

 

[dachboden20]

Mal eine Gegenfrage:
Was soll passieren wenn die Datei dem Namen nach mehrmals in dieser Ordnerstruktur existiert?

Zum Glück wird es jeden Dateinamen nur 1x geben :-)

Habe noch folgende offene Fragen:

1. Die Sache mit den Unterordnern!?

2. Habe ich zur E-Mail-Versendung eines Ausschnittes aus diesem Arbeitsblatt folgenden Code gefunden:

Sub BereichAlsEMailVersenden()
      Dim Empänger, Titel As String
      Dim n As Range
      Empfänger = "Test@aol.com"
      Titel = "Excel-Bereich als Anhang"
      Set n = Application.InputBox _
         ("Wählen Sie den Bereich aus, den Sie versenden möchten", Type:=8)
      Range(n.Address).Select
      Selection.Copy
      Worksheets.Add
      ActiveSheet.Paste
      ActiveWorkbook.SaveAs "Anhang.xls"
      Application.Dialogs(xlDialogSendMail).Show Empfänger, Titel
   End Sub

Es wird für den Ausschnitt ein neues Arbeitsblatt erstellt, aber nun habe ich das Prblem, dass die Formatierung nicht übernommen wird. Das heitß, Zellgrößen passen nicht. Kann ich das irgendwie beeinflussen?
Wie kann ich der Mail einen Standardtext und meine Signatur hinzufügen?