copy

Sunday, 20 May 2018

Last updated: 20/05/2018, 1 min read

Get External Hyperlinks From A Webpage


Introduction



Some years ago, I published a VBA code that proved to be very popular in blog users: the code and the accompanying workbook could be used for downloading files from the internet. One common request that I get since I published that code, is how to retrieve the hyperlinks from a webpage, so as to download the files afterwards.

I decided to help all these blog users by creating a workbook that does exactly what they requested. So, the workbook that you will find in the downloads section can be used for grabbing all the hyperlinks from a given URL. The procedure is quite straightforward: just enter the URL on a specific cell and hit the Get Links button. After a few seconds (depending on your internet speed) you will have all the external hyperlinks from the particular URL. Moreover, apart from the hyperlinks, you will also get the displayed text for each hyperlink. In this way, you can match the hyperlink with its position on the webpage and filter out those links that are not relevant.

NOTE: the code ignores internal hyperlinks and anchor tags. In other words, the hyperlinks that are retrieved should all start with "http".



Demonstration video



The short video below demonstrates the usage of the spreadsheet using as an example the URL of this blog. In the particular case, 43 hyperlinks retrieved.




Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it. The VBA code was protected using the Unviewable+.



Read also



Excel & VBA: Download Internet Files Automatically

Monday, 30 April 2018

Last updated: 03/05/2018, 2 min read (without the code)

Open A Password-Protected PDF File With VBA


Introduction



In the past, we have seen two ways to open PDF files with VBA: the first one involved the usage of the Adobe Object Model and it could be only used with Adobe Professional. The second one was more generic; it was taking advantage of the Windows API functions and it could be used by both Adobe Reader and Professional. Both ways worked and continue to work just fine. There is a problem, though: what will happen if the PDF file you want to open is password-protected? Is there a way to fill the password in the open dialog and continue opening the file?

The answer is, yes! The suggested solution relies on several Windows APIs since the Adobe Object Model does not provide an option/method for including the password when opening a PDF file. Note: the VBA code that you will see below is NOT a password cracking piece of code! The code implies that you know the password of the file. It just automates the opening procedure, especially if you have to open multiple PDF files.


Spy++ For Opening Locked PDF File


Main idea



The main idea behind the code below can be divided into four steps:
  1. First of all, the code checks if the given path is valid (e.g. the file exists). Then, the ShellExecute API is used to open the file using the associated default program (either Adobe Reader or Adobe Professional).
  2. The FindWindow API function is used to find the pop-up window that prompts the user to fill the password.
  3. By using the FindWindowEx API function, the code searches the subsequent child windows until the text box is reached (using the class RICHEDIT50W).
  4. Finally, the SendMessage and PostMessage API functions are invoked in order to fill the known password and hit the Open button.
Similar to many previous posts, the Spy++ software was used to specify the windows hierarchy when opening the password-protected file. The above image shows the window tree that should be navigated in order to find the text box that will receive the password.



VBA code



Below you will find the OpenLockedPdf macro that does the main work, the FileExists function that is responsible for testing the file existence, as well as the SamplePdfTest macro that performs a sample test. Note that:
  • The OpenLockedPdf macro can be used to open a PDF file that is NOT password-protected. In that case, the code that is right after the line of ShellExecute is ignored.
  • The macro can be used in every Office application, as well as in AutoCAD. It works with both 32 and 64-bit applications.

Option Explicit

'Declaring the necessary API functions for both 64 and 32 bit applications.
#If VBA7 And Win64 Then
    
    'For 64 bit applications.
    'Performs an operation on a specified file.
    Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                                        (ByVal hwnd As LongPtr, _
                                                        ByVal lpOperation As String, _
                                                        ByVal lpFile As String, _
                                                        ByVal lpParameters As String, _
                                                        ByVal lpDirectory As String, _
                                                        ByVal nShowCmd As Long) As LongPtr
    
    'Retrieves a handle to the top-level window whose class name and window name match the specified strings.
    'This function does not search child windows. This function does not perform a case-sensitive search.
    Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
                                                        (ByVal lpClassName As String, _
                                                        ByVal lpWindowName As String) As LongPtr
            
    'Retrieves a handle to a window whose class name and window name match the specified strings.
    'The function searches child windows, beginning with the one following the specified child window.
    'This function does not perform a case-sensitive search.
    Public Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
                                                        (ByVal hWnd1 As LongPtr, _
                                                        ByVal hWnd2 As LongPtr, _
                                                        ByVal lpClassName As String, _
                                                        ByVal lpWindowName As String) As LongPtr
       
    'Sends the specified message to a window or windows. The SendMessage function calls the window procedure
    'for the specified window and does not parentWindowurn until the window procedure has processed the message.
    Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
                                                        (ByVal hwnd As LongPtr, _
                                                        ByVal wMsg As Long, _
                                                        ByVal wParam As LongPtr, _
                                                        lParam As Any) As LongPtr
        
    'Places (posts) a message in the message queue associated with the thread that created the specified
    'window and parentWindowurns without waiting for the thread to process the message.
    Public Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
                                                        (ByVal hwnd As LongPtr, _
                                                        ByVal wMsg As Long, _
                                                        ByVal wParam As LongPtr, _
                                                        ByVal lParam As LongPtr) As Long
                                                    
                                                                                                                        
