I am writing a VB5 app that sends output to an Excel 97 spreadsheet, and then creates a chart from the data. As the process takes quite some time to complete, and knowing how users are very impatient, I have (where practicable) avoided all references to Activesheet, Selection etc... and have tried to program with absolute references
So I have m_xlOutput.Charts(1) instead of m_xlOutput.ActiveCharts
This is where the problem arises.
I have two sheets in the spreadsheet, "Inputs" and "Outputs". I then add a chart (charts.add), add the series, and format it. By default the Add method creates the chart as a new sheet, so I need to move it to an object on the sheet "Outputs", so I use the:
..Charts(1).Location xlLocationAsObject, "Outputs"
This behaves strangely, and moves the chart to the "Inputs" page, but more disturbingly, removes the chart from the Charts collection. Thus Charts(1) now no longer exists.
So the question really is, (1) how do you refer to a chart object, that is an object on a sheet, and (2) why is the Location method moving the chart to the wrong sheet
So I have m_xlOutput.Charts(1) instead of m_xlOutput.ActiveCharts
This is where the problem arises.
I have two sheets in the spreadsheet, "Inputs" and "Outputs". I then add a chart (charts.add), add the series, and format it. By default the Add method creates the chart as a new sheet, so I need to move it to an object on the sheet "Outputs", so I use the:
..Charts(1).Location xlLocationAsObject, "Outputs"
This behaves strangely, and moves the chart to the "Inputs" page, but more disturbingly, removes the chart from the Charts collection. Thus Charts(1) now no longer exists.
So the question really is, (1) how do you refer to a chart object, that is an object on a sheet, and (2) why is the Location method moving the chart to the wrong sheet
