Exporting Figures from a Worksheet to a File

Ok, after a little bit more research I'm not done but I have a path to think about.

It seems to be impossible using only the Excel features. The only way that seems to be possible is to copy the figure to the clipboard and then save it somehow to a file.

Save the picture to the clipboard is quite easy. I can do with the code below:

Please Login or Register  to view this content.

The second part is more dificult, since it seems not possible to do in Excel.

I see two possibilities to explore:
1 - Paste the saved image to mspaint and then save the image
2 - Write some .NET code to perform the task in C#. (In this case I would in fact write the whole solution in .NET)

I would really like to avoid solution 2 as it's not pretty portable.
Solution 1 is not perfect but would do the job.

I'm now trying to find out how to make my macro communicate with mspaint in order to complete the cycle.

If anyone has a better idea/solution please let me know.
I'd really apreciate a full Excel way.

Regards.

Eric.

Well, after some research and coding I found a solution that does the job, but is not that clean.

If someone someone has a better approach I'm still interested in.

Here the solution:

I used the option 1 I wrote in the previous post. The missing part was to communicate with mspaint to save the image.
In VBA we do this by calling Shell(appPath, options) and as paint doesn't supply an API to use its functions we shall use SendKeys(strKeys, wait) to interact with the window. SendKeys works as if we were typing on keyboard.

The drawback of this approach is that the mspaint window will be appearing for each figure we save. It's also important don't click the mouse in order to don't remove focus of the mspaint window.

Here follows my code:

Please Login or Register  to view this content.

There are a few things to comment on the code above.

The first line enables me to use the Sleep sub in place of Application.Wait. This is because I want an interval of less than 1 sec.

In SaveFigure I first check to see if the figure exists and if so its deleted (other strategies may be applyed). This is important to avoid the overwrite confirmation window in paint.

The saveClipBoardPicture sub saves the picture copied to the clipboard with the name supplied as its argument.
First I call Shell function to open mspaint. The sleep time that follows is needed to wait the application load. After that we activate the window.
From now on we can send commands via SendKeys as commented in code.

That's it.

Regards.

Eric.