Google Sheets IMAGE Function

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Is there any way to replicate the IMAGE function from Google Sheets in Excel? Maybe a VBA code? From what I can tell, the function in Google Sheets takes a URL and imbeds that image into a cell, not a hovered object like Excel would. The syntax is =IMAGE(url, [mode], [height], [width]). An example pulling in Google's logo is =IMAGE("https://www.google.com/images/srpr/logo3w.png").
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
In case you missed it, see post #18 where I responded re: png v jpg.
I don't see that it will add more time to process at all - I'm not suggesting that you would need to do anything other than what you're already doing. With the method I used above, Excel is still downloading the image (it obviously has to do that in order to get it) and then inserts it - it's just with the method above, we don't 'see' that process. What I'm suggesting now is skipping that inbuilt method and coding each step of downloading it and inserting it. Again, you wouldn't be anything different and Excel will essentially be doing the same thing, just in a way where whatever limitations exist on the inbuilt method have been removed with my code.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows
I'm sorry, I missed #18. I'll wait to see what you come up with next. Thanks again.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
Do you happen to know if you have 32-bit or 64-bit office?
Don't worry about that - I think I'm pretty much done, but out of curiosity, are all of the images from this server? Do any work?
Also, if you need to do a bulk download/insert, are you certain that you want to do it through a worksheet function? It would be quicker and easier if you just ran it as a subroutine - you would also avoid the problem of the ever looming threat of all the formulas redownloading the images on recalculation of the spreadsheet.
 
Last edited:

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
So this works for me:
1642115214073.png

The way it works is itlooks to see if you have a directory called C:\TEMPIMAGES, if not it makes one. Then it creates a filename to download the image to based on the URL - this turned out to be be problematic when it came to the URL for your avatar because of the annoying question mark - that's been dealt with, but just bear that in mind in case there are any errors in future (hopefully not, though).

If a file by that name already exists Excel doesn't download it again, but instead uses that file as the picture - this saves your PC's CPU, your internet allowance (?) and your sanity from downloading these images multiple times.

It then inserts the image as before. Looking online, this seems to be the standard approach to this problem (not a new issue, it seems, with some images). Will post code in a moment.
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I really do think that this is only really a good idea for just a few images - when talking on the scale of 1500, it's best to just bulkdownload and insert with a simple subroutine. Leveraging the function below. Would you like to do that instead?

Fingers and toes crossed:
VBA Code:
#If VBA7 Then
    Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
    Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Private Const ERROR_SUCCESS As Long = 0
Private Const BINDF_GETNEWESTVERSION As Long = &H10
Private Const INTERNET_FLAG_RELOAD As Long = &H80000000
Enum ImageModeEnum
    FitCell_MaintainAspect = 1              'resizes the image to fit inside the cell, maintaining aspect ratio.
    FitCell_IgnoreAspect = 2                'stretches or compresses the image to fit inside the cell, ignoring aspect ratio.
    OriginalSize = 3                        'leaves the image at original size, which may cause cropping.
    CustomSize = 4                          'allows the specification of a custom size.
End Enum

Function IMAGE(TargetURL As String, Optional ImageMode As ImageModeEnum = 3, Optional CustomHeight As Long = -1, Optional CustomWidth As Long = -1)
    
    Dim TargetCell As Range, Img As Object
    Dim CallerCell As Variant, TargetFileName As String
    
    Const BASEPATH = "C:\TEMPIMAGES\"
    
    If Len(Dir(BASEPATH, vbDirectory)) = 0 Then MkDir BASEPATH
        
    CallerCell = Application.Caller.Address
    
    If VarType(CallerCell) = vbString Then
        Set TargetCell = Range(CallerCell)
        
        TargetFileName = BASEPATH & GetFilenameFromURL(TargetURL)
        If Len(Dir(TargetFileName)) <> 0 Then Kill TargetFileName
        DownloadFile TargetURL, TargetFileName
        TargetCell.Parent.Shapes.AddPicture Filename:=TargetFileName, linktofile:=msoFalse, savewithdocument:=msoTrue, Top:=TargetCell.Top, Left:=TargetCell.Left, Width:=CustomWidth, Height:=CustomHeight
        Set Img = TargetCell.Parent.Shapes(TargetCell.Parent.Shapes.Count)
        With Img
            .LockAspectRatio = IIf(ImageMode = 2, msoFalse, msoTrue)
            .Placement = xlMoveAndSize
            Select Case ImageMode
                Case FitCell_MaintainAspect
                    If .Width > .Height Then
                        .Width = TargetCell.Width
                    Else
                        .Height = TargetCell.Height
                    End If
                Case FitCell_IgnoreAspect
                    .Width = TargetCell.Width
                    .Height = TargetCell.Height
                Case CustomSize
                    .ShapeRange.LockAspectRatio = msoFalse
                    If CustomHeight >= 0 And CustomWidth >= 0 Then
                        .Width = CustomWidth
                        .Height = CustomHeight
                    ElseIf CustomHeight >= 0 Then
                        .Height = CustomHeight
                    Else
                        .Width = CustomWidth
                    End If
            End Select
        End With
        
    End If
    
    IMAGE = ""
End Function

Private Function DownloadFile(ByVal SourceURL As String, ByVal LocalFile As String) As Boolean
   DownloadFile = URLDownloadToFile(0&, SourceURL, LocalFile, BINDF_GETNEWESTVERSION, 0&) = ERROR_SUCCESS
End Function

Private Function GetFilenameFromURL(ByVal FilePath As String) As String
    If Right$(FilePath, 1) <> "/" And Len(FilePath) > 0 Then
        If InStr(FilePath, "?") > 0 Then FilePath = Split(FilePath, "?")(0)
        GetFilenameFromURL = GetFilenameFromURL(Left$(FilePath, Len(FilePath) - 1)) + Right$(FilePath, 1)
    End If
End Function
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Dan, here is what I've figured out from your code. (BTW it works perfectly!).

I'm pulling the URL from a large text string. The formula I'm using to do this is:

=IFERROR(MID(XLOOKUP(A3,DataDump[id],DataDump[images]),SEARCH("http",XLOOKUP(A3,DataDump[id],DataDump[images])),SEARCH("jpg",XLOOKUP(A3,DataDump[id],DataDump[images]))-SEARCH("http",XLOOKUP(A3,DataDump[id],DataDump[images]))+3),"")

The result of the formula in this particular cell is:


If I enter this formula in a cell:

=IMAGE(IFERROR(MID(XLOOKUP(A3,DataDump[id],DataDump[images]),SEARCH("http",XLOOKUP(A3,DataDump[id],DataDump[images])),SEARCH("jpg",XLOOKUP(A3,DataDump[id],DataDump[images]))-SEARCH("http",XLOOKUP(A3,DataDump[id],DataDump[images]))+3),""),2)

Or this formula:

=IMAGE(https://cf-images.us-east-1.prod.bo...-995d-88c23fa22ee9/1280x720/match/image.jpg,2)

I get a VALUE# error

The only way it works is to run the formula in one cell, then refer to it in another cell. That's absolutely fine and it works perfectly. I'm just curious, is that the way it was written to do that?

Regarding your comment "when talking on the scale of 1500, it's best to just bulkdownload and insert with a simple subroutine. Leveraging the function below. Would you like to do that instead?", how would that be different on my end and what does that entail for you?
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You are putting the URL in quotation marks, though, aren't you?
=IMAGE(IFERROR(MID(XLOOKUP(A3,DataDump[id],DataDump[images]),SEARCH("http",XLOOKUP(A3,DataDump[id],DataDump[images])),SEARCH("jpg",XLOOKUP(A3,DataDump[id],DataDump[images]))-SEARCH("http",XLOOKUP(A3,DataDump[id],DataDump[images]))+3),""),2)
There's way too much going on with this formula for me to make sense of it. Even if I had it infront of me, I think I would make liberal use of named ranges etc, and some kind of dashboard setting out in a cell what the results of each element were. Otherwise you just unpick the whole thing each time it doesn't work out.
Regarding your comment "when talking on the scale of 1500, it's best to just bulkdownload and insert with a simple subroutine. Leveraging the function below. Would you like to do that instead?", how would that be different on my end and what does that entail for you?
I am always nervous about making internet calls through a worksheet function, because invariably all the functions will be updated (i.e., executed) each time there is a sheet recalculation ...

Slightly different process for you, and not much work for me. What I propose is that you use a subroutine that downloads and inserts the pictures upon the press of a button (for example), rather than using a worksheet function. The process would be the same - it's just that you would predesignate which range of cells contained the URLs either in the code, or simply by selecting them, and then running a macro which you can do by click on a button. For me, it's just a matter of tweaking the code above slightly to respond to that button press. As I mentioned above, the function currently only runs from the worksheet, so I need to adjust that (which I should probably do anyway).

Basically, unless you expect that these 1500 pictures are going to be regularly changing, and that your workbook needs to have a constantly up-to-date copy of those constantly changing images, there really is no reason to have this as worksheet function. But then, I don't know how you intend to use the code. I'll tell you what, I'm going to do the adjustments anyway and you can take a view as to which one makes more sense for you.
 
Last edited:

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
So I just checked, and this worked for me

VBA Code:
=image("https://cf-images.us-east-1.prod.boltdns.net/v1/static/4684385845001/6693d3fb-a125-4429-8af3-672a728ff2ca/3f997ccb-4213-482a-995d-88c23fa22ee9/1280x720/match/image.jpg",2)


1642129954410.png
 

Dan_W

Well-known Member
Joined
Jul 11, 2018
Messages
786
Office Version
  1. 365
Platform
  1. Windows
Hi - how did you go with the image above? Did you manage to get it to work?
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
143
Office Version
  1. 365
Platform
  1. Windows
...and today it's working for me too! Don't know why the change. One odd thing I noticed is the formula you sent to me has "image" in lower case, but when I run this, it changes it to upper case like any other function.

You are absolutely correct, even trying to do this on 300 rows is a drag on the system. I don't see how it will work for 1,500 rows.

I need to think about what I can do next.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,493
Messages
5,764,698
Members
425,231
Latest member
mramb

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top