Building reports is a common task for me and I often struggle putting images in Excel so this post is just a small reference for myself to easy find how I have done it before.
I have build SQL before which would build html to display product images.
select '<tr><td>' + productid + '</td></tr>' from products
And would paste that in a text file which has <table>…</table> and would save it as .html
That’s ugly and hard to work with, but it would/could show images (if you use <img src=’http://www.google.com/’ etc.)
Copying from the SMSS result panel
and pasting in Excel is an option to work with the data (apply extra filters or ordering etc.)
The open source SQL Operations Studio even has a community contribution to export directly to Excel! I just found out searching for the download link that the product is renamed to Azure Data Studio
But then you only have a filename or uri or (local) path to a file in a sheet
I stored the Excel file as one with macro’s enabled and enabled the developer menu on the lint.
- Click the File tab.
- Click Options.
- Click Customize Ribbon.
- Under Customize the Ribbon and under Main Tabs, select the Developer check box.
And I used this small VB sub:
Sub PlacePics() Dim Path As String, Pics As Range, Pic As Range Path = "https://www.ourcompany.com/images/" Set Pics = ActiveSheet.Range("F2:F2164") For Each Pic In Pics On Error Resume Next Pic.RowHeight = 100 Pic.Offset(0, 0).Select ActiveSheet.Pictures.Insert(Path & Pic.Value).Select Next Pic End Sub
It might take a while to process (depending on the amount of rows). But it gets the job done.