VBA to get a list of image names, dimensions, and ratio

[MarcoRex]

Hi,
we work on Macs, and produce banners and of all sizes and dimensions.
Often times we have to refer to previously done and approves sizes to create new ones that are similar.
At the moment there is no efficient way to search our network servers and create a database of images in the way we need.
I have taken a screenshot of the rough idea I have in mind, and I'd like to know if this is achievable at all, or just a bizzarre idea (I am only a graphic designer and know nothing about VBA or coding).

I'd like to be able to specify a folder on our network, in this case the "Example" folder, and i'd like excel to look for only image files (jpgs, pngs, tiff), and return and organize them in the sheet as per screenshot with their server path, names, dimensions, and "Type" which is based on ratio brackets that would be specified in the code (example if the value of Width/Height is between 1.77 and 1.66 than the image type is classed as "16x9", between 1.95 and 1.77 classed as "Wide 16x9" and so on).

I am sorry i can't offer much in return, but I'd be super appreciative of any idea about this.

Thank you very much to the community.

Marco

 

[Micron]

You can find code that uses msoFileDialogFilePicker if what you want to do is get info about one file. If you want to do a batch, perhaps msoFileDialogFolderPicker or you can loop over a list of files (e.g. in a spreadsheet). For getting the image info, I'd try using one of the methods posted here

VBA - Get Image Properties, Dimensions, Etc | DEVelopers HUT

VBA function to retrieve various image properties (dimensions, extension, resolution) of an image.

www.devhut.net

I tried the first one by calling it as
?WIA_GetImgDimensions("C:\Users\Micron\Pictures\serialnumber.jpg")

but changed this

    Img.PixelDepth = oWIA.PixelDepth
Error_Handler_Exit:

to this

    Img.PixelDepth = oWIA.PixelDepth
    Dim str As String
    str = Img.Width & ", " & Img.Height & ", " & Img.FileExtension & ", " & Img.HorizontalResolution & ", " & Img.VerticalResolution & ", " & Img.PixelDepth
    Debug.Print str
Error_Handler_Exit:

and the printout was this
1280, 720, jpg, 150, 150, 24
Maybe those ideas will get you started. I didn't read the whole article so I'm not seeing the sense in having the function return a Boolean value.

 

[MarcoRex]

You can find code that uses msoFileDialogFilePicker if what you want to do is get info about one file. If you want to do a batch, perhaps msoFileDialogFolderPicker or you can loop over a list of files (e.g. in a spreadsheet). For getting the image info, I'd try using one of the methods posted here

VBA - Get Image Properties, Dimensions, Etc | DEVelopers HUT

VBA function to retrieve various image properties (dimensions, extension, resolution) of an image.

www.devhut.net

I tried the first one by calling it as
?WIA_GetImgDimensions("C:\Users\Micron\Pictures\serialnumber.jpg")

but changed this

    Img.PixelDepth = oWIA.PixelDepth
Error_Handler_Exit:

to this

    Img.PixelDepth = oWIA.PixelDepth
    Dim str As String
    str = Img.Width & ", " & Img.Height & ", " & Img.FileExtension & ", " & Img.HorizontalResolution & ", " & Img.VerticalResolution & ", " & Img.PixelDepth
    Debug.Print str
Error_Handler_Exit:

and the printout was this
1280, 720, jpg, 150, 150, 24
Maybe those ideas will get you started. I didn't read the whole article so I'm not seeing the sense in having the function return a Boolean value.

Thanks for your kind reply,
however i think this flies way above my head :/

 

[Micron]

You can create a module, copy and paste that code and run it (but provide a valid image file address) yes? Then see what happens?
I can't even tell you if WIA works on macbooks but a google search should turn up something about that question. Or you experimenting with that code should answer that. You won't be able to manage this without code, so you might as well start dabbling at some point.

 

[MarcoRex]

You can create a module, copy and paste that code and run it (but provide a valid image file address) yes? Then see what happens?
I can't even tell you if WIA works on macbooks but a google search should turn up something about that question. Or you experimenting with that code should answer that. You won't be able to manage this without code, so you might as well start dabbling at some point.

I am not entirely unfamiliar with code, but VBA+excel is totally alien to me.

So i have tried that, using my test image path

Function WIA_GetImgDimensions(ByVal sFile As String) As Boolean
    'For a complete listing of available WIA ImageFile properties
    '   Ref: https://docs.microsoft.com/en-us/previous-versions/windows/desktop/wiaaut/-wiaaut-imagefile
    On Error GoTo Error_Handler
    Dim oWIA                  As Object

    Set oWIA = CreateObject("WIA.ImageFile")
    oWIA.LoadFile sFile
    img.Width = oWIA.Width
    img.Height = oWIA.Height
    img.FileExtension = oWIA.FileExtension
    img.HorizontalResolution = oWIA.HorizontalResolution
    img.VerticalResolution = oWIA.VerticalResolution
    img.PixelDepth = oWIA.PixelDepth

Error_Handler_Exit:
    On Error Resume Next
    If Not oWIA Is Nothing Then Set oWIA = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: WIA_GetImgDimensions" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function



Private Sub Testme()
    Dim sFile                 As String

    sFile = "/Users/Name/Documents/-Documenti Personali-/Example/Cartella/altraimmagine.png"
    Call WIA_GetImgDimensions(sFile)
    Debug.Print sFile, "Width: " & img.Width, "Height: " & img.Height, _
                "FileExtension: " & img.FileExtension, _
                "HorizontalResolution: " & img.HorizontalResolution, _
                "VerticalResolution: " & img.VerticalResolution, _
                "PixelDepth: " & img.PixelDepth
End Sub

but end up with this error, so i assume this type of solution is a no go on mac

 

[Micron]

I have a feeling that you're missing a code library reference. I've put the code in a new wb because the one I play with for Mr. Excel as a ton of references already set and it's not obvious to me which one you might be missing. It raised an error when I tried to run it so I'll see which one I'm missing and let you know.

 

[Micron]

So don't assume the worst just because you get an error. Turns out that I didn't notice what was at the very top of the code I copied. You should have this at the very top of the code page

Option Explicit

Private Type ImgageInfo
    Height As Long
    Width As Long
    PixelDepth As Long
    FileExtension As String
    HorizontalResolution As Double
    VerticalResolution As Double
End Type

Public Img As ImgageInfo