For David McRitchie and/or Harold Staff and/or anyone else -- creating multiple gifs from a workshee

[Father Guido]

Hi guys,

A few years ago one or both of you passed along this code
to me, if takes various sections of an excel page and
exports thos sections as individual gif pictures named
t0.gif thru t19.gif. I haven't had need to use it the
last year, but when I tried to use it tonight I got a
Run-time error '1004': Method 'Export' of '_Chart' failed

I was using this exactly as is 18 months ago (except for
the file name update to 0506 and changed/added some ranges
for inclusion). I had Excel 2002 when I ran this last, now
I'm using Excel 2003 -- just in case that makes a difference.

The code always fails at the following line.

ActiveChart.Export Filename:=LCase(SaveName), _
FilterName:="GIF"

The entire macro code is as follows:

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:Q22,A26:G39,A41:G52,A54:G67," & _
"A69:G84,A86:G102,A104:G118,A120:G136,A138:G152," & _
"A154:G167,A169:G184,A186:G200,A202:G216,A218:G236," & _
"A238:G256,A258:G273,A275:G287,A289:G308,A310:G324,A326:G340")
rng.Select
Set Sourcebok = ActiveWorkbook
ImageContainer_init

i = -1
For Each ar In rng.Areas
i = i + 1
container.ChartArea.ClearContents
SaveName = "C:\Documents and Settings\root\Desktop\Pool0506\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



Thanks for any light you can share!!!

Norm

PS Anyone know why my scroll wheel mouse
won't scroll in the VBA Edit window in
Excel 2003?

 

[Gary''s Student]

I suspect SaveName. Make sure your directory structure still supports the
value.

Does C:\Documents and Settings\root\Desktop\Pool0506 still exist?

 

[Bob Phillips]

Norm,

Can't reproduce the error on the code, but for the mouse wheel

Either
- revert to Version 4, or
- use a third party driver product, for example,
http://www.geocities.com/SiliconValley/2060/freewheel.html, or
- look at http://www.gasanov.net/VBScroll.htm


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Father Guido]

On Sun, 13 Nov 2005 03:05:05 -0800, Gary''s Student

~I suspect SaveName. Make sure your directory structure still
supports the
~value.
~
~Does C:\Documents and Settings\root\Desktop\Pool0506 still exist?

Hi,

Yes, C:\Documents and Settings\root\Desktop\Pool0506 does exist.
How do I ensure my directory structure supports the 'SaveName' value?

Thanks for your help,

Norm

 

[Father Guido]

On Sun, 13 Nov 2005 11:19:02 -0000, "Bob Phillips"

~Norm,
~
~Can't reproduce the error on the code, but for the mouse wheel
~
~Either
~- revert to Version 4, or
~- use a third party driver product, for example,
~http://www.geocities.com/SiliconValley/2060/freewheel.html, or
~- look at http://www.gasanov.net/VBScroll.htm

Hi Bob,

So you copied the code and it ran OK? That means something is screwed
up in my application setup then. I wonder what's wrong with my setup
then. Anyway thanks for your help, and I'll check out the freewheel
sites.

Thanks

Norm

 

[Bob Phillips]

Yeah, exactly that.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Vic Eldridge]

Hi Norm,

From the Excel help files,
"You also need to have the GIF filter installed to export a single chart to
a GIF file by using Microsoft Visual Basic for Applications."

To see which graphics filters are installed, check the following registry key,
HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\Graphics Filters\Export.

If there's not a GIF filter installed, you'll need to use the Office
installation program to install it.

Regards,
Vic Eldridge

 

[Father Guido]

On Sun, 13 Nov 2005 18:22:03 -0800, Vic Eldridge

~Hi Norm,
~
~From the Excel help files,
~"You also need to have the GIF filter installed to export
~a single chart to a GIF file by using Microsoft Visual
~Basic for Applications."
~
~To see which graphics filters are installed, check the
~following registry key,
~HKEY_LOCAL_MACHINE\Software\Microsoft\Shared Tools\Graphics
Filters\Export.
~
~If there's not a GIF filter installed, you'll need to use the Office
~installation program to install it.
~
~Regards,
~Vic Eldridge

