Plot Secondary Y-Axis in Excel

[noxen1586]

I have two series of data and am trying to plot them to a primary Y-Axis and a secondary Y-Axis in Excel using ActiveX.  Right now I have a file in Excel that I am using to test my code that has the values 1 through 20 in the first three columns and is saved as test.xlsx in the filepath C:\Templates\test.xlsx.  I am able to open this file and plot the data in an XY scatter chart and name the primary x and y axes, but cannot figure out how to plot one of the data series against the secondary y-axis.

I created a macro in Excel and viewed the code it created to make a secondary y-axis.  This is the code:

Sub Macro1()
' Macro1 Macro
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).AxisGroup = 2
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.SeriesCollection(2).Select
End Sub

When I try to do this with ActiveX, I can select SeriesCollection(2), but when I choose AxisGroup, it is only a read function and I cannot write a 2 to it.

Is there another way to plot one of these data series against a secondary y-axis.

[Ben_J1]

Noxen,

  Which ActiveX Call specifically are you trying to call? Can you link the MSDN page of it?

Regards from Austin,

[noxen1586]

The attached image was the way that I would have thought I was supposed to create the secondary axis by writing a 2 or xlSecondary to the AxisGroup, but it does not work because AxisGroup is Read Only and I cannot write to it.

http://msdn.microsoft.com/en-us/library/aa141701%28office.10%29.aspx

If I create a secondary axis using a macro, I can act upon that axis.  However, I am not able to create the secondary axis without a macro, which is what I would really like to do.

Ryan

[Ben_J1]

Ryan,

  Unfortunately I am having a tough time navigating Excels ActiveX library to find what you are looking for in order to add that second Y-axis. The MSDN site you pointed me to was Visual Basic, not ActiveX as well. Could you find the ActiveX call on the MSDN site?

Regards from Austin, 

[noxen1586]

This is the link in the MSDN for the Axis Group in ActiveX.

I do not know if this is the correct ActiveX call to create the secondary Y-axis.  This is the main problem, I cannot find the correct ActiveX call to create the secondary axis.

[noxen1586]

http://msdn.microsoft.com/en-us/library/bb240962%28v=office.12%29.aspx

Sorry, here is the link.

[Ben_J1]

Noxen,

  Unfortunately I do not know which call to use either. Hopefully someone with more ActiveX experience will be able to shed some light on the correct way to achieve this second y-axis.

Regards from Austin,