#Else

    'For 32 bit applications.
    Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
                                                        (ByVal hwnd As Long, _
                                                        ByVal lpOperation As String, _
                                                        ByVal lpFile As String, _
                                                        ByVal lpParameters As String, _
                                                        ByVal lpDirectory As String, _
                                                        ByVal nShowCmd As Long) As Long
                                                        
    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
                                                        (ByVal lpClassName As String, _
                                                        ByVal lpWindowName As String) As Long

    Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
                                                        (ByVal hWnd1 As Long, _
                                                        ByVal hWnd2 As Long, _
                                                        ByVal lpClassName As String, _
                                                        ByVal lpWindowName As String) As Long

    Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
                                                        (ByVal hwnd As Long, _
                                                        ByVal wMsg As Long, _
                                                        ByVal wParam As Long, _
                                                        lParam As Any) As Long

    Public Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA" _
                                                        (ByVal hwnd As Long, _
                                                        ByVal wMsg As Long, _
                                                        ByVal wParam As Long, _
                                                        ByVal lParam As Long) As Long
                                                        
#End If

'Constants used in API functions.
Public Const SW_HIDE As Long = 0
Public Const SW_SHOWNORMAL As Long = 1
Public Const SW_SHOWMAXIMIZED As Long = 3
Public Const SW_SHOWMINIMIZED As Long = 2
Public Const WM_SETTEXT = &HC
Public Const VK_RETURN = &HD
Public Const WM_KEYDOWN = &H100

Public Sub OpenLockedPdf(pdfPath As String, password As String)
    
    '------------------------------------------------------------------------
    'Opens a password-protected PDF file, given its (known) password.
    'API functions are used to find the pop-up window and fill the password.
    '
    'The subroutine can be used in every Office application, as well as
    'in AutoCAD. It works for both 32 and 64 bit applications.
    
    'The macro also works with PDF files that are NOT password-protected.
    'In that case, the code after the line of ShellExecute is ignored.
    
    'Written By:    Christos Samaras
    'Date:          30/04/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '------------------------------------------------------------------------
           
    'Declaring the necessary variables (different for 32 or 64 bit applications).
    #If VBA7 And Win64 Then
        Dim parentWindow            As LongPtr
        Dim firstChildWindow        As LongPtr
        Dim secondChildFirstWindow  As LongPtr
    #Else
        Dim parentWindow            As Long
        Dim firstChildWindow        As Long
        Dim secondChildFirstWindow  As Long
    #End If
    Dim timeCount                   As Date

    'Check if the PDF file exists.
    If FileExists(pdfPath) = False Then
        MsgBox "The PDF file doesn't exist!", vbCritical, "Error in PDF path"
        Exit Sub
    End If
        
    'The ShellExecute API will try to open the PDF file using the default application that
    'is associated with PDF files (either Adobe Reader or Professional).
    ShellExecute Application.hwnd, "Open", pdfPath, vbNullString, "C:\", SW_SHOWNORMAL
             
    'Note: The code below will be ignored if the PDF file has no protection.
    
    'Find the handle of the pop-up window.
    timeCount = Now()
    Do Until Now() > timeCount + TimeValue("00:00:05")
        parentWindow = 0
        DoEvents
        parentWindow = FindWindow("#32770", "Password")
        If parentWindow <> 0 Then Exit Do
    Loop
    
    If parentWindow <> 0 Then
    
        'Find the handle of the first child window (it is a group box).
        timeCount = Now()
        Do Until Now() > timeCount + TimeValue("00:00:05")
            firstChildWindow = 0
            DoEvents
            firstChildWindow = FindWindowEx(parentWindow, ByVal 0&, "GroupBox", vbNullString)
            If firstChildWindow <> 0 Then Exit Do
        Loop

        'Find the handle of the subsequent child window (it is the text box for filling the password).
        If firstChildWindow <> 0 Then
            timeCount = Now()
            Do Until Now() > timeCount + TimeValue("00:00:05")
                secondChildFirstWindow = 0
                DoEvents
                secondChildFirstWindow = FindWindowEx(firstChildWindow, ByVal 0&, "RICHEDIT50W", vbNullString)
                If secondChildFirstWindow <> 0 Then Exit Do
            Loop
            
            'The handle was found, so...
            If secondChildFirstWindow <> 0 Then
            
                'Fill the password in the text box.
                SendMessage secondChildFirstWindow, WM_SETTEXT, 0&, ByVal password
                
                'Press the OK button (it is the default action, so no need to find the handle of the button).
                PostMessage secondChildFirstWindow, WM_KEYDOWN, VK_RETURN, 0

            End If
        
        End If
        
    End If
   
End Sub

