Export Access Chart to Excel using VBA code

[ADFC]

Hello,



I would like to export an Access Chart to Excel using VBA code.

Most specifically, I have a form that shows some data that is obtained from a query.
In that same form there is a chart that graphs out the query.

What I want to do, is to export the query and it's associated chart by using a form button with VBA code.

The query export process is easy.
For that I used the following code:

Private Sub Command00_Click()
On Error GoTo Err_Command00_Click

Dim Output1 As Integer
DoCmd.Requery
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Query1", "DB_Export", True

Output1 = MsgBox("DB exported successfully!", vbOKOnly, "")

Exit_Command00_Click:
    Exit Sub

Err_Command00_Click:
    MsgBox Err.Description
    Resume Exit_Command00_Click

End Sub

Now, what code should I include to export the chart as well?
If it's not possible to export the chart (as a chart element), how can I export the chart as a JPEG into the Excel file "DB_Export.XLSB" that is created with the code provided above?

Excel File Name = DB_Export.XLSB
Excel Worksheet Name = Sheet1
Access Chart Name=Chart1


Thank you kindly

[June7]

Everything returned by a web search concerned exporting chart from Excel. Access can export data to Excel then Excel charting can use the data to render a graph. Or Excel can link to Access query and pull in the data for use by graph.

Don't think Access can export to jpg format. I think PDF is only graphic format available to Access or any MS Office app.

[ADFC]

Hello June7.

Everything returned by a web search concerned exporting chart from Excel.
Yes but I want to export from Access to Excel.


Access can export data to Excel then Excel charting can use the data to render a graph.
I know, but I would like to automate the process with a single click of a button.
And please remember that the excel file is generated after I click on my form's button, so there is no simple way for me to have a chart waiting for the query output.


Or Excel can link to Access query and pull in the data for use by graph.
Not automated enough, but please correct me if I'm wrong.


Don't think Access can export to jpg format. I think PDF is only graphic format available to Access or any MS Office app.
That's why I want to use VBA code.


Any help?
Thank you

[June7]

I know you want to export chart to excel, just saying I couldn't find any help reference on the web for that requirement.

Why not export data to an existing Excel sheet with a chart already developed to use the data? Or link to data from Excel side. Then all the Access VBA has to do is open the Excel file via a hyperlink.

I don't think any MS Office app can export to graphic format other than PDF, with or without VBA.

I know it's not what you want to hear but just might be the reality.

Why do you need data and graph in Excel?

[ItsMe]

I was going to suggest, "Send your data to Excel and use Excel chart." Maybe make a couple references to Excel and use a template.

Maybe I won't suggest that now.

[ADFC]

Why not export data to an existing Excel sheet with a chart already developed to use the data? Or link to data from Excel side.

Yes I ended up taking this suggestion. I only export the raw data, and then, the pre-applied Excel chart does the rest.

I don't think any MS Office app can export to graphic format other than PDF, with or without VBA.

Hum... are you sure?
I am able to export the chart (but in JPEG format) by using the following simple code.

Private Sub Command00_Click()
On Error GoTo Err_Command00_Click
Dim Output1 As Integer
Dim My_Graph1 As Object

DoCmd.Requery
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Query1", "DB_Export", True

Set My_Graph1 = Me.Graph1.Object
My_Graph1.Export "C:\Documents\Graph1.jpg", "JPEG"
Set My_Graph1 = Nothing
Me.Graph1.Action = acOLEClose
Output1 = MsgBox("Success", vbOKOnly)

Exit_Command00_Click:
    Exit Sub

Err_Command00_Click:
    MsgBox Err.Description
    Resume Exit_Command00_Click

End Sub

But for some reason, the code above crashes MS Access, so maybe someone can perfect it a bit more.

It would still be nice to export the chart as an "Excel Chart Element", but for now, the pre-applied Excel chart does the trick.

[June7]

Of course I can be wrong - couldn't find any code examples on first search. Just found this http://mrvsto.com/2010/07/excel-vba-...t-to-jpg-file/

I got this to work in Access:

Forms!MainMenu.Graph1.Export "C:\Temp\Graph1.jpg", "JPEG"

I also tried BMP but that failed.

[ItsMe]

If there is a chart object in Excel, you can export that object as a JPG or BMP using VBA.
https://www.accessforums.net/program...tml#post212827

If I were looking for a way to link a chart object in Excel to an OLE control on an Access report I would take the above link into consideration.

[trevor40]

If you are after a single click to export your chart to excel, and you are used to vba for access and excel, you could do it all from access by manipulating excel from access. you can create charts, ranges, lists, sheets, anything. to get started record a macro in excel and use that as a reference for the code to use in access to do the same thing. a link between access and excel is required to do this. i have posted some examples of this in other posts.