VBA display excel chart

[tekster23]

I found a code snippet that opens a workbook and creates a temp gif image to display on a form. I have modified it a bit and it is pretty good, ( I pass a password as the workbook is protected and I created a tempvar to store the strfile so that I can delete it when Im done with it) but I have ran into a few issues.

If the workbook at more than 3-4 graphs the script does not return the image; instead it returns a error 2114. I ended up having to create several individual excel documents with 3 graphs each. Is that a limitation of excel or access or windows or the snippet? Thoughts?

dim newapp as object
set newapp=createobject("excel.application")
dim wb as object
dim ws as object
dim ct as object
dim strFile as string
set wb=newapp.workbooks.open("workbook path")
set ws=wb.sheets(1)
set ct=ws.chartobjects(1) 'sometimes you have to put .chartobjects(1).chart
strFile=environ("temp") & "\" & format(now,"mmddyyhhmmss") & ".GIF"
ct.export strfile,FilterName:="GIF"
Me.NameOfAnImageControl.Picture = strFile
Me.Repaint

 

[CSmith]

What do you mean does not return image? It will only save/export first 3 gif image chart pages?

 

[tekster23]

When the workbook at 15 charts, it would only return the first 3 indexed charts all others would return the 2114 error.

set ct=ws.chartobjects(1)

I actually changed that to something like ...

set ct=ws.chartobjects([chartnumber])

So I can save a # of which cart I want a particular link to open.

 

[CSmith]

Have you tried adding an error routine and skipping any exports that error out or moving the chart to another workbook?

 

[tekster23]

Like I said I split out the 15 charts to 5 individual workbooks and they all load. I was trying to figure out why it wasn't working with all 15 in one workbook.

 

[CSmith]

Yes so was I sounds like memory issue but not sure...