Function FileExists(FilePath As String) As Boolean
   
    '--------------------------------------------------
    'Checks if a file exists (using the Dir function).
    '--------------------------------------------------

    On Error Resume Next
    If Len(FilePath) > 0 Then
        If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0
   
End Function

Sub SamplePdfTest()
    
    'Full path example that can be used in every Office application, as well as with AutoCAD:
    'OpenLockedPdf "C:\Users\Christos\Desktop\Locked File.pdf", "Newsletter"
    
    'Relative path example (in Excel):
    'OpenLockedPdf ThisWorkbook.Path & "\" & "Locked File.pdf", "Newsletter"
    
    'For this example only (with the button):
    OpenLockedPdf ThisWorkbook.Sheets("Open Locked Sample File").Range("C3"), ThisWorkbook.Sheets("Open Locked Sample File").Range("C5")
    
End Sub



Demonstration video



The video that follows demonstrates the result of the "OpenLockedPdf" macro, as well  as gives more information on the usage of Spy++.




Downloads



Download

The zip file contains a sample PDF file that is locked with a password, as well as a sample workbook containing the code presented above that can be used to open that file (the password is included). The workbook can be opened with Excel 2007 or newer. Please enable macros before using it. Finally, there is also a VBA module with the code to attach it in any Office or AutoCAD application you might have.



Read also



VBA Macro To Open A PDF File
Open PDF File With VBA

Wednesday, 21 March 2018

Last updated: 29/03/2018, 1 min read (without the code)

Reverse Geocoding Using VBA & Google API


Introduction



After updating the existing VBA functions that dealt with Google APIs, it is time to introduce a new function that can be used for Reverse Geocoding, a short definition of which, based on Wikipedia, is the following: “Reverse geocoding is the process of back (reverse) coding of a point location (latitude, longitude) to a readable address or place name. This permits the identification of nearby street addresses, places, and/or areal subdivisions such as neighbourhoods, county, state, or country”.

The developed VBA function can be utilized directly from Excel, as long as the user provides a valid pair of latitude and longitude. The GetAddress function sends a request to the Google server and, then, uses its XML response to read the appropriate information (the formatted address in particular). If you are curious how the server response looks like, see the picture below, where the main nodes are highlighted in red boxes. This function does the exact opposite of the GetCoordinates function that was written a few years ago.

Reverse Geocoding Response

Apart from latitude and longitude, the function incorporates an optional third parameter (ResultTypeFilter). This parameter acts as a filter, by reducing/filtering the results returned from the server. In the code comments, you will find the values that are supported.



VBA code



Below you will find the VBA code for the GetAddress function. Bear in mind that the use of the Google Geocoding API is subject to a limit of 2500 requests per day, so be careful not to exceed this limit. To use this VBA function you will need a valid API key. Check this link that presents a step-by-step guide on how to acquire one for free.

Option Explicit

Function GetAddress(Latitude As Double, Longitude As Double, Optional ResultTypeFilter As String) As String
    
    '-----------------------------------------------------------------------------------------------------------------------
    'This function returns the address of a given latitude, longitude pair using the Google (Reverse) Geocoding API.
    
    'The optional paramter ResultTypeFilter is a filter of one or more address types, separated by a pipe (|).
    'If the parameter contains multiple address types, the API returns all addresses that match any of the types.
    'A note about processing: The result_type parameter does not restrict the search to the specified address type(s).
    'Rather, the result_type acts as a post-search filter: the API fetches all results for the specified latlng,
    'then discards those results that do not match the specified address type(s).
        
    'The following values are supported:
    'street_address: indicates a precise street address.
    'route: indicates a named route (such as "US 101").
    'intersection: indicates a major intersection, usually of two major roads.
    'political: indicates a political entity. Usually, this type indicates a polygon of some civil administration.
    'country: indicates the national political entity, and is typically the highest order type returned by the Geocoder.
    'administrative_area_level_1: indicates a first-order civil entity below the country level. Within the United States,
        'these administrative levels are states. Not all nations exhibit these administrative levels.
        'In most cases, administrative_area_level_1 short names will closely match ISO 3166-2 subdivisions and other
        'widely circulated lists; however this is not guaranteed as our geocoding results are based on a variety of
        'signals and location data.
    'administrative_area_level_2: indicates a second-order civil entity below the country level. Within the United States,
        'these administrative levels are counties. Not all nations exhibit these administrative levels.
    'administrative_area_level_3: indicates a third-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'administrative_area_level_4: indicates a fourth-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'administrative_area_level_5: indicates a fifth-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'colloquial_area" indicates a commonly-used alternative name for the entity.
    'locality: indicates an incorporated city or town political entity.
    'ward: indicates a specific type of Japanese locality, to facilitate distinction between multiple
        'locality components within a Japanese address.
    'sublocality: indicates a first-order civil entity below a locality. For some locations may receive one of the
        'additional types: sublocality_level_1 to sublocality_level_5. Each sublocality level is a civil entity.
        'Larger numbers indicate a smaller geographic area.
    'neighborhood: indicates a named neighborhood
    'premise: indicates a named location, usually a building or collection of buildings with a common name
    'subpremise: indicates a first-order entity below a named location, usually a singular building within
        'a collection of buildings with a common name
    'postal_code: indicates a postal code as used to address postal mail within the country.
    'natural_feature: indicates a prominent natural feature.
    'airport: indicates an airport.
    'park: indicates a named park.
    'point_of_interest: indicates a named point of interest. Typically, these "POI"s are prominent local entities that
        'don't easily fit in another category, such as "Empire State Building" or "Statue of Liberty."
    
    'The function ignores parameters such as language and location_type and returns always the FIRST RESULT.
    
    'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 2500
    'requests per day, so be careful not to exceed this limit. For more info check:
    'https://developers.google.com/maps/documentation/geocoding/usage-limits
        
    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    
    'Moreover, to use this function you will also need a valid API key.
    'Check the next link that guides you on how to acquire a free API key:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    
    'Written By:    Christos Samaras
    'Date:          17/03/2018
    'Last Updated:  29/03/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          https://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim ApiKey          As String
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim AddressNode     As IXMLDOMNode

    'Set your API key in this variable. Check this link for more info:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    ApiKey = "Your API Key goes here!"
    
    'Check that an API key has been provided.
    If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
        GetAddress = "Invalid API Key"
        Exit Function
    End If
    
    'Check the input variables:
    'The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.
    If Latitude < -90 Or Latitude > 90 Then
        GetAddress = "Invalid Latitude value"
        Exit Function
    End If

    'Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
    If Longitude < -180 Or Longitude > 180 Then
        GetAddress = "Invalid Longitude value"
        Exit Function
    End If
      
    'Generic error handling.
    On Error GoTo errorHandler
    
    'Create the request based on Google's (Reverse) Geocoding API. Parameters:
    '- latlng: The latitude and longitude values specifying the location for which you wish to obtain the closest, human-readable address.
    '- key: Your application's API key. This key identifies your application for purposes of quota management.
    'Differentiate the request if a filter is provided.
    If ResultTypeFilter = vbNullString Then
        Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
                            & "latlng=" & Latitude & "," & Longitude & "&key=" & ApiKey, False
    Else
        Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
                            & "latlng=" & Latitude & "," & Longitude & "&result_type=" & ResultTypeFilter & "&key=" & ApiKey, False
    End If
    'Send the request to the Google server.
    Request.send
    
    'Read the results from the request.
    Results.LoadXML Request.responseText
    
    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")
    
    'Based on the status node result, proceed accordingly.
    Select Case UCase(StatusNode.Text)
    
        Case "OK"   'The API request was successful. At least one result was returned.
        
            'Get the formatted address of the first result.
            Set AddressNode = Results.SelectSingleNode("//result/formatted_address")
            
            'Return the address.
            GetAddress = AddressNode.Text
                    
        Case "ZERO_RESULTS"   'The geocode was successful but returned no results.
            GetAddress = "The address probably not exists"
            
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
            GetAddress = "Requestor has exceeded the server limit"
            
        Case "REQUEST_DENIED"   'The API did not complete the request.
            GetAddress = "Server denied the request"
            
        Case "INVALID_REQUEST"  'The API request is empty or is malformed.
            GetAddress = "Request was empty or malformed"
        
        Case "UNKNOWN_ERROR"    'Indicates that the request could not be processed due to a server error.
            GetAddress = "Unknown error"
        
        Case Else   'Just in case...
            GetAddress = "Error"
        
    End Select
        
    'Release the objects.
errorHandler:
    Set AddressNode = Nothing
    Set StatusNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    
End Function

NOTE: In case of multiple results, the function returns the first result, so be careful with your inputs. Tip: use the ResultTypeFilter parameter to reduce the results that are returned. For anyone who is interested to learn how the Google (Reverse) Geocoding API works, he/she can visit the corresponding page.



Downloads



Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.



Read also



How To Get A Free Google API Key
Geocoding Using VBA & Google API 
Custom Trip Distance Function (VBA & Google Directions API)
Custom Elevation Function (VBA & Google API)

Saturday, 24 February 2018

Last updated: 24/02/2018, 2 min read

How To Get A Free Google API Key


Introduction



During the previous years, I published several VBA functions that used Google web services, such as Geocoding, Elevation, and Directions. These functions worked in the same way: a properly formatted request was sent to Google server, and, then, a response was received in XML format. If the response was successful, the requested field was retrieved (latitude, longitude, elevation, trip distance etc.) by reading the XML file. These VBA functions working fine for several years, however, Google started to not allow the requests over HTTP protocol anymore, so these functions have become useless. This is the reason why I decided to rewrite all of these VBA functions while guiding you on how to get a free Google API Key that is now required.



Steps to get a valid API key



The tutorial below can be used for ANY Google API. As an example, I use the Geocoding API. The only prerequisite for this process is to just have a Google account.

1.    First of all, log in to your Google Account.

Logged In With Gmail Account

2.    Search for the appropriate API by using something like “Google API Name API Key”. The first result will probably be the one you need to click.

