Excel VBA Clipboard issue

[jholdcraft]

Well, I'm new to imagemagick. I just installed it last week and have been very impressed so far with how well it integrates with various programming platforms and the functionality of it. I mostly work with excel VBA. My latest project uses excel to organize photos and create a KMZ file of photooverlays.
In my kmz file folder structure, I have 7 png files that relate to folders and overlays common to every project.
I want to take the 7 png files used in the kmz file and embed them into an excel sheet and copy them into the kmz file structure when the routine is run to create the kmz file rather than copy them from some arbitrary folder on my c drive.

If I where to do it manually I would right click the shape/image and copy it to the clipboard. Open an image software and paste it and save it in my file structure. But obviously I don’t want to do it manually. I want to do it programmatically and keep my transparency. My vba code has two ways to copy the shape.

Option 1 is to use the Copy method
Sheets("External Data").Shapes("Pole_Mark").Copy

Option 2 is to use the CopyPicture method
Sheets("External Data").Shapes("Pole_Mark").CopyPicture xlScreen, xlPicture

If I run my code and stop after that copy line either 1 or 2 and open an image editor and paste the clipboard
Option 1 does not maintain the transparency but looks normal

Option 2 does what I want it to do: Looks normal and maintains transparency.

If I run my code and paste the clipboard using imagemagick VBA command
C = objIMG.Convert("clipboard:", "-background", "transparent", "-gravity", "center", stKMLFiles & "\Pole_Mark" & ".png")
Option 1 runs the code but does not maintain the transparency and causes a pixel shift where 3 or 4 columns of pixels on the right are shifted to the left.

Option 2 gives me a run-time error. '-2147215503 (80041771)': Automation error.

At this point I’m pretty sure option 2 is the method I need to use to copy my image. I just need a way to save it now.

Any ideas? I think Imagemagick should work. I'm just too new to it to know if I'm doing something wrong or not.

I did find this though - https://stackoverflow.com/questions/260 ... t-in-excel

Thanks

[snibgo]

I don't use VBA.

ImageMagick can copy an image to the clipboard, if that helps. Just run a command with the output going to "clipboard:".

[jholdcraft]

Interesting thought. How do I copy it from my excel sheet with imagemagick?
C = objIMG.Convert(Sheets("External Data").Shapes("Pole_Mark"), "-background", "transparent", "-gravity", "center", "clipboard:")
I tried this and got the same run-time error.

[jholdcraft]

I am still in need of a solution. Can anyone from the imagemagick team tell me what the run-time error is?

[snibgo]

Option 2 gives me a run-time error. '-2147215503 (80041771)': Automation error.

This message does not come from ImageMagick.

[jholdcraft]

But what does the error mean? Why is imagemagick not working with the clipboard correctly in this environment?

[GeeMack]

But what does the error mean? Why is imagemagick not working with the clipboard correctly in this environment?

Just for consideration with Windows clipboard issues, and most likely unrelated to this, but there is a problem with the way current versions of IM handle the Windows clipboard with gray colorspace images. The result of copying a grayscale image to the clipboard, then pasting it and saving it to another file, causes the resulting image to be rolled horizontally some number of pixels. I have no idea if this problem extends further than this symptom.

[whugemann]

Your code results in different data formats stored in the clipboard. You can see this within Excel if you choose 'Paste special':

Code: Select all

Sheets("External Data").Shapes("Pole_Mark").Copy

results in several formats beeing copied to the clipboard, the topmost being a raster image.

Code: Select all

Sheets("External Data").Shapes("Pole_Mark").CopyPicture xlScreen, xlPicture

Results in only a Windows Meta File being stored in the clipboard.

Anyway, when testing such things, you should split up the entire task:
Converting the image stored in the clipboard can be tested with IM's command line tools, i.e. convert or magick (depending on version 6 or seven). When you have got that running, you can simply call the shell with this command from Excel VBA:

Code: Select all

strcmd = "convert " & ...
Shell(strcmd)

You don't even have to use the COM interface.

[jholdcraft]

@whugemann, thank you for the help understanding the data difference. I think that explains much of the problem. I am still struggling though. I've been trying to figure out what a windows mefafile is. I've never heard of them before but it appears to be just a container for an image file more or less in pseudo vector format. I've tried converting it in VBA to a PNG. When I check the shape.type, it comes back as 'msoPicture'. I've tried looking through imagemagick to see how they handle .wmf files. I'm not making any forward progress. I don't understand why I can take the Windows metafile data and paste it into Paint.Net just fine but am not able to paste it into imagemagick. With the convert command is there another way to save the wmf file that I am missing? Thanks everyone for the help so far, I really appreciate it.

[whugemann]

I've just tested that IM has no problems converting WMF into something else – well at least pure vector WMF. It however doesn't convert WMFs from the clipboard. When you address the clipboard, IM obviously expects a raster image.

An msoPicture is a Microsoft Office specific data format that IM cannot handle at all, I guess.

As a first step you should make sure that your VBA code copies a raster image to the topmost position of the clipboard. You don't have to identify the format via VBA, just look into the clipboard via 'paste special'.

As I said: Split up the task into small peaces, in order to locate the source of the error.

[jholdcraft]

Thanks again for your input. I may look into this further later, but for now I found a work around that works really well. It is an adaptation from code found here. https://www.tapatalk.com/groups/xlvbafr ... guN7ZKyAqY. For my case I have 7 .png files I saved on a sheet named "Icon". I named the pictures "Picture_#" 1-7. I also have a blank chart on the sheet that I named "ImgChart". I fixed the chart area to be Filled with <Pick any color> with no outline and fixed the transparency setting to be 100% The following code selects each image and saves them as a .png file and maintains the original image transparency.

Sub ExportMyPicture()
Dim inPic As Integer
Dim PicWidth As Long, PicHeight As Long

For inPic = 1 To 7

With Sheets("Icon").Shapes("Picture_" & inPic)
PicHeight = .Height
PicWidth = .Width
.CopyPicture xlScreen, xlPicture
End With

With Sheets("Icon").Shapes("ImgChart")
.Width = PicWidth
.Height = PicHeight
.Chart.Paste
.Chart.Export Filename:="c:\temp\MyPic" & inPic & ".png", FilterName:="png"
End With

Selection.Cut

Next

End Sub

Doesn't really relate to Imagemagick anymore but thought people might be interested