Using Blobs from Excel VBA

[markt]

I’ve had some limited success to get ImageMagickObject to work with Blobs from Excel VBA.

As I could find some posts on the internet that others have been having problems with this, but none with working solutions I thought I would share the info.

First I created a simple test script, based on the ArrayTest.vbs sample.
TestImageMagic.vbs:-

Code: Select all

Dim objIM
Dim myimage(1)

Set objIM = CreateObject("ImageMagickObject.MagickImage.1")

MsgBox objIM.Convert("rose:", "myimage.jpg")

myimage(0) = "JPEG:"
MsgBox objIM.Convert("logo:", myimage)

MsgBox objIM.Convert(myimage, "myimage2.jpg")

This creates myimage.jpg with the rose image, but then crashes with a windows script host error, "Catastrophic failure, 8000FFFF". (I’m testing on Windows 8.1 64 bit, so this is running via the x64 version of wscript.)

Then using TestImageMagicx64.bat file to test with wscript or cscript:-

Code: Select all

@echo
C:
\Windows\System32\cscript.exe C:\Users\Mark\Desktop\TestImageMagic.vbs
Pause

Shows the same wscript error as above and similar "Catastrophic failure" error for cscript.

Using similar TestImageMagic.bat to test the 32 bit version of ImageMagickObject via 32 bit version of wscript and cscript:-

Code: Select all

@echo
C:
\Windows\SysWOW64\cscript.exe C:\Users\Mark\Desktop\TestImageMagic.vbs
Pause

This 32 bit version seems to work correctly and creates both myimage.jpg and myimage2.jpg.
(Make sure that the first ImageMagick path in the windows environment variable path is pointing to the 32 bit version, as the path environment variable is used to find the object dll.)

I’ll leave the problem with 64 bit version of ImageMagickObject for now, as I plan to use ImageMagickObject from Excel 2010, 32 bit version, and as posted earlier by spieler, this will only work with the 32 bit version of ImageMagickObject.

I created an Excel VBA test similar to the above script in a standard Excel VBA module:-

Code: Select all

Option Explicit

Sub TestImageMagic()

    Dim objIM      '  As ImageMagickObject.MagickImage    '   Causes error
    Dim myimage(1)
    
    Set objIM = CreateObject("ImageMagickObject.MagickImage.1")
    
    MsgBox objIM.Convert("rose:", " C:\Users\Mark\Desktop\ myimage.jpg")
    
    myimage(0) = "JPEG:"
    MsgBox objIM.Convert("logo:", myimage)
    
    MsgBox objIM.Convert(myimage, " C:\Users\Mark\Desktop\ myimage2.jpg")
End Sub

This throws a run time error, "80041771 - convert: 410: no images defined", at the final convert to create myimage2.jpg.

Using the VBA debug and watch window shows that although there is no error generated when creating the blob, the array myimage now only has two empty variants. Prior to this it showed myimage(0)="JPEG:" and myimage(1)=Empty.

In order to see what was going wrong, I installed ImageMagick from source and compiled for Win32 in debug. This caused some additional problem with an exception thrown, but by using retry and continue I was able to get to the point where the convert function was completed and the output array appeared to correctly hold the blob, but on return to VBA the array was empty again as above.

This seems to show that excel is not handling the return by reference to the myimage array, which is probably to be expected when declared as a static array using "Dim myimage(1)", as this would also not be expected to work in a call by reference to a VBA function or sub.

Although this seems to be OK for VBscript it is not handled by VBA.

To avoid this problem I made a copy of the array in a variant and then pass the variant to ImageMagickObject:-

Code: Select all

Option Explicit

Sub TestImageMagic()

    Dim objIM
    Dim myimage(1)
    Dim myarray As Variant
    
    Set objIM = CreateObject("ImageMagickObject.MagickImage.1")
    
    MsgBox objIM.Convert("rose:", "C:\Users\Mark\Desktop\myimage.jpg")
    
    myimage(0) = "JPEG:"
    myarray = myimage
    MsgBox objIM.Convert("logo:", myarray)
    
    MsgBox objIM.Convert(myarray, "C:\Users\Mark\Desktop\myimage2.jpg")
End Sub

Then using the watch window again, before calling convert to create the blob, myarray seems to look the same as myimage.

After calling convert, myarray changed from an array of two variants to a byte array containing the blob.

It also no longer includes “JPEG:”, but it seems this is not required in order to use the blob to create “myimage2.jpg”.

A simpler version which still seems to work correctly in Excel VBA:-

Code: Select all

Option Explicit

Sub TestImageMagic()

    Dim objIM As Object        '  As ImageMagickObject.MagickImage    '   Causes error
    Dim myimage As Variant
    
    Set objIM = CreateObject("ImageMagickObject.MagickImage.1")
    
    MsgBox objIM.Convert("rose:", "C:\Users\Mark\Desktop\myimage.jpg")
    
    myimage = Array("JPEG:")
    MsgBox objIM.Convert("logo:", myimage)
    
    MsgBox objIM.Convert(myimage, "C:\Users\Mark\Desktop\myimage2.jpg")
End Sub

Or from VBscript, (using 32 bit wscript or cscript):-

Code: Select all

Dim objIM

