Set custom chart type from MS Access 97 to Excel 2000?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using VBA in MS Excel 97 in calling Excel 2000 to populate a
spreadsheet and draw a chart. I'm having trouble choosing a custom chart
type when I call from Access.

In VBA for Excel 2000, I can use the following to set a custom chart type:
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

but when I call this from Access, it generates an xlColumnClustered
chart. (I know this because the debugger tells me that the numerical
value for ActiveChart.ChartType is 51, which is the numerical value for
xlColumnClustered.) I'm guessing that the Access -> Excel code passing
is the problem, because if I run some VBA in Excel itself, I have no
problem with the above code. BTW, the debugger in Excel reports
ActiveChart.ChartType = -4111. I can't plug that into the ChartType when
I set the value, because I get a type mismatch. Obviously the -4111 is
some kind of junk since it's a custom chart type.

Any idea how I can get this custom chart type represented? If not, is
there a way to programatically create a custom chart type on the fly
which will show a line - column chart?
 
Another way to create a combo chart is to create a chart of one kind
(with both series). Then, select the series that should be the other
kind and use Chart | Chart Type... to select the appropriate type.

Record your actions and XL will give you the necessary syntax which you
can then generalize.

--
Regards,

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

NoSpamTakeSquareRootOfNumber <"nlee144
(NoSpamTakeSquareRootOfNumber)"@eesus.jnj.com> says...
 
When you have mixed types in a chart, VBE will return -4111 for the
chart type.

Have you set a reference to the Excel Object Model from Access? (In the
Access VB Editor, choose References from the Tools menu, scroll to find
Excel, and check the checkbox.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Jon said:
When you have mixed types in a chart, VBE will return -4111 for the
chart type.

Have you set a reference to the Excel Object Model from Access? (In the
Access VB Editor, choose References from the Tools menu, scroll to find
Excel, and check the checkbox.)

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
Click to expand...

I did set the reference, and was having no trouble getting any other
Excel VBA to work from within Access. I've found a workaround, though.
For some reason, it just didn't want to allow me to choose a predefined
custom chart type, so I found that I could alter the chart type for a
single data series (I thought the chart type veriable was for the entire
chart object, but it can apparently also be applied to individual data
series objects. Problem solved, the chart looks great!

-Nathan
 
NoSpamTakeSquareRootOfNumber said:
I did set the reference, and was having no trouble getting any other
Excel VBA to work from within Access. I've found a workaround, though.
For some reason, it just didn't want to allow me to choose a predefined
custom chart type, so I found that I could alter the chart type for a
single data series (I thought the chart type veriable was for the entire
chart object, but it can apparently also be applied to individual data
series objects. Problem solved, the chart looks great!

-Nathan
Click to expand...

Nathan -

You've discovered the secret of custom combination charts. Each series
can have its own independently defined type. (I have more hints like
this on my web site.) Much better to know how to type your own series
than rely on the limited set that come with Excel.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
 
Setting Chart Type of Excel Graph in Access VBA

I'm into this thread now.

I wanna set the Chart Type in Access VBA.

What's the equivalent syntax in Access VBA for the following Excel code?

Code: 
ActiveChart.ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column"

Got this after recording a macro in Excel.
It sets the Chart Type into "Custom Type", "Line - Column".

What's in Access VBA?

Some help is really appreciated.
 
Back
Top