How to export XL to GIF as a transparent gif?

Hi,

I'm using a procedure Harold Staff made for me a few years ago. It works
very well, but I would like to improve on it slightly, but I don't know
how, or if it's even possible. I'm using XL2002 and Windows XP.
I made one change to remove the border, now I would like to export the
gif image as a transparent gif. I use several gifs for a web page and
convert each gif manually with MS Photo Editor to transparent so the
images appear over the background, rather than in the background. The
background is a picture, so the white boxes look bad. The chart/gif
background is white after the xl2gif routine has finished creating the
gif. Setting the chart plot area to no fill doesn't affect the gif
output. Sooooo...

does anyone know how/if I can set transparency as my gifs are created?

Thanks!

Norm
________________________________________________________________________

Option Explicit
'Harold Staff -- see http://www.mvps.org/dmcritchie/excel/xl2gif.htm
'XL2GIF_module -- GIF_Snapshot
Dim container As Chart
Dim containerbok As Workbook
Dim Obnavn As String
Dim Sourcebok As Workbook

Private Sub ImageContainer_init()
Workbooks.Add (1)
ActiveSheet.Name = "GIFcontainer"
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Worksheets(1).Range("A1")
ActiveChart.Location Where:=xlLocationAsObject, _
Name:="GIFcontainer"
ActiveChart.ChartArea.ClearContents
Set containerbok = ActiveWorkbook
Set container = ActiveChart
End Sub

Sub MakeAndSizeChart(ih As Integer, iv As Integer)
Dim Hincrease As Single
Dim Vincrease As Single
Obnavn = Mid(ActiveChart.Name, Len(ActiveSheet.Name) + 1)
Hincrease = ih / ActiveChart.ChartArea.Height
ActiveSheet.Shapes(Obnavn).ScaleHeight Hincrease, _
msoFalse, msoScaleFromTopLeft
Vincrease = iv / ActiveChart.ChartArea.Width
ActiveSheet.Shapes(Obnavn).ScaleWidth Vincrease, _
msoFalse, msoScaleFromTopLeft
End Sub
Public Sub GIF_Snapshot()
Dim varReturn As Variant
Dim MyAddress As String
Dim SaveName As Variant
Dim MySuggest As String
Dim Hi As Integer
Dim Wi As Integer
Dim Suffiks As Long
Dim rng As Range
Dim ar As Range
Dim i As Integer

Set rng = Range("H1:Q19,A23:G39,A41:G56,A58:G76," & _
"A78:G97,A99:G116,A118:G131,A133:G149,A151:G170," & _
"A172:G191,A193:G211,A213:G229,A231:G250,A252:G268," & _
"A270:G287,A289:G306,A308:G325")
rng.Select
Set Sourcebok = ActiveWorkbook
ImageContainer_init

i = -1
For Each ar In rng.Areas
i = i + 1
container.ChartArea.ClearContents
' C:\Documents and Settings\Norm\Desktop\Pool0304\Temporary
SaveName = "C:\Documents and Settings\Norm\Desktop\Pool0304\t" & i
& ".gif"
Sourcebok.Activate
ar.Select
Selection.CopyPicture Appearance:=xlScreen, _
Format:=xlBitmap
Hi = Selection.Height + 4 'adjustment for gridlines
Wi = Selection.Width + 6 'adjustment for gridlines
containerbok.Activate
ActiveSheet.ChartObjects(1).Activate
MakeAndSizeChart ih:=Hi, iv:=Wi
ActiveChart.Paste
ActiveChart.ChartArea.Border.LineStyle = 0
ActiveChart.Export Filename:=LCase(SaveName), _
FilterName:="GIF"
ActiveChart.Pictures(1).Delete
Sourcebok.Activate
Next
Avbryt:
On Error Resume Next
Application.StatusBar = False
containerbok.Close SaveChanges:=False
End Sub
________________________________________________________________________



Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

Norm -

As far as I know, you cannot apply transparency in Excel to an exported
gif file. You have to resort to Photo Editor or another graphics program.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Father Guido wrote:

> Hi,
>
> I'm using a procedure Harold Staff made for me a few years ago. It works
> very well, but I would like to improve on it slightly, but I don't know
> how, or if it's even possible. I'm using XL2002 and Windows XP.
> I made one change to remove the border, now I would like to export the
> gif image as a transparent gif. I use several gifs for a web page and
> convert each gif manually with MS Photo Editor to transparent so the
> images appear over the background, rather than in the background. The
> background is a picture, so the white boxes look bad. The chart/gif
> background is white after the xl2gif routine has finished creating the
> gif. Setting the chart plot area to no fill doesn't affect the gif
> output. Sooooo...
>
> does anyone know how/if I can set transparency as my gifs are created?
>
> Thanks!
>
> Norm
> ________________________________________________________________________
>
> Option Explicit
> 'Harold Staff -- see http://www.mvps.org/dmcritchie/excel/xl2gif.htm
> 'XL2GIF_module -- GIF_Snapshot
> Dim container As Chart
> Dim containerbok As Workbook
> Dim Obnavn As String
> Dim Sourcebok As Workbook
>
> Private Sub ImageContainer_init()
> Workbooks.Add (1)
> ActiveSheet.Name = "GIFcontainer"
> Charts.Add
> ActiveChart.ChartType = xlColumnClustered
> ActiveChart.SetSourceData Source:=Worksheets(1).Range("A1")
> ActiveChart.Location Where:=xlLocationAsObject, _
> Name:="GIFcontainer"
> ActiveChart.ChartArea.ClearContents
> Set containerbok = ActiveWorkbook
> Set container = ActiveChart
> End Sub
>
> Sub MakeAndSizeChart(ih As Integer, iv As Integer)
> Dim Hincrease As Single
> Dim Vincrease As Single
> Obnavn = Mid(ActiveChart.Name, Len(ActiveSheet.Name) + 1)
> Hincrease = ih / ActiveChart.ChartArea.Height
> ActiveSheet.Shapes(Obnavn).ScaleHeight Hincrease, _
> msoFalse, msoScaleFromTopLeft
> Vincrease = iv / ActiveChart.ChartArea.Width
> ActiveSheet.Shapes(Obnavn).ScaleWidth Vincrease, _
> msoFalse, msoScaleFromTopLeft
> End Sub
> Public Sub GIF_Snapshot()
> Dim varReturn As Variant
> Dim MyAddress As String
> Dim SaveName As Variant
> Dim MySuggest As String
> Dim Hi As Integer
> Dim Wi As Integer
> Dim Suffiks As Long
> Dim rng As Range
> Dim ar As Range
> Dim i As Integer
>
> Set rng = Range("H1:Q19,A23:G39,A41:G56,A58:G76," & _
> "A78:G97,A99:G116,A118:G131,A133:G149,A151:G170," & _
> "A172:G191,A193:G211,A213:G229,A231:G250,A252:G268," & _
> "A270:G287,A289:G306,A308:G325")
> rng.Select
> Set Sourcebok = ActiveWorkbook
> ImageContainer_init
>
> i = -1
> For Each ar In rng.Areas
> i = i + 1
> container.ChartArea.ClearContents
> ' C:\Documents and Settings\Norm\Desktop\Pool0304\Temporary
> SaveName = "C:\Documents and Settings\Norm\Desktop\Pool0304\t" & i
> & ".gif"
> Sourcebok.Activate
> ar.Select
> Selection.CopyPicture Appearance:=xlScreen, _
> Format:=xlBitmap
> Hi = Selection.Height + 4 'adjustment for gridlines
> Wi = Selection.Width + 6 'adjustment for gridlines
> containerbok.Activate
> ActiveSheet.ChartObjects(1).Activate
> MakeAndSizeChart ih:=Hi, iv:=Wi
> ActiveChart.Paste
> ActiveChart.ChartArea.Border.LineStyle = 0
> ActiveChart.Export Filename:=LCase(SaveName), _
> FilterName:="GIF"
> ActiveChart.Pictures(1).Delete
> Sourcebok.Activate
> Next
> Avbryt:
> On Error Resume Next
> Application.StatusBar = False
> containerbok.Close SaveChanges:=False
> End Sub
> ________________________________________________________________________
>
>
>
> Father Guido
> ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
> I plan on living forever... so far, so good

On Mon, 24 Nov 2003 12:38:14 -0500, Jon Peltier
<jonpeltierNOSPAM@yahoo.com> wrote:

>Norm -
>
>As far as I know, you cannot apply transparency in Excel to an exported
>gif file. You have to resort to Photo Editor or another graphics program.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>http://www.geocities.com/jonpeltier/Excel/index.html
>_______

Thanks Jon,

I was afraid of that. Thank goodness I only have 17 gifs to
transparencise weekly,

Norm

Father Guido
¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
I plan on living forever... so far, so good

I once used SendKeys to automate PhotoEditor. It wasn't very fast, and
it wasn't very pretty, but it did the job.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Father Guido wrote:

> On Mon, 24 Nov 2003 12:38:14 -0500, Jon Peltier
> <jonpeltierNOSPAM@yahoo.com> wrote:
>
>
>>Norm -
>>
>>As far as I know, you cannot apply transparency in Excel to an exported
>>gif file. You have to resort to Photo Editor or another graphics program.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>http://www.geocities.com/jonpeltier/Excel/index.html
>>_______
>
>
> Thanks Jon,
>
> I was afraid of that. Thank goodness I only have 17 gifs to
> transparencise weekly,
>
> Norm
>
> Father Guido
> ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
> I plan on living forever... so far, so good