1. #1
    Cadet 1st Year
    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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:

    Klicke auf die Grafik für eine größere Ansicht 

Name:	Unbenannt.PNG 
Hits:	57 
Größe:	11,7 KB 
ID:	659198

    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)

    Klicke auf die Grafik für eine größere Ansicht 

Name:	Unbenannt1.PNG 
Hits:	44 
Größe:	18,9 KB 
ID:	659199


    Vielleicht kann jemand helfen?

  2. Anzeige
    Logge dich ein, um diese Anzeige nicht zu sehen.
  3. #2
    Lt. Junior Grade
    Dabei seit
    Mär 2007
    Beiträge
    381

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

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

    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?
    Geändert von lechef (03.01.2018 um 10:54 Uhr)

  4. #3
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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?
    Geändert von dachboden20 (03.01.2018 um 11:11 Uhr)

  5. #4
    Lt. Junior Grade
    Dabei seit
    Mär 2007
    Beiträge
    381

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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)

    Code:
    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:

    Code:
    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:
    Code:
    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:

    Code:
    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.

  6. #5
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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!?

  7. #6
    Lt. Junior Grade
    Dabei seit
    Mär 2007
    Beiträge
    381

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    Klar. Helfen dir diese beiden Bildchen:

    Klicke auf die Grafik für eine größere Ansicht 

Name:	1.png 
Hits:	32 
Größe:	62,2 KB 
ID:	659243
    Klicke auf die Grafik für eine größere Ansicht 

Name:	2.png 
Hits:	21 
Größe:	40,1 KB 
ID:	659244

  8. #7
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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?
    Geändert von dachboden20 (03.01.2018 um 17:19 Uhr)

  9. #8
    Vice Admiral
    Dabei seit
    Apr 2012
    Ort
    im Herzen Deutschlands
    Beiträge
    6.870

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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
    »Regen, Regen, gehe weg · verzieh’ dich an ’nen and’ren Fleck!« (nach Hägar, dem Schrecklichen)

  10. #9
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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:

    Code:
    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?

  11. #10
    Lt. Junior Grade
    Dabei seit
    Mär 2007
    Beiträge
    381

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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:
    Code:
    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:

    Code:
    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
    [code]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?

  12. #11
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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. ;-)

  13. #12
    Lt. Junior Grade
    Dabei seit
    Mär 2007
    Beiträge
    381

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    Dann probier es mal mit diesem Code:

    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.

  14. #13
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

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

  15. #14
    Vice Admiral
    Dabei seit
    Apr 2012
    Ort
    im Herzen Deutschlands
    Beiträge
    6.870

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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
    »Regen, Regen, gehe weg · verzieh’ dich an ’nen and’ren Fleck!« (nach Hägar, dem Schrecklichen)

  16. #15
    Cadet 1st Year
    Ersteller dieses Themas

    Dabei seit
    Jan 2018
    Ort
    Riesa
    Beiträge
    13

    AW: Bestimmtes Objekt automatisch in Excel-Datei einfügen

    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:

    Code:
    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?

Berechtigungen

  • Neue Themen erstellen: Nein
  • Themen beantworten: Nein
  • Anhänge hochladen: Nein
  • Beiträge bearbeiten: Nein
  •  
Forum-Layout: Feste Breite / Flexible Breite