1

I've attempted the solution in the following. Inserting an Online Picture to Excel with VBA

Unfortunately I get a run-time error '1004' "Unable to get the Insert property of the Picture class" which stops on the following code :

    Set myPicture = ActiveSheet.Pictures.Insert(pic)

Could this be due to my Office version 2016 (64bit) ? If not, are there any suggestions of how I might get embed images to adjacent cells in column AK using the image urls from column AJ ?

Thanks in advance

Community
  • 1
  • 1
LangerFeen
  • 27
  • 5
  • This works fine for me with 2016 64-bit. Can you include more info, such as a URL you are failing to load? – keydemographic Mar 23 '17 at 02:25
  • 1
    Well it seems I'm having 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. Is there a way to get images from s3.amazonaws ? and also, how can I mark which urls errored while moving onto the next url without causing a debug error and stopping the code ?? Thanks again in advance – LangerFeen Mar 23 '17 at 03:03
  • Unfortunately I cannot provide the url as it's linked to client data. I can however paste the url link into a browser that has never been logged into this site and download the image successfully but excel doesn't seem to be able to get the image – LangerFeen Mar 23 '17 at 03:11
  • To avoid the error you can add error handling (on error go to a statement that prints an error in the cell, then returns to the loop that is going over the column). As far as not being able to pull s3 data, checking from a browser was a good test. Does the connection require https? – keydemographic Mar 23 '17 at 05:04
  • 1
    yes, it's https I can provide the following working example : https://ygl-photos.s3.amazonaws.com/7526W5416d77c1190d.jpg – LangerFeen Mar 23 '17 at 22:16

2 Answers2

1

There's some evidence that Excel has trouble downloading from AWS, and I've recreated your issue using the URL you mentioned. In this case, if I were on a deadline I'd put in this fall-back method when the first one fails: download the file, then insert it into the document, then delete the file. 

directDownloadFailed:
Dim FileNum As Long
Dim TempFile As String
Dim FileData() As Byte
Dim WHTTP As Object

Set WHTTP = CreateObject("WinHTTP.WinHTTPrequest.5.1")

WHTTP.Open "GET", imgURL, False
WHTTP.Send
FileData = WHTTP.ResponseBody
Set WHTTP = Nothing

FileNum = FreeFile
TempFile = "path\to\save\img.jpg"
Open TempFile For Binary Access Write As #FileNum
    Put #FileNum, 1, FileData
Close #FileNum

Set img = ActiveSheet.Pictures.Insert(TempFile)

SetAttr TempFile, vbNormal
Kill TempFile

GoTo resumeProcessingImg
Community
  • 1
  • 1
keydemographic
  • 101
  • 6
0

@keydemographic

I appreciate the response and that definitely sounds like an option but I cannot get that code to work or incorporate it into the code I'm using. I get a compile error "Label Not defined" on GoTo resumeProcessingImg

The following will download and embed the images into the cells but the code stops once it gets to the s3 aws image urls. I'll try a few other ways to incorporate your code but I'm not having much luck with it so far.

This is my test file

Sub URL2IMG() 
Dim pic As String 'path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator

Set rng = Range("b2:b12")   '<~~ as needed, Modify range to where the images are to be embedded.
For Each cl In rng
pic = cl.Offset(0, -1)      '<~~ defines image link URL in column to the left of the embedded column
Set myPicture = ActiveSheet.Pictures.Insert(pic)

'you can play with the following to manipulate the size & position of the picture.
 With myPicture
    .ShapeRange.LockAspectRatio = msoFalse

    ' currently this shrinks the picture to fit inside the cell.
    .Width = cl.Width
    .Height = cl.Height
    .Top = Rows(cl.Row).Top
    .Left = Columns(cl.Column).Left

  End With

 Next

 End Sub
LangerFeen
  • 27
  • 5
  • @keydemographic Hi, I haven't been able to incorporate your code to get it to work. Is it possible to use yours with the URL2IMG () code above ? – LangerFeen Mar 29 '17 at 22:11
  • Also, Thanks to @david-zemens for this code at [link - excel-vba-insert-images-from-image-name-in-column](http://stackoverflow.com/questions/15588995/excel-vba-insert-images-from-image-name-in-column/15589423#15589423) – LangerFeen Mar 29 '17 at 22:18
  • Hi @keydemographic, it turns out the problem was with version of Excel I was using, 2016. The URL2IMG code I was using actually works in Excel 2010. It seems the Insert property and/or Picture class has changed in someway that affects images behind s3.amazonaws urls. There is also some evidence that the s3.amazonaws urls are being redirected which may also be causing a problem for vba in 2016 but not in 2010. Either way, Thanks for taking the time to assist. Cheers Gav – LangerFeen Apr 03 '17 at 03:59