Export to excel and then invoke Excel to produce charts?

  • Thread starter Thread starter mscertified
  • Start date Start date
M

mscertified

I have a need to produce charts from reports. These are too complex for
Access to handle. From within Access, I'd like to export a query to Excel
then invoke Excel to produce the charts. Is this possible, and is there
sample code anywhere?

Thanks.
 
I currently create a pivot table report. It is complex as I needed to allow
multiple uses of the database at the same time. Therefore, I created a
template spreadsheet for each potential user. The output query is written to
a "temporary" table. this table is created and deleted within the macro. It
is then output to a data spreadsheet which is used as a data source by the
template spreadsheet. At the end, I open the template, refresh the data (the
template has autorefresh turned off), and save it under a new name. It is
then made available for the user to view.

Here is the applicable code:

'Output the table created in Query 2 to the Excel Data file
Path = "J:\Share\ASA\DSS 1.0\Access DataBase\"
DoCmd.OutputTo acOutputTable, StrQ2_tablename, acFormatXLS, Path &
UserID & "Data.xls", False

'Prepare to open the proper Excel Spreadsheet template
Set ExcelSheet = CreateObject("Excel.Application")

' Open the Template Pivot Table
Set ExcelWB = ExcelSheet.Workbooks.Open(Path & UserID & "Template.xls")

'Update The Pivot Table
ExcelWB.RefreshAll

'Save the updated Pivot Table as the Product Named
OutputPath = "J:\Share\ASA\DSS 1.0\Product Pivot Tables\"

ExcelWB.SaveAs (OutputPath & UserID & " " & ProductName & ".xls")

' Make Excel visible through the Application object.
ExcelSheet.Visible = True
 
Back
Top