D
Dan
Recently I had a need to make GIFs from embedded charts, with optional
resizing to any (within reason) user-specified scale factor. With
simple charts it's easy to just turn off screen updating, resize the
chart, make the GIF, put the chart back to its original size, and turn
screen updating back on. But with complicated charts having lots of
data labels, embedded text, and possibly other embedded shapes,
changing the size of the chart doesn't always give good results. Even
Auto scale fonts only scale in discrete steps, and custom-placed data
labels and other text doesn't stay in exactly the same relative
position when the chart size is changed.
So I snooped around on this ng. Harald Staff's XL2GIF macro gave me a
good start. After some additional tweaking I got what I wanted, and
when I was done I decided to duplicate the relevant code an put it
into a stand-alone utility. I'm happy to do a little payback by
making it available to readers. Here's what it does:
1. Assumes that an image (picture or bitmap) is already in the
clipboard. You can do a manual Shift > Edit > Copy Picture on a
chart, range of cells, or any shape such as Word Art, text boxes,
arrows, etc to make a clipboard copy. Or you can have some VBA code
do the Copy Picture for you; several examples are included. Or you
can use Alt-Print Screen to get a copy of the active window. Or you
can use the "Copy" function that many scientific and technical
programs have to make a bitmap copy of a plot or graph.
2. Pastes the clipboard image onto a temporary worksheet, optionally
resizes it using ScaleWidth and ScaleHeight, keeps track of the
dimensions, and (if necessary) copies the resized image back to the
clipboard.
3. Builds an empty chart and sets its size to exactly the same size
as the clipboard copy.
4. Pastes from the clipboard again, this time into the (empty) chart
area.
5. Adjusts the position of the pasted picture to make it line up with
the outside border of the overall chart, not with the slightly offset
chart area portion of the chart. This avoids the need to then have
any extra padding around the picture.
6. Exports the empty-except-for-the-picture chart as a GIF to a
user-specified file.
Available at www.qsl.net/ac6la/, look for the "Make a GIF" link. You
can use the utility as is or extract portions of the VBA code for your
own projects. If you are an image or clipboard guru (I'm not) you
might enjoy playing around with the ListClipboardFormats proc included
for debugging. Try selecting a single empty cell on a worksheet and
pressing Ctrl-C. You'll see that Excel has placed some 30-odd
different formats into the clipboard, most of which are a mystery to
me. All for a single empty cell!
Many thanks to Harald Staff, David McRitchie, and Stephen Bullen for
showing me the way.
Dan
resizing to any (within reason) user-specified scale factor. With
simple charts it's easy to just turn off screen updating, resize the
chart, make the GIF, put the chart back to its original size, and turn
screen updating back on. But with complicated charts having lots of
data labels, embedded text, and possibly other embedded shapes,
changing the size of the chart doesn't always give good results. Even
Auto scale fonts only scale in discrete steps, and custom-placed data
labels and other text doesn't stay in exactly the same relative
position when the chart size is changed.
So I snooped around on this ng. Harald Staff's XL2GIF macro gave me a
good start. After some additional tweaking I got what I wanted, and
when I was done I decided to duplicate the relevant code an put it
into a stand-alone utility. I'm happy to do a little payback by
making it available to readers. Here's what it does:
1. Assumes that an image (picture or bitmap) is already in the
clipboard. You can do a manual Shift > Edit > Copy Picture on a
chart, range of cells, or any shape such as Word Art, text boxes,
arrows, etc to make a clipboard copy. Or you can have some VBA code
do the Copy Picture for you; several examples are included. Or you
can use Alt-Print Screen to get a copy of the active window. Or you
can use the "Copy" function that many scientific and technical
programs have to make a bitmap copy of a plot or graph.
2. Pastes the clipboard image onto a temporary worksheet, optionally
resizes it using ScaleWidth and ScaleHeight, keeps track of the
dimensions, and (if necessary) copies the resized image back to the
clipboard.
3. Builds an empty chart and sets its size to exactly the same size
as the clipboard copy.
4. Pastes from the clipboard again, this time into the (empty) chart
area.
5. Adjusts the position of the pasted picture to make it line up with
the outside border of the overall chart, not with the slightly offset
chart area portion of the chart. This avoids the need to then have
any extra padding around the picture.
6. Exports the empty-except-for-the-picture chart as a GIF to a
user-specified file.
Available at www.qsl.net/ac6la/, look for the "Make a GIF" link. You
can use the utility as is or extract portions of the VBA code for your
own projects. If you are an image or clipboard guru (I'm not) you
might enjoy playing around with the ListClipboardFormats proc included
for debugging. Try selecting a single empty cell on a worksheet and
pressing Ctrl-C. You'll see that Excel has placed some 30-odd
different formats into the clipboard, most of which are a mystery to
me. All for a single empty cell!
Many thanks to Harald Staff, David McRitchie, and Stephen Bullen for
showing me the way.
Dan