Search For API Key

3.    On the API page, click on the Get A key button.

Navigate To Google API Page

4.    You need to select the Yes option on the radio button. If you need to change the default project name, then click on “My Project”.
Enable API
5.    Select the Create a new project option to name your new project.
Create A New Project
6.    Rename the project and press the Next button.
New Project
7.    After waiting for a few seconds, your API key will pop up. You can simply copy it and then press the Done button. However, I suggest you click on the API console hyperlink.
Get The API Key
8.    On the page that opens on a new browser tab you can rename your API key, but more importantly, you can restrict its access (if you need to do it). Furthermore, you can regenerate or even delete the key if you no longer need it.

Console Options

9.    If you click on the Dashboard option on the top left of the page, you can see useful information for the particular project, such as the requests, the errors per hour etc. Note that you can use multiple APIs on the same project. In this example, I use only the Geocoding API on my test project.

Console Dashboard

10.    Finally, the previous step is useful (Dashboard) because each API has some limits for free usage. You can check these limits on the corresponding page and make sure you don’t exceed them!

API Usage Limits


Epilogue



If you followed these easy steps, you will get a valid API key that you can use it on your projects. To save you some time, here are the links to the API key pages that I have used in the VBA functions presented in this blog:

Sunday, 18 February 2018

Last updated: 19/02/2018, 2 min read (without the code)

Get Image Size In Pixels With VBA


Introduction



People who are interested in digital photography taking seriously the size of the images that they take. The dimensions of an image – width and height – can be easily spotted in the Windows Explorer. If you hover your mouse over an image file you will see a pop window showing its dimensions.

Get Image Size By Hovering

Alternatively, you can right-click on an image file, select properties on the menu that pop-ups and then, in the Properties form, go to the Details tab. Close to the end, there is the image section where the dimensions, the width and height of the image is shown (in pixels).

Get Image Size Using File Properties

But, is there a way to get programmatically the size of an image with VBA? The answer is YES, and, unlike a previous post, does NOT involve Photoshop!



VBA code



The GetImageSize function uses the Microsoft Windows Image Acquisition Library v2.0 in order to retrieve the necessary information from an image file. The function returns an array of integers that hold the image width and height in pixels. The FileExists and the IsValidImageFormat functions check if a file exists and if a given path corresponds to a valid image file format respectively.

Option Explicit

Function GetImageSize(ImagePath As String) As Variant
    
    '--------------------------------------------------------------------------------------
    'Returns an array of integers that hold the image width and height in pixels.
    'The first element of the array corresponds to the width and the second to the height.
    
    'The function uses the Microsoft Windows Image Acquisition Library v2.0, which can be
    'found in the path: C:\Windows\System32\wiaaut.dll
    'However, the code is written in late binding, so no reference is required.
            
    'Written By:    Christos Samaras
    'Date:          18/02/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '--------------------------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim imgSize(1)  As Integer
    Dim wia         As Object
    
    'Check that the image file exists.
    If FileExists(ImagePath) = False Then Exit Function
    
    'Check that the image file corresponds to an image format.
    If IsValidImageFormat(ImagePath) = False Then Exit Function
    
    'Create the ImageFile object and check if it exists.
    On Error Resume Next
    Set wia = CreateObject("WIA.ImageFile")
    If wia Is Nothing Then Exit Function
    On Error GoTo 0
    
    'Load the ImageFile object with the specified File.
    wia.LoadFile ImagePath
    
    'Get the necessary properties.
    imgSize(0) = wia.Width
    imgSize(1) = wia.Height
    
    'Release the ImageFile object.
    Set wia = Nothing
    
    'Return the array.
    GetImageSize = imgSize

End Function

Function FileExists(FilePath As String) As Boolean
   
    '--------------------------------------------------
    'Checks if a file exists (using the Dir function).
    '--------------------------------------------------

    On Error Resume Next
    If Len(FilePath) > 0 Then
        If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0
   
End Function

Function IsValidImageFormat(FilePath As String) As Boolean
   
    '----------------------------------------------
    'Checks if a given path is a valid image file.
    '----------------------------------------------
    
    'Declaring the necessary variables.
    Dim imageFormats    As Variant
    Dim i               As Integer
                
    'Some common image extentions.
    imageFormats = Array(".bmp", ".jpg", ".gif", ".tif", ".png")
                    
    'Loop through all the extentions and check if the path contains one of them.
    For i = LBound(imageFormats) To UBound(imageFormats)
        'If the file path contains the extension return true.
        If InStr(1, UCase(FilePath), UCase(imageFormats(i)), vbTextCompare) > 0 Then
            IsValidImageFormat = True
            Exit Function
        End If
    Next i
   
End Function



Sample workbook



The sample workbook contains two sheets: the first one (Function Examples) presents several ways of using the GetImageSize function directly from the worksheet. The examples involve the CELL, TRANSPOSE and the INDEX function.

Get Image Size Function Examples

The second sheet (Batch Mode) can be used to get the size information of up to 100 different images. By inserting the image paths in column C, the width and height are automatically shown in columns D and E. If you need an easy way to retrieve the file paths form a folder, use this free tool.

