Excel graph with changing source data 2

[cbd]

At the moment I am exporting a query from Access97 to Excel97. The data is exported into a template with various graphs. Every works grand but my problem is that the number of records that get imported into the spreadsheet varies each time but the graph source data requires me select a fixed range, e.g.'10mg Data'!$P$2:$P$26. This normally results in either not all the data being represented on the graph or empty trailing cells being graphed. Is it possible to dynamically select the source data for graphs when the data is imported from Access

 

[xlbo]

If you are using code to extract the data from access, use
lRow =activesheet.range("A65536&quot.end(xlup).row
Activesheet.names.add Name:="chtRange", RefersTo:= _
"=Sheet1!A1" & lRow

Where chart data is in A

then use this name in your chart source data
HTH
Geoff

 

[AWithers]

You could also use .Selection.CurrentRegion.Select.

Which will select the entire block in which the cursore currently is (this is the same as using edit/goto/special/current region using the menu commands)

It may be of no use if you just want to graph a small part of the data block

Andy

 

[cbd]

Geoff, could possible explain your code in more detail. I am actually using Access to export the data to Excel. I am exporting the data to a sheet called "10mg" and I have a graph then on another sheet called "10mg Yield". Say the Category(X)axis labels is in column A in the sheet "10mg" and the values for the series is in column B in "10mg". Could you use this information to help explain your code.

Cheers,

Cormac

 

[SkipVought]

Hi,

What Goeff is referring to is Named Ranges. He has given you a code segment for giving a range a Name. If you use that Name in your Source Data definition, then, whenever your data range changes, and you run that code, the Name adjusts to the range and your graph will point to that range.

Now I might do it a bit differently. I am assuming that your data has headings in row 1. I like to use (and record for programatic use) Insert/Name/Create/Create names in top row. Now each column of data has the name that is in the heading at the top of that column. Record this and use it in step 5 following. Here's how I recorded and modified it...

Sub AdjustNameRanges()
    Application.DisplayAlerts = False
    [A1].CurrentRegion.CreateNames _
        Top:=True, Left:=False, Bottom:=False, Right:=False
    Application.DisplayAlerts = True
End Sub

2. Right click the chart and select Source Data... for the popup.

3. On the Series Tab, for each series, replace the Values: with...
=
then click on the sheet tab
then type the column name in Values:
repeat for each series

4. For Category X labels do the same thing using the column name for your x axis values

5. Now you are all set. Add a few rows to the source data. Run the macro you recorede in step 1 and VOLA!

Hope this helps Skip,
metzgsk@voughtaircraft.com

 

[SaintAugustine]

I gave you a star, Withers, because the actually solves another problem I had