How to export multiple charts from Access

[Ian Millward]

I need to export several tables and charts from an Access 2000 db into Excel
2000 because Access is very limited for displaying Charts. Ideally, I would
like to group data of a similar nature on the same sheet, tables down the
left side in blocks and charts adjacent. I can't seem to manage that.



The best I can do is one table of data per sheet. I can live with that but
the problem is that all the charts seem to go onto "Sheet1" and stack-up on
top of each other. I have played all the various combinations of
ActiveChart.Location and ActiveChart.SetSourceData but all to no avail. It
works fine if I import the tables into Excel and handraulically create the
charts but not using VBA, although this is not an acceptable option.



My question is: Am I trying to defy the laws of nature by trying to do
something Excel can't do or have I just not hit the correct permutation of
possibilities



Many thanks,



Ian Millward

Edinburgh

 

[Ian Millward]

Disregard.

I have just found the definitive answer on MS KB Page.

It is a bug and cannot be done.

 

[M]

I have a VBScript that may help you. The code imports a
recordset into a .XLS and generates a chart based on the
<.usedrange>. I use it as a basic template for larger
jobs, and maybe you will find it useful too. Sorry for the
bad textwrap.

M

'*---ADO Connection for ChartScript
'*---By: M. Mills 02/12/2004
'*---Imports data from Access.mdb and generates
'*---a chart in Excel.
'*---------------------------------

dim con
dim rst
dim rng
dim strCon
dim strSQL


strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _
&"Data Source=C:\Test.mdb"

set con = CreateObject("ADODB.Connection")
set rst = CreateObject("ADODB.Recordset")

con.Open strCon

'*---Establish the SQL statement
strSQL = "SELECT * INTO
Excel8.0;Database=C:\Drive.xls].[Drive] FROM Query2"

set rst = con.Execute(strSQL)

'*---Release Access and close connection
Set con = Nothing
Set rst = Nothing

'*---Open Excel and generate chart based on the
imported data

Set excel=CreateObject("Excel.Application")
excel.WorkBooks.Open "c:\Drive.xls"
excel.application.visible = true

'*---Delete the imported Field Names
Set rng = excel.Worksheets(1).Range("A1:C1")

rng.Select
rng.Delete

excel.Worksheets(1).usedrange.select

'*---Set up the chart
Set MyChart = Excel.Charts.Add()
MyChart.ChartType = 55
MyChart.SeriesCollection(2).Name = "=""Y
Axis Title"""
MyChart.SeriesCollection(1).Name = "=""X
Axis Title"""

'*---Basic chart properties
With MyChart
.HasTitle = True

.ChartTitle.Characters.Text= "Whatever1"
.Axes(1).HasTitle = True
.Axes(1).AxisTitle.Characters.Text
= "Whatever2"
.Axes(3).HasTitle = False
.Axes(2).HasTitle = True
.Axes(2).AxisTitle.Characters.Text
= "Whatever3"
End With

'*---Save the chart
Excel.ActiveWorkbook.SaveAs "C:\MSGraph_Test.xls"

 

[Tushar Mehta]

I'd be interested in what reference you found in the MSKB that led you
to conclude what you want cannot be done.

From your description of the task it is eminently doable.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Ian Millward]

I'd be interested in what reference you found in the MSKB that led you

to conclude what you want cannot be done.

From your description of the task it is eminently doable.

Try Article 245089

 

[Ian Millward]

Many thanks,

That will do nicely

I have a VBScript that may help you. The code imports a
recordset into a .XLS and generates a chart based on the
<.usedrange>. I use it as a basic template for larger
jobs, and maybe you will find it useful too. Sorry for the
bad textwrap.

M

'*---ADO Connection for ChartScript
'*---By: M. Mills 02/12/2004
'*---Imports data from Access.mdb and generates
'*---a chart in Excel.
'*---------------------------------

dim con
dim rst
dim rng
dim strCon
dim strSQL


strCon = "Provider=Microsoft.Jet.OLEDB.4.0; " _
&"Data Source=C:\Test.mdb"

set con = CreateObject("ADODB.Connection")
set rst = CreateObject("ADODB.Recordset")

con.Open strCon

'*---Establish the SQL statement
strSQL = "SELECT * INTO
Excel8.0;Database=C:\Drive.xls].[Drive] FROM Query2"

set rst = con.Execute(strSQL)

'*---Release Access and close connection
Set con = Nothing
Set rst = Nothing

'*---Open Excel and generate chart based on the
imported data

Set excel=CreateObject("Excel.Application")
excel.WorkBooks.Open "c:\Drive.xls"
excel.application.visible = true

'*---Delete the imported Field Names
Set rng = excel.Worksheets(1).Range("A1:C1")

rng.Select
rng.Delete

excel.Worksheets(1).usedrange.select

'*---Set up the chart
Set MyChart = Excel.Charts.Add()
MyChart.ChartType = 55
MyChart.SeriesCollection(2).Name = "=""Y
Axis Title"""
MyChart.SeriesCollection(1).Name = "=""X
Axis Title"""

'*---Basic chart properties
With MyChart
.HasTitle = True

.ChartTitle.Characters.Text= "Whatever1"
.Axes(1).HasTitle = True
.Axes(1).AxisTitle.Characters.Text
= "Whatever2"
.Axes(3).HasTitle = False
.Axes(2).HasTitle = True
.Axes(2).AxisTitle.Characters.Text
= "Whatever3"
End With

'*---Save the chart
Excel.ActiveWorkbook.SaveAs "C:\MSGraph_Test.xls"

-----Original Message-----
Disregard.

I have just found the definitive answer on MS KB Page.

It is a bug and cannot be done.


.

 

[Jon Peltier]

The article says

"RESOLUTION
To work around this problem, do not use the Location method. Instead,
use the Add method to add the embedded chart to the ChartObjects
collection."

This is one of the suggestions I very frequently make, not to solve
particular problems, but to simplify the chart automation process.

- Jon