Hi Vic,
I checked the registry and I have entries for GIF, JPEG, PNG, and
TIFF. Under GIF, there's an Option folder with the following three
entries...

NAME TYPE DATA
(Default) REG_SZ (value not set)
ShowMetafileBackgroundTransparent REG_SZ No
ShowProgressDialog REG_SZ Yes

Under JPEG I see... Options

NAME TYPE DATA
(Default) REG_SZ (value not set)
Quality REG_SZ 0x0000004b
ShowProgressDialog REG_SZ Yes

Under PNG I see... Options

NAME TYPE DATA
(Default) REG_SZ (value not set)
ShowMetaBackgroundTransparent REG_SZ No
ShowMetafileBackgroundTransparent REG_SZ No
ShowProgressDialog REG_SZ Yes

Under TIFF I see...

Filter API containing...
NAME TYPE DATA
(Default) REG_SZ (value not set)

Image API containing...
NAME TYPE DATA
(Default) REG_SZ (value not set)
BiLevel REG_SZ 1, 141, 1
Black Is 0 REG_SZ Yes
Compression Type REG_DWORD 0x00000080 (128)
Description REG_DWORD 0x00000007 (7)
GrayScale REG_SZ 1672, 177, 1
Halftones REG_SZ No
Palette REG_SZ 142, 177, 1
RGB REG_SZ 128, 161, 3
RGB Planar REG_SZ 128, 161, 3
Time Stamp REG_DWORD 0x01be2275 (29237877)

Options containing...
NAME TYPE DATA
(Default) REG_SZ (value not set)
Quality REG_DWORD 0x0000004b (75)
ShowProgressDialog REG_SZ Yes

The funny thing is I can choose TIFF in a program written by
John Walkenbach and it works, but when I choose GIF, JPEG, or
PNG it fails and says I may need to install the converter, or
my drive is full. Well the drive isn't full. I d/l an Office
filters pack and installed it and rebooted, but that didn't
make any difference -- it still fails. So I guess I'll need
to get the filters/converters and install them. I'm using a
registered version Office 2003 standard on Windows XP Home.

I don't see anything specific on the CD about filters or
converters. Can I add them by just running setup and selecting
an option, or do I need to re-install the entire Office 11
suite? This is a pain in the (rhymes with gas). <sigh>

Norm

 

[David McRitchie]

Hi Norm (aka Father Guido),
Just noticed your thread, not that I could have answered it except for
a different suggestion for the scrolling problem in VBE part.

I expect that turning off Archiving, especially since you are the original
poster, will mess up this thread in Google Groups when Google deletes your
posting per your instruction. Had I not checked reference in Google Groups
I would not have noticed.
X-Newsreader: Forte Agent 1.92/32.572
X-No-Archive: yes
From Google Groups I can see your original message indicates:
Note: The author of this message requested that it not be archived. This
message will be removed from Groups in 1 day (Nov 20, 2:23 am).

 

[Father Guido]

On Fri, 18 Nov 2005 11:51:30 -0500, "David McRitchie"

~Hi Norm (aka Father Guido),
~Just noticed your thread, not that I could have answered it except
for
~a different suggestion for the scrolling problem in VBE part.
~
~I expect that turning off Archiving, especially since you are the
original
~poster, will mess up this thread in Google Groups when Google
deletes your
~posting per your instruction. Had I not checked reference in Google
Groups
~I would not have noticed.
~ X-Newsreader: Forte Agent 1.92/32.572
~ X-No-Archive: yes
~From Google Groups I can see your original message indicates:
~ Note: The author of this message requested that it not be
archived. This
~ message will be removed from Groups in 1 day (Nov 20, 2:23 am).
~

Hi Dave, I didn't even realize I had that turned on. I have nothing to
hide and have turned off that option. I guess it's too late for the
last/this thread, however I'll be starting a new one as I'm still
having problems.

Thanks,

Norm