PDA

View Full Version : Imbedding Image File Instead of Referencing?



sam314159
10-22-2009, 01:41 PM
I wanted to use an image file as the background for a chart that my VBA script was creating so I used the code below to reference the image file:

ActiveChart.PlotArea.Select
Selection.Fill.UserPicture PictureFile:= _
"T:\Shared\Resources\ChartGround.gif"

This worked great with one shortcoming. It only worked when the user had access to the shared drive and when some external users started using the script they couldn't get the image.

1. Is there any way to actually imbed the image file in the workbook and then reference it like I did above?

2. Can anyone think of a better way for me to do this? I just want my VBA script to use an image as the background to the chart it creates.

ps I have tried other alternatives like sending the image file to every user and just referencing the image relative to the working directory but I don't like having to send the user two files. I would much rather just handle a single file.

Thanks in advance for the help. I hope I didn't miss the solution when I tried searching the forums.

Sam

sam314159
10-23-2009, 05:08 AM
Is this even possible?

hardlife
10-23-2009, 02:38 PM
Hi Sam,

You could put image to Your workbook, than export "named range" to workbook directory (GIF, JPG, ..) using this:

http://www.excelforum.com/excel-programming/510767-save-range-in-a-new-workbook-as-gif.html

and import exported range back to Your chart and delete exported image.

Goog Luck:hi:

sam314159
10-26-2009, 07:37 AM
Thanks for the reply Hardlife.

I tried modifying the code you suggested to get it to do what I want but I couldn't get it to work.

I can get it to paste the selected picture over the entire chart but I actually need it to set the picture as the chart background and still show all the plotted data.

I can do this using:

ActiveChart.PlotArea.Select
Selection.Fill.UserPicture PictureFile:= _
"T:\Shared\Resources\ChartGround.gif"

But I can't figure out how to do this using an image embedded in the workbook instead of having to reference an external file.

Any other ideas please?

hardlife
10-28-2009, 02:07 PM
Hi Sam,

me do not know if You will like it, but it works for me :-)

chocolate welcome :-)

Happy and Sunny Day, Good Night for now, Pavel

:hi:

sam314159
10-29-2009, 11:50 AM
Hi Sam,

me do not know if You will like it, but it works for me :-)

chocolate welcome :-)

Happy and Sunny Day, Good Night for now, Pavel

:hi:

That's brilliant Pavel! Thanks man.

I never would've thought of first creating the file on the fly for every user machine and then deleting it when done. Nice suggestion.

I do have 1 followup question if anyone gets a second please.

Is there anyway to get rid of the white margin that Excel puts around the image when I paste it inside a chart? I want the chart area to be 100% image and no stupid margin but I can't seem to turn it off.

You can see it when you run the macro above. The saved image file always has a thin white border around it, I'd like to get rid of it.

Thanks again for the help.

hardlife
10-29-2009, 12:36 PM
Sam, try third button, it does work for me in older office, not in 2007.

Good night for now and Good Luck, Pavel :hi:

hardlife
10-31-2009, 11:30 AM
Sam, please try some new buttons :-)

HTH, Good Luck, Pavel :hi:

sam314159
11-03-2009, 11:34 AM
Pavel,

That did exactly what I needed! Thank you so much for going through the trouble of creating example macros. I posted this same question on three other forums and I couldn't get a single reply.

Thanks again.

hardlife
11-03-2009, 12:14 PM
Sam, me is happy to help You :hi:

You are welcome, Happy day and Good Luck, Pavel :hi: