Managing charts in excel workbook created from Access

  • Thread starter ragtopcaddy via AccessMonster.com
  • Start date

R

ragtopcaddy via AccessMonster.com

I have a template.xls file that contains one "form" (actually a range, A1:
AE28, formatted for my data) for displaying store data. This "form" includes
8 small charts that have datasources on the same range as the charts. I can
easily determine the datasource range for each of the 8 charts, but am having
difficulty assigning the datasource to the correct chart due to the
difficulty in identifying, from my code, which chart goes with which
datasource.

This is only the beginning of the problem. What my Access code does, is it
opens the "template.xls" and saves it as "current.xls". Then I copy the
"form", including the charts, and paste it below the original. I then
populate the original, at the top of the worksheet, with the store data. Then
I copy the empty copy below the form I've just populated, paste it again
under the empty form I just copied, and proceed to populate the second form.
Then I copy the 3rd form and paste it underneath again, and so on, and so on,
until all 900+ stores have forms, each containing 8 small charts, with source
data in ranges to their right.

I hope this makes sense to someone who can give me some guidance. I've
learned that the sourcedata is a property of the chart, which is a property
of the chartobject in the chartobjects collection of the worksheet, but have
not been able to concisely identify the location of each chart so I can
supply the corresponding source data ranges. As it is, when I copy the charts,
they retain their original source data at the top of the worksheet. I need to
change that to reflect the ranges to the right of the charts as I copy and
paste them in the worksheet.

Here's a code sample I tried to use to rename the chartobjects, but I don't
know if the chartobjects are listed in the collection in the same order they
appear on the worksheet:

Function DeleteCharts(x As Integer) As String
'renames charts to reflect actual # in worksheet
Dim CO As ChartObject
Dim n As Integer

For Each CO In Sheets("StoreReports").ChartObjects
n = n + 1
CO.Name = "ChObj " & n
Next CO

End Function
 
Ad

Advertisements

J

Jeanette Cunningham

You will get a much better answer if you ask this in the excel discussion
group.

Jeanette Cunningham
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top