Introduction
About a year ago, I published a post where I demonstrated several ways of retrieving the image dimensions manually and programmatically using VBA. Delphine asked me if it is possible to retrieve similar size information from online images (using their URLs).
I decided to fulfil Delphine’s request by developing two code snippets that many of you might find useful. The macros below, along with the accompanying workbook, demonstrate:
- How to retrieve the width and height of an image by knowing its URL.
- How to scrape a page for all the images and retrieve the relevant information (URL, title, width, and height).
The workbook you will find in the Downloads section is ready to use in either of the two ways you prefer.
VBA code
Both macros using late binding, so no reference to an external library is required (e.g., Microsoft Internet Controls).
Get image sizes from known URLs
The GetImageSizesFromKnownURLs macro is actually a loop. Based on the given image URLs, it creates a new instance of Internet Explorer, navigates to each URL, and returns the width and the height of each image.
Option Explicit
Sub GetImageSizesFromKnownURLs()
'-----------------------------------------------------------------------
'Loops through the given image URLs and returns information about the
'corresponding images, particularly the width and height of each image.
'Written By: Christos Samaras
'Date: 30/05/2019
'Last Updated: 06/06/2019
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'-----------------------------------------------------------------------
'Declaring the necessary variables.
Dim sht As Worksheet
Dim IE As Object
Dim IEPage As Object
Dim IEPageImgElements As Object
Dim lastRow As Long
Dim i As Long
'Set the sheet that contains the known URLs.
Set sht = ThisWorkbook.Sheets("Known Image URLs")
'Find the last row.
With sht
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
'Check if there is at least one URL.
If lastRow < 4 Then
MsgBox "There are no URLs in the sheet!", vbCritical, "URLs Error"
Exit Sub
End If
'Disable screen flickering and make the cursor wait.
'The wait is used to make the user believe that the process is still running.
'This indeed is happening but in an asynchronous way.
With Application
.ScreenUpdating = False
.Cursor = xlWait
End With
'Clear any existing sizes.
sht.Range("C4:D" & lastRow).ClearContents
'Create a new Internet Explorer instance.
On Error Resume Next
Set IE = CreateObject("InternetExplorer.Application")
'Check if the object was created.
If Err.Number <> 0 Then
'Re-enable the screen and the cursor.
With Application
.ScreenUpdating = True
.Cursor = xlDefault
End With
MsgBox "Sorry, it was impossible to start Internet Explorer!", vbCritical, "Internet Explorer Error"
Exit Sub
End If
'Loop through all the rows.
For i = 4 To lastRow
'Navigate to the requested URL.
IE.Navigate sht.Cells(i, 2).Value
'Wait until the web page is fully loaded.
Do Until IE.ReadyState = 4 'READYSTATE_COMPLETE in early binding
DoEvents
Loop
'Get the document of the URL.
Set IEPage = IE.document
'Find all the images using the tag name property.
Set IEPageImgElements = IEPage.getElementsByTagName("img")
'Check if images were found (technically only one image will be found since you using the exact image URL).
If IEPageImgElements.Length > 0 Then
'Get the size and write it into the sheet.
With sht
.Cells(i, 3) = IEPageImgElements(0).Width
.Cells(i, 4) = IEPageImgElements(0).Height
End With
'Clean the object collection.
Set IEPageImgElements = Nothing
End If
Next i
'Close Internet Explorer.
IE.Quit
'Release the rest of the objects.
Set IEPage = Nothing
Set IE = Nothing
'Adjust the column widths.
sht.Columns("B:D").AutoFit
'Re-enable the screen and the cursor.
With Application
.ScreenUpdating = True
.Cursor = xlDefault
End With
'Inform the user.
MsgBox "The image sizes were retrieved successfully!", vbInformation, "Done"
End Sub Get image sizes by scraping a web page
The GetImageSizesByScrapingWebPage macro, on the other hand, creates a new instance of Internet Explorer, navigates to the given web page (URL), and returns information about all the images that the page contains (source, title, width, and height). It performs similar functionality to this workbook.
Option Explicit
Sub GetImageSizesByScrapingWebPage()
'-------------------------------------------------------------------
'Scrapes a given web page (URL) and returns information about the
'images that the page contains (src, title, width, and height).
'Written By: Christos Samaras
'Date: 30/05/2019
'Last Updated: 06/06/2019
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'-------------------------------------------------------------------
'Declaring the necessary variables.
Dim sht As Worksheet
Dim url As String
Dim IE As Object
Dim IEPage As Object
Dim IEPageImgElements As Object
Dim i As Long
'Set the sheet that contains the url of the page to be scrapped.
Set sht = ThisWorkbook.Sheets("Scraping A Web Page")
'Get the page url.
url = sht.Range("C3").Value
'Check if the requested URL is valid.
If IsURLValid(url) = False Then
MsgBox "Sorry, the URL you provided is not valid!", vbCritical, "URL Error"
Exit Sub
End If
'Disable screen flickering and make the cursor wait.
'The wait is used to make the user believe that the process is still running.
'This indeed is happening but in an asynchronous way.
With Application
.ScreenUpdating = False
.Cursor = xlWait
End With
'Clear any existing sizes.
sht.Range("B6:E105").ClearContents
'Create a new Internet Explorer instance.
On Error Resume Next
Set IE = CreateObject("InternetExplorer.Application")
'Check if the ojbect was created.
If Err.Number <> 0 Then
'Re-enable the screen and the cursor.
With Application
.ScreenUpdating = True
.Cursor = xlDefault
End With
MsgBox "Sorry, it was impossible to start Internet Explorer!", vbCritical, "Internet Explorer Error"
Exit Sub
End If
'Navigate to the requested URL.
IE.Navigate url
'Wait until the web page is fully loaded.
Do Until IE.ReadyState = 4 'READYSTATE_COMPLETE in early binding
DoEvents
Loop
'Get the document of the URL.
Set IEPage = IE.document
'Find all the images using the tag name property.
Set IEPageImgElements = IEPage.getElementsByTagName("img")
'Check if images were found.
If IEPageImgElements.Length > 0 Then
'Loop through all the elements and write the data in the sheet.
For i = 0 To IEPageImgElements.Length - 1
'The data should be placed at the sixth row and below.
With sht
.Cells(i + 6, 2) = IEPageImgElements(i).src
.Cells(i + 6, 3) = IEPageImgElements(i).Title
.Cells(i + 6, 4) = IEPageImgElements(i).Width
.Cells(i + 6, 5) = IEPageImgElements(i).Height
End With
Next i
End If
'Close Internet Explorer.
IE.Quit
'Release the objects.
Set IEPageImgElements = Nothing
Set IEPage = Nothing
Set IE = Nothing
'Adjust the column widths.
sht.Columns("B:E").AutoFit
'Re-enable the screen and the cursor.
With Application
.ScreenUpdating = True
.Cursor = xlDefault
End With
'Inform the user.
MsgBox "The image sizes were successfully retrieved from the given URL!", vbInformation, "Done"
End Sub You can find the code for the IsURLValid function here.
Downloads
You can open the file with Excel 2007 or newer. Please enable macros before using it.
Read also
Get Image Size In Pixels With VBA
Get External Hyperlinks From A Web Page
Page last updated: 12/06/2019
