asked on

Saving an Excel Chart as BMP To Display on Visual Basic Picture Box

Hi, I have spreadsheet with data on it. I have created a Chart using visual basic. My next step is to display this chart on a picture box. I have read about pictures boxes and they display images of BMP, GIF and metafile format. So I need to save this chart to .BMP or GIF format and show it on the Picture box. I have done some more research on this but with out success. Can you please help me?
This is a line I found but it does not work:
oXSheet.ChartObjects(1).Chart.Export FileName:="c:\Log\AllData1.gif", FilterName:="GIF"
When I run it I get this message: Object variable or With block variable not set

ahammar

I don't know for sure if this will work, but try it and see:

Dim Chrt as object
Set Chrt = oXSheet.ChartObjects(1).Chart.Export FileName:="c:\Log\AllData1.gif", FilterName:="GIF"

:-)
ah

Jeroen Rosink

perhaps this works out for you:

Private Sub UserForm_activate()
Set ThisChart = ActiveSheet.ChartObjects(1).Chart
fName = ThisWorkbook.path & Application.PathSeparator & "\temp.Gif"
ThisChart.Export FileName:=fName, FilterName:="GIF"

'Sheets("Sheet1").ChartObjects(1).Delete
Image1.Picture = LoadPicture(fName) ' the object used here is Image1

Kill ThisWorkbook.path & Application.PathSeparator & "temp.gif"

End Sub

caleno

ASKER

Guys thanks for your responses.
roos01: Can you explain your answer. I am not an expert on this and I am having trouble trying to understand it. I have try it and it gave an error right on this line: "Compile Error"
fName = c:\Log\AllData1.xls.PathSeparator & "\temp.Gif"
I have replaced your ThisWorkbook.path & Application for the actual path of my excel file.

Can you explain to me what this line does? Kill ThisWorkbook.path & Application.PathSeparator & "temp.gif"

Thank you

ASKER CERTIFIED SOLUTION

Jeroen Rosink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

caleno

ASKER

Jeroen, I tried your again but it gave an error when it tried to execute ThisWorkbook line.

Run-Time error '1004':
Method 'ThisWorlbook' of object '_Global' failed

This is what I have:

Set oXLBook = oXLApp.Workbooks.Open("c:\Log\AllData1.xls")
Set oXLSheet = oXLBook.Worksheets(1)

oXLApp.Visible = True
Set oXLBook = Nothing
Set oXLApp = Nothing

Set ThisChart = oXLSheet.ChartObjects(1).Chart
fName = ThisWorkbook.Path & Application.PathSeparator & "\temp.Gif" '****ERROR Right Here*****
DataChart.Picture = LoadPicture(fName)

What I am missing here? I appreciate your help.

ahammar

Application.PathSeparator is equal to "\". Since you have a backslash in front of "temp.Gif" you don't need both. Get rid of the backshlash or "& Application.PathSeparator" and see if it works.

So use this:
fName = ThisWorkbook.Path & "\temp.Gif"

or this:
fName = ThisWorkbook.Path & Application.PathSeparator & "temp.Gif"

so that you do not have 2 backslashes in the string

:-)
ah

caleno

ASKER

Thanks to you guys for your responses. I found the answer to my question and now it is working.