Created
August 11, 2016 12:55
-
-
Save mcnemesis/e1b2beb23d11c0948319538a3e0f72aa to your computer and use it in GitHub Desktop.
Excel VBA Macro to insert image into a cell based on url in another cell
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub InstallPictures() | |
Application.ScreenUpdating = False | |
Application.Calculation = xlCalculationManual | |
Dim url_column As Range | |
Dim image_column As Range | |
'column with url... | |
Set url_column = Worksheets(1).UsedRange.Columns("AE") | |
'column where image will be inserted | |
Set image_column = Worksheets(1).UsedRange.Columns("AF") | |
Dim i As Long | |
For i = 2 To url_column.Cells.Count | |
Set Picture = image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value) | |
Picture.Left = image_column.Cells(i).Left | |
Picture.Top = image_column.Cells(i).Top | |
Picture.Height = 40 | |
image_column.Cells(i).EntireRow.RowHeight = 40 | |
Next | |
Application.ScreenUpdating = True | |
Application.Calculation = xlCalculationAutomatic | |
End Sub |
Ah, it seems I'm having trouble with trouble with images from https://s3.amazonaws.com.
In my test file of urls, I added in a couple of extra image urls to google and a few other logos which all downloaded and embedded correctly.
Unfortunately, the code doesn't error trap and move on to the next record/row if there's a problem.
Is there a way to get images from s3.amazonaws ?
and also, how can I mark an adjacent cell for an erring url while moving onto the next url ??
Thanks again in advance
This not getting the pictures from this URL
https://cdn.shopify.com/s/files/1/0965/5800/products/K5104-AF05-anvil-tt-pave-large-slider-with-chain.jpg?v=1541170590
any reason that why its not working.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Hi,
Thank you. This looks like exactly what I needed but I get a run-time error '1004'
"Unable to get the Insert property of the Picture class" which stops on the following code :
Set Picture = image_column.Worksheet.Pictures.Insert(url_column.Cells(i).Value)
I'm using Excel 2016 64bit. I think this might require a change to some of the code for the 64bit version but I don't know enough about vba.
Can you suggest a fix ?
Thanks in advance