Get Image Size In Batch Mode



Downloads



Download

The zip file contains a workbook with the two worksheets that presented above, the VBA code and a sample image to play with it. The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.

Monday, 15 January 2018

Last updated: 15/01/2018, 1 min read (without the tables)

Pipe Friction Factor - Online Calculator


Introduction



After a long time, I am returning to a subject that has bothered me many times in the past: friction factor. This time, however, I tried to switch my typical tools, so instead of an Excel/VBA solution, you will get a free online tool. The tool calculates the pipe friction factor based on Churchill's equation. If you have read any of my previous posts, you will probably remember that Churchill's equation is valid for the entire Reynolds range (e.g. any flow type). The equation involves the calculation of two intermediate values that are used in the final equation.

Churchill Equations



Inputs

Roughness of the pipe material [μm]


Internal Diameter of the (circular) pipe [mm]


Discharge [m³/h]


Kinematic Viscosity of the fluid flowing through the pipe [cSt = 10−6 m²/s]





Results

Fluid Velocity [m/s]


Reynolds [-]


Type of flow [-]


Friction Factor [-]







Table 1: Suggested roughness values for several pipe materials.

Pipe MaterialRoughness (μm)
Aluminum1.5
Asbestos cement25
Cast iron (asphalted)122
Cast iron (uncoated)254
Concrete (steel forms, with smooth joints)180
Drawn brass1.5
Drawn copper1.5
Drawn tubing - glass, brass, plastic1.5
Fiberglass5
Fiberglass with epoxy3
Galvanized iron152
Polyethylene3
PVC1.5
Seamless commercial steel (galvanised)150
Seamless commercial steel (light rust)250
Seamless commercial steel (new)25
Stainless steel45
Welded steel600
Wood stave600
Wrought iron45


Table 2: Suggested kinematic viscosity values for various liquids.

LiquidTemperature (C)Kinematic Viscosity (cSt)
Acetaldehyde CH3CHO16.10.305
200.295
Acetic acid - vinegar - 10% CH3COOH151.35
Acetic acid - 50%152.27
Acetic acid - 80%152.85
Acetic acid - concentrated glacial151.34
Acetic acid anhydride (CH3COO)2O150.88
Acetone CH3COCH3200.41
Alcohol - allyl201.6
Alcohol - butyl-n203.64
Alcohol - ethyl (grain) C2H5OH201.52
37.81.2
Alcohol - methyl (wood) CH3OH150.74
01.04
Alcohol - propyl202.8
501.4
Aluminum sulfate - 36% solution201.41
Ammonia-17.80.3
Aniline204.37
106.4
Asphalt RC-0, MC-0, SC-025159-324
37.860-108
Automatic crankcase oil SAE 10W-17.81295-max
Automatic crankcase oil SAE 10W-17.81295-2590
Automatic crankcase oil SAE 20W-17.82590-10350
Automatic crankcase oil SAE 2098.95.7-9.6
Automatic crankcase oil SAE 3098.99.6-12.9
Automatic crankcase oil SAE 4098.912.9-16.8
Automatic crankcase oil SAE 5098.916.8-22.7
Automotive gear oil SAE 75W98.94.2 min
Automotive gear oil SAE 80W98.97.0 min
Automotive gear oil SAE 85W98.911.0 min
Automotive gear oil SAE 90W98.914-25
Automotive gear oil SAE 14098.925-43
Automotive gear oil SAE15098.943 - min
Beer201.8
Benzene (Benzol) C6H601
200.74
Bone oil54.447.5
10011.6
Bromine200.34
Butane-n-1.10.52
0.35
Butyric acid n201.61
Calcium chloride 5%18.31.156
Calcium chloride 25%15.64
Carbolic acid (phenol)18.311.83
Carbon tetrachloride CCl4200.612
37.80.53
Carbon disulfide CS200.33
200.298
Castor oil37.8259-325
54.498-130
China wood oil20.6308.5
37.8125.5
Chloroform200.38
600.35
Coconut oil37.829.8-31.6
54.414.7-15.7
Cod oil (fish oil)37.832.1
54.419.4
Corn oil54.428.7
1008.6
Corn starch solution, 22 Baumé21.132.1
37.827.5
Corn starch solution, 24 Baumé21.1129.8
37.895.2
Corn starch solution, 25 Baumé21.1303
37.8173.2
Cotton seed oil37.837.9
54.420.6
Crude oil 48o API15.63.8
54.41.6
Crude oil 40o API15.69.7
54.43.5
Crude oil 35.6o API15.617.8
54.44.9
Crude oil 32.6o API15.623.2
54.47.1
Decane-n17.82.36
37.81.001
Diethyl glycol21.132
Diethyl ether200.32
Diesel fuel 2D37.82-Ιουν
54.41.-3.97
Diesel fuel 3D37.86-11.75
54.43.97-6.78
Diesel fuel 4D37.829.8 max
54.413.1 max
Diesel fuel 5D5086.6 max
71.135.2 max
Ethyl acetate CH3COOC2H3150.4
200.49
Ethyl bromide C2H5Br200.27
Ethylene bromide200.787
Ethylene chloride200.668
Ethylene glycol21.117.8
Formic acid 10%201.04
Formic acid 50%201.2
Formic acid 80%201.4
Formic acid concentrated201.48
Trichlorofluoromethane, R-1121.10.21
Dichlorodifluoromethane, R-1221.10.27
FDichloro-fluoromethane, R-2121.11.45
Furfurol201.45
Fuel oil 121.12.39-4.28
37.8-2.69
Fuel oil 221.13.0-7.4
37.82.11-4.28
Fuel oil 321.12.69-5.84
37.82.06-3.97
Fuel oil 5A21.17.4-26.4
37.84.91-13.7
Fuel oil 5B21.126.4-
37.813.6-67.1
Fuel oil 65097.4-660
71.137.5-172
Gas oils21.113.9
37.87.4
Gasoline a15.60.88
37.80.71
Gasoline b15.60.64
37.8
Gasoline c15.60.46
37.80.4
Glycerine 100%20.3648
37.8176
Glycerine 50% water205.29
Glycol52
Glucose37.87.7M-22M
65.6880-2420
Heptanes-n-17.80.928
37.80.511
Hexane-n-17.80.683
37.80.401
Honey37.873.6
Hydrochloric acid1.9
Ink, printers37.8550-2200
54.4238-660
Insulating oil21.124.1 max
37.811.75 max
Kerosene202.71
Jet Fuel-34.47.9
Lard37.862.1
54.434.3
Lard oil37.841-47.5
54.423.4-27.1
Linseed oil37.830.5
54.418.94
Mercury21.10.118
37.80.11
Methyl acetate200.44
Methyl iodide200.213
Menhaden oil37.829.8
54.418.2
Milk201.13
Molasses A, first37.8281-5070
54.4151-1760
Molasses B, second37.81410-13200
54.4660-3300
Molasses C, blackstrap37.82630-5500
54.41320-16500
Naphthalene800.9
Neatstool oil37.849.7
54.427.5
Nitrobenzene201.67
Nonane-n-17.81.728
37.80.807
Octane-n-17.81.266
37.80.645
Olive oil37.843.2
54.424.1
Palms oil37.847.8
54.426.4
Peanut oil37.842
54.423.4
Pentane-n17.80.508
26.70.342
Petrolatum54.420.5
71.115
Petroleum ether15.631(est)
Phenol, carbolic acid11.7
201.13
Propylene glycol21.152
Quenching oil100-120
(typical)
Rapeseed oil37.854.1
54.431
Rosin oil37.8324.7
54.4129.9
Rosin (wood)37.8216-11M
93.3108-4400
Sesame seed oil37.839.6
54.423
Silicate of soda79
Sodium chloride 5%201.097
Sodium chloride 25%15.62.4
Sodium hydroxide (caustic soda) 20%18.34
Sodium hydroxide (caustic soda) 30%18.310
Sodium hydroxide (caustic soda) 40%18.3
Soya bean oil37.835.4
54.419.64
Sperm oil37.521-23
54.415.2
Sulphuric acid 100%2014.56
Sulphuric acid 95%2014.5
Sulphuric acid 60%204.4
Sulphuric acid 20%
Tar, coke oven21.1600-1760
37.8141-308
Tar, gas house21.13300-66M
37.8440-4400
Tar, pine37.8559
55.6108.2
Toluene200.68
Triethylene glycol21.140
Turpentine37.886.5-95.2
54.439.9-44.3
Varnish, spar20313
37.8143
Water, distilled201.0038
Water, fresh15.61.13
54.40.55
Water, sea1.15
Whale oil37.835-39.6
54.419.9-23.4
Xylene-o200.93



Read also


Sunday, 5 November 2017

Last updated: 06/11/2017, 1 min read (without the code)

Photoshop VBA


Introduction



Adobe Photoshop is probably the most famous raster graphics editor that is available on the market for almost three decades. You can do amazing things with this software. I have only played with it for a few hours, and I was quite surprised with the results. One of the things that intrigued me the most was the ability to program it. So, in this post I will take the opportunity to show you how to:

  • Resize an image.
  • Modify an image (apply filters).
  • Convert an image.

Here I will only scratch the surface on the topic of Photoshop VBA programming. However, I hope that it can give you some insights on what can be achieved.



VBA code



The code was written in early binding, just to allow the user to investigate the Photoshop's object model and the available options.

Option Explicit