Set objIM = CreateObject("ImageMagickObject.MagickImage.1")

msgbox objIM.Convert("rose:", "myimage.jpg")

myimage = Array("JPEG:")
msgbox objIM.Convert("logo:", myimage)

msgbox objIM.Convert(myimage, "myimage2.jpg")

Additional points to remember:-
1). The variant used to return the blob needs to be a variant array, even if it only has one element, in order for ImageMagickObject to recognize this as a reference to save the blob. It can be created by setting a variant equal to a static array or using the Array function.

2). Use regsvr32 to register the ImageMagickObject.dll. (Use cmd.exe, run as administrator.)

3). Ensure the path environment variable is set to the path of ImageMagickObject.dll, and that if you have both 64 and 32 bit versions installed then the 32 bit version is the earliest included in path. Tip – install the 64 bit version first and then install the 32 bit version last, but you might need to specify the full path to continue using 64 bit convert.exe and other command line utilities.

4). From Excel VBA, Tools, References, make sure "ImageMagickObject 1.0 Type Library" is shown as checked, and make sure this is the 32 bit version for 32 bit versions of Excel.

5). Declare the ImageMagickObject as type Object in Excel VBA and not ImageMagickObject.MagickImage.

Note that 2 & 3 should already be done, by the install from binaries, but included in case you are installing from source.

Remaining questions and issues.
1). I’ve tested with Excel 2010 32 bit version using 32 bit ImageMagickObject on 64 bit Windows 8.1, I can also test with Excel 2007 32 bit on 64 bit Win7 and I’ll add to this post with results. If anyone is successful with other combinations it might be helpful to others to add replies to this post.

2). 64 bit install of ImageMagickObject does not seem to work from VBscript, so suspect it will also not work from 64 bit Excel VBA. If anyone has this working or can confirm it isn’t working then I’d also be interested to hear about it.

3). If I use "Dim objIM As ImageMagickObject.MagickImage" then ImageMagick still throws an error due to the input safearray does not match the expected type, so I suspect there is still some problem with the way Excel VBA is interpreting the object definition or in the way this is declared by ImageMagickObject. I may take another look at this later but it's not stopping me from using blobs in my project.

[markt]

I confirmed the samples using variant array passed to ImageMagickObject are working on Windows 7 Pro 64bit, with Excel 2007 32 bit and ImageMagickObject 32 bit.

I also found it possible to take data from a WIA image file direct to ImageMagickObject instead of creating a temporary file to disk. This worked for both TIFF and JPEG formats, I didn't try other formats yet.

Code: Select all

Dim wiaImg As WIA.ImageFile
Dim IMimage1 As Variant

'	Code to read image file from scanner goes here

IMimage1 = Array("TIFF:")  '   Specify the image type
sArg = objIM.Convert(wiaImg.FileData.BinaryData, "-crop", sArea, "+repage", "-shave", sBorder, _
		"-bordercolor", "white", "-border", sBorder, "-white-threshold", FormatPercent(dWhiteThreshold, 0), IMimage1)

This is probably not going to work from ImageMagickObject blob to a WIA.ImageFile, but still useful.

Unfortunately I'm finding Excel crashes out when I try to use "-deskew", "40%", with the output to a blob, I'll create a new thread for that problem.

[markt]

It turned out to be fairly easy to convert an ImageMagick blob back to a WIA image file object.

Example creates a blob from logo, creates an image file object from the blob, saves it to a file, uses the image file object to input blob to convert, then runs convert on the blob.

Excel VBA:-

Code: Select all

Option Explicit

Sub TestImageMagic()

    Dim objIM As Object
    Dim myimage As Variant
    Dim img As WIA.ImageFile
    Dim v As WIA.Vector
    
    Set objIM = CreateObject("ImageMagickObject.MagickImage.1")
    
    myimage = Array("TIFF:")
    MsgBox objIM.Convert("logo:", myimage)
    
    Set v = New WIA.Vector
    v.BinaryData = myimage
    Set img = v.ImageFile
    Set v = Nothing
    
    img.SaveFile ("C:\Users\Mark\Desktop\myimage.tiff")
    
    MsgBox objIM.Convert(img.FileData.BinaryData, "C:\Users\Mark\Desktop\myimage1.tiff")
    Set img = Nothing
    
    MsgBox objIM.Convert(myimage, "C:\Users\Mark\Desktop\myimage2.tiff")
End Sub

Slightly different from VBscript as this needs to use CreateObject instead of New to create the vector object.

Code: Select all

Dim objIM
Dim myimage
Dim img
Dim v
    
Set objIM = CreateObject("ImageMagickObject.MagickImage.1")
    
myimage = Array("TIFF:")
MsgBox objIM.Convert("logo:", myimage)
    
Set v = CreateObject("WIA.Vector")
v.BinaryData = myimage
Set img = v.ImageFile
Set v = Nothing
    
img.SaveFile ("C:\Users\Mark\Desktop\myimage.tiff")
    
MsgBox objIM.Convert(img.FileData.BinaryData, "C:\Users\Mark\Desktop\myimage1.tiff")
Set img = Nothing
    
MsgBox objIM.Convert(myimage, "C:\Users\Mark\Desktop\myimage2.tiff")