in Spalte A und dem jeweiligen Bild in Spalte B. In dieser Tabelle befinden sich weitere Angaben zu den Artikeln wie EAN-Code und dergleichen. Der Datenbereich beginnt in Zeile 2.
c) Aufgaben:
Die richtigen Fotos aus der Tabelle Fotos sollen in die Tabelle Artikel verknüpft bzw. eingefügt werden.
Weitere Angaben aus der Tabelle Fotos sollen in die Tabelle Artikel übernommen werden.
Die Bilder sollen auch auf anderen PCs angezeigt werden können, also vollständig in Excel eingebettet sein.
Schön solls auch aussehen!
B) Lösungs-Weg
1) Anwendung von SVERWEIS für Auslesen des EAN-Codes (und anderer Daten)
=WENN(ISTFEHLER(SVERWEIS(F3;FOTOS!$A$2:FOTOS!$Q$9999;14;FALSCH));0;
SVERWEIS(F3;FOTOS!$A$2:FOTOS!$Q$9999;14;FALSCH))
Formel in Tabelle Artikel an gewünschter Stelle einfügen und mit Drag/Drop runterziehen
F3 = Hier befindet sich die eindeutige Artikelnummer
FOTOS!$A$2:FOTOS!$Q$9999;14 = Suchbereich aus der Tabelle Fotos, wobei in Spalte A (=erste Spalte) die eindeutige Artikelnummer und in Spalte 14 der EAN-Code steht.
Fehler abfangen durch WENN(ISTFEHLER): Da nicht bei allen Artikeln ein EAN-Code vorhanden ist, wird ein möglicher Fehler durch SVERWEIS abgefangen und stattdessen 0 hingeschrieben.
2) Exportieren der Bilder in einen Ordner
Export aller Bilder aus Tabelle Fotos mit Hilfe der Speicher-Funktion Als Website speichern
Website-Export-Format auf Kompatibilität Internet Explorer 6 setzen (damit wird eine doppelte Ausgabe der Bilddateien verhindert).
Export auf Tabelle einschränken
Es wird eine .htm-Datei angelegt und ein Ordner im gleichen Verzeichnis, in dem sich alle Bilder befinden.
Diesen Ordner umbennen in bilder
Excel exportiert die Bilder DER REIHENFOLGE nach (Zeile für Zeile) und benennt sie entsprechend, also heißt das erste Bild image001.jpg
ACHTUNG: Ist das exakt gleiche Bild mehrfach vorhanden, wird es dennoch nur einmal exportiert. Es muss bei dieser Methode zwingend darauf geachtet werden, dass die Menge der exportierten Fotos mit den vergebenen Dateinamen übereinstimmt! Beispiel: 1.500 Fotos sind vorhanden, das erste Bild ist image001.jpg, das letzte Bild ist image1500.jpg
3) Optional: Bilder verbessern über Photoshop-Batch-Verarbeitung
Die Bilder liegen in unterschiedlichen Formaten vor, für die Endverarbeitung sollen diese alle gleich aussehen und schön in der Artikel-Liste angezeigt werden.
In Photoshop einen kleinen Aktions-Satz anlegen:
Bild zuschneiden auf Pixelfarbe oben links (weiße Leerfläche um die Artikelbilder wird entfernt)
Bild einpassen: gleiche Werte für Horizontal und Vertikal eingeben (z.B. 150x150px)
Arbeitsfläche anpassen: gleiche Werte für Horizontal und Vertikal eingeben, etwas größer als unter Bild einpassen, z.B. 160x160px
Speichern unter in den Aktionssatz einbeziehen (erspart unnötige Rückfragen von Photoshop)
Aktionssatz unter Stapelverarbeitung ausführen
Ergebnis: Alle Bilder sind quadratisch, haben die gleiche Größe und ein bisschen Luft zum Atmen
4) Deklarierung der Bildnamen in der Fotos-Tabelle
Wie unter Punkt B1) beschrieben, werden die Bilder beim Export fortlaufend durchnummeriert. Diese Nummerierung mittels Drag&Drop in einer freien Spalte in der Fotos-Tabelle nachvollziehen: In folgendem Beispiel habe ich in Spalte D beim ersten Artikel einfach 1 erfasst und mittels Drag&Drop bis zum Ende runtergezogen, beim letzten Artikel steht dann 1500
Bild-Dateiname und relativen Pfad in einer freien Spalte (z.B. E) konstruieren: ="\bilder\image"&TEXT(D2;"000")&".jpg"
Formel mittels Drag&Drop runterziehen
Neue Spalte nach E anlegen, als nur Text formatieren, Daten aus Spalte E kopieren und mittels Inhalte einfügen - nur Werte einfügen (es wird ein sauberer String-Wert für den später folgenden VBA-Code benötigt).
In Spalte F befindet sich nun \bilder\image001.jpg in der ersten Datenreihe, \bilder\image002.jpg in der zweiten Reihe usw.
5) Bilder einfügen über VBA-Code
Neues Modul einfügen
Code einfügen:
Option Explicit
Private Declare Function KillTimer Lib "user32.dll" ( _
ByVal hWnd As Long, _
ByVal nIDEvent As Long) As Long
Private Declare Function SetTimer Lib "user32.dll" ( _
ByVal hWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long) As Long
Private Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Const GC_CLASSNAMEMSEXCEL = "XLMAIN"
Private objCell As Range
Private strPicturePath As String
Private hWnd As Long
Public Function Grafik(Zelle As Range, Pfad As Range) As String
Set objCell = Zelle.Cells(1, 1)
strPicturePath = ThisWorkbook.Path & Pfad.Cells(1, 1).Value
hWnd = FindWindow(GC_CLASSNAMEMSEXCEL, Application.Caption)
SetTimer hWnd, 0, 1, AddressOf prcTimer
End Function
Private Sub Grafik_einfuegen()
Dim objShape As Picture
Set objShape = ActiveSheet.Pictures.Insert(strPicturePath)
With objShape
.Top = objCell.Top + 1
.Left = objCell.Left
.Height = objCell.Height - 8
.Width = objCell.Width - 8
End With
End Sub
Private Sub prcStopTimer()
KillTimer hWnd, 0
End Sub
Private Sub prcTimer(ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long)
On Error Resume Next
Call prcStopTimer
Call Grafik_einfuegen
End Sub
Dieser Code fügt ein Bild in der im ersten Parameter angeführten Zelle, unter Auslesen des relativen Pfads + Bildnamens aus der im zweiten Parameter angeführten Zelle aus.
Der absolute Pfad wird automatisch mit Hilfe von ThisWorkbook.Path zusammengebaut.
Die Bilder werden entsprechend der Zellgröße eingefügt, wobei sie gleichzeitig um ein paar Pixel kleiner skaliert und minimal innerhalb der Zelle runtergerückt werden.
Ergebnis: Absolut einheitliche Ausrichtung der Bilder in den gewünschten Zellen und etwaige Rahmenformatierungen bleiben erhalten.
6) Funktionsaufruf des Bild-Einfügen-Codes
In Tabelle Artikel in eine beliebige freie Zelle in der ersten Datenreihe, z.B "Y3" folgende Funktionsformel eingeben: =grafik(A3;L3)
Erster Wert "A3" = Dort soll das Bild eingefügt werden, also in der ersten Spalte der ersten Datenreihe (vorab Sicherstellen, dass Spalte A auch frei ist)
Zweiter Wert "L3" = Dort steht der Pfad und der Bildname, der mittels SVERWEIS ausgelesen wird:
=WENN(ISTFEHLER(SVERWEIS(F3;FOTOS!$A$2:FOTOS!$Q$9999;6;FALSCH));0;
SVERWEIS(F3;FOTOS!$A$2:FOTOS!$Q$9999;6;FALSCH))
Hinweis: Der Wert 6 bezieht sich auf die 6. Spalte F in der Fotos-Tabelle. Dort befinden sich der relative Pfad und der Dateiname des Bildes (siehe Punkt 3)
Mittels Drag&Drop runterziehen. Leider passiert (fast) nichts. Es wird nämlich keine Formel, sondern eine Funktions-Formel runtergezogen und die Funktions-Formel wird nur bei expliziter Bestätigung ausgeführt. Deshalb noch ein kleiner Code:
7) Code zum automatisierten Anklicken der "Funktionsformel"
Neues Modul anlegen
Code einfügen:
Public Sub F2Enter()
Dim zelle2 As Object
Sheets(1).Range("Y1:Y1365").Select
For Each zelle2 In Selection
SendKeys "{F2}", True
SendKeys "{ENTER}", True
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Next zelle2
End Sub
In der Zeile Sheets(1).Range("Y3:Y1365").Select ist im Rangebereich der gewünschte Zellenbereich anzugeben
Dieser Code geht in jede Zelle im angeführten Range-Bereich, drückt dort F2 (Zelle aktivieren) und anschließend ENTER. Damit wird die Funktions-Formel ausgeführt.
Damit der Code nicht zu schnell ausgeführt wird, ist eine 2-Sekunden-Bremse eingebaut. Dieser Wert kann in der Zeile newSecond = Second(Now()) + 2 geändert werden.
Formular-Schaltfläche anlegen und das Makro F2Enter auswählen.
Schaltfläche drücken, zuschauen, freuen & FERTIG!