Sub PlayingWithPhotoshop()

    '-----------------------------------------------------------------------------------------------------------------
    'The macro demonstrates how to resize, modify (apply filters) and convert an image in Adobe Photoshop using VBA.
    
    'The macro requires the Adobe Photoshop library in order to work.
    'Go to Tools -> References -> Adobe Photoshop CSx Object Library, where x depends on your
    'Adobe Photoshop version (i.e. 6 or 5) you have installed on your PC.
        
    'Written By:    Christos Samaras
    'Date:          04/11/2017
    'E-mail:        xristos.samaras@gmail.com
    'Site:          http://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------------------
            
    'Declaring the necessary variables.
    Dim InputImagePath      As String
    Dim OutputImagePath     As String
    Dim OutputWidthInPixels As Integer
    Dim PsApp               As Photoshop.Application
    Dim PsDoc               As Photoshop.Document
    Dim PsSaveOptions       As Photoshop.PNGSaveOptions
    
    'Set the necessary input variables.
    'The input image that will be modified. You can give the full path, e.g.:
    'InputImagePath = "C:\Users\Christos\Desktop\Input.jpg"
    InputImagePath = ThisWorkbook.Path & "\" & "Input.jpg"
    
    'The output image that will be created. Here it will be a png image. You can give the full path as well, e.g.:
    'OutputImagePath = "C:\Users\Christos\Desktop\Output.png"
    OutputImagePath = ThisWorkbook.Path & "\" & "Output.png"
    
    'The required width of the output image.
    OutputWidthInPixels = 750
    
    'Check if the input image path is valid.
    If FileExists(InputImagePath) = False Then
        MsgBox "The path of the input image is invalid!", vbCritical, "Input Image Path Error"
        Exit Sub
    End If
    
    'Create a new instance of Photoshop application and make it visible.
    On Error Resume Next
    Set PsApp = New Photoshop.Application
    If PsApp Is Nothing Then
        MsgBox "Sorry, it was impossible to start Photoshop!", vbCritical, "Photoshop Application Error"
        Exit Sub
    End If
    PsApp.Visible = True
     
    'Try to open the input image.
    Set PsDoc = PsApp.Open(InputImagePath)
    If PsDoc Is Nothing Then
        MsgBox "Sorry, it was impossible to open the input image!", vbCritical, "Image Opening Error"
        Exit Sub
    End If
    On Error GoTo 0
    
    'Print the image dimensions before resizing (in the Immediate window of the VBA editor).
    Debug.Print "Before: Width (px): " & PsDoc.Width * PsDoc.Resolution / 2.54 & " Height (px): " & PsDoc.Height * PsDoc.Resolution / 2.54
    
    'Resize the image.
    PsDoc.ResizeImage 2.54 * OutputWidthInPixels / PsDoc.Resolution
    
    'Print the image dimensions after resizing.
    Debug.Print "After: Width (px): " & PsDoc.Width * PsDoc.Resolution / 2.54 & " Height (px): " & PsDoc.Height * PsDoc.Resolution / 2.54
    
    'Apply the Sharpen filter.
    PsDoc.ArtLayers(1).ApplySharpen
    
    'Apply the Gaussian Blur filter within the specified radius (in pixels). Valid range: 0.1 - 250.0.
    PsDoc.ArtLayers(1).ApplyGaussianBlur 5
    
    'Create a new PNGSaveOptions object that will store the necessary saving parameters.
    Set PsSaveOptions = New Photoshop.PNGSaveOptions
    
    'Set the compression of the image. Valid range: 0 - 9, default: 0.
    PsSaveOptions.Compression = 5
    
    'Set the Interlaced option, which indicates whether the rows should interlace. Default value: false.
    PsSaveOptions.Interlaced = True
    
    'Save the modified image using the defined saving options.
    PsDoc.SaveAs OutputImagePath, PsSaveOptions, True, PsExtensionType.psLowercase 'PsExtensionType.psLowercase = 2 in late binding
        
    'Close the image.
    PsDoc.Close psDoNotSaveChanges 'psDoNotSaveChanges = 2 in late binding
    
    'Quit Photoshop.
    PsApp.Quit
    
    'Release the objects.
    Set PsSaveOptions = Nothing
    Set PsDoc = Nothing
    Set PsApp = Nothing
    
    'Inform the user about the process.
    If FileExists(OutputImagePath) = True Then
        MsgBox "The output image was successfully created!", vbInformation, "Finished"
    Else
        MsgBox "The output image was not created!", vbCritical, "Output Image Error"
    End If

End Sub

Function FileExists(FilePath As String) As Boolean
   
    '--------------------------------------------------
    'Checks if a file exists (using the Dir function).
    '--------------------------------------------------

    On Error Resume Next
    If Len(FilePath) > 0 Then
        If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
    End If
    On Error GoTo 0
   
End Function



Example



The picture below is the input image in this example:

Input Image - Before Processing

Input image: 4760 x 3120 pixels, jpg format. Click the image to view it in real size.

And here is the output image, after running the code:

Output Image - After Processing

Output image: 750 x 563 pixels, png format, with Sharpen and Gaussian Blur filter.

The result is neither fancy nor impressive (this wasn't my intent). However, it just shows some possible applications of VBA programming in respect to Photoshop.



Downloads



Download

The zip file contains a workbook with the VBA code and a sample image to play with it (the one that used in this example). The workbook can be opened with Excel 2007 or newer. Please enable macros before using it.