Chart Source data in a closed Workbook

[Guest]

Hi,

Programmatically, how would i refer to the Chart Source data that is in
another Workbook please?

Currently i have:
ActiveChart.SetSourceData
Source:=Workbooks("Cases_Older_Than_One_Year.xls").Sheets("Charts").Range("A3:" & Mycol & "6"), PlotBy:=xlRows

When Cases_Older_Than_One_Year.xls is open it works fine (as i would expect)
but i would rather not have to have this WB open at this point in the macro.

Thanks

John

 

[Kostas]

The following code is an extract from a program that was extensively
used for auto-assigning ranges to plots. All you need to do is toggle
between target and source file to get the data.

Also, please remember that the code is highly dependent on the file
layout. Please make the necessary change when targeting various sheets
& files.

Have fun





Public Current_Data_Worksheet
Public Current_Plot_Worksheet
Public Current_chart_number
Public current_file_name

Sub v_vs_lnp_plot_range_generation()
Dim ROW_START As Integer

'setting up parameters

ROW_START = 14 ' row at which last data row loop will start
Current_Data_Worksheet = "Step 2 - IsoCompression" ' worksheet at
which plot data are stored
Current_Plot_Worksheet = "v vs lnp" ' worksheet at which data is
stored
X1_COLUMN = 22
Y1_COLUMN = 18
'X2_COLUMN
'Y2_COLUMN
'X3_COLUMN
'Y3_COLUMN









current_file_name = ActiveWorkbook.Name

Windows(current_file_name).Activate
Worksheets(Current_Data_Worksheet).Activate

Call Previous_Range_deletion_v_vs_lnp


'calculating last data row

Worksheets(Current_Data_Worksheet).Activate
Range("t5").Select


Dim CurrentRow As Integer 'Current row being dealt with

CurrentRow = ROW_START 'Start on first row of data


'Find end row
While Cells(CurrentRow, COLUMN_TIME).Value <> "" 'While the cell
isn't blank

CurrentRow = CurrentRow + 1 'Move one cell down

Wend

row_end = CurrentRow - 1 'Note last data row










'graph range loop

Sheets(Current_Plot_Worksheet).Select
ActiveChart.ChartArea.Select




'constructiong new set of data ranges


Set Site_Name = Worksheets("Initially").Cells(2, 2)
Set Experiment_date = Worksheets("Initially").Cells(3, 2)
Set Approximate_Sample_Depth = Worksheets("Initially").Cells(5,
5)
Set sample_state = Worksheets("Initially").Cells(4, 5)


Xvalue = "='" & Current_Data_Worksheet & "'!R13C" & X1_COLUMN &
":R" & row_end & "C" & X1_COLUMN

Yvalue = "='" & Current_Data_Worksheet & "'!R13C" & Y1_COLUMN &
":R" & row_end & "C" & Y1_COLUMN

Legend_name = sample_state & " - " & Site_Name & " - " &
Approximate_Sample_Depth & "m - " & Experiment_date

Debug.Print Xvalue
Debug.Print Yvalue
Debug.Print Legend_name

ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = Xvalue
ActiveChart.SeriesCollection(1).Values = Yvalue
ActiveChart.SeriesCollection(1).Name = Legend_name




' Worksheets(Current_Data_Worksheet).Activate




End Sub

Sub Previous_Range_deletion_v_vs_lnp()

Dim count As Integer


Sheets(Current_Plot_Worksheet).Select
ActiveChart.ChartArea.Select



'deliting previous graph ranges

i = ActiveChart.SeriesCollection.count

Do While i > 0

ActiveChart.SeriesCollection(1).Delete

i = i - 1

Loop




End Sub

 

[Jon Peltier]

Where is the closed workbook that the OP asked about?

A workbook must be open for a chart to change a reference to a worksheet
range within that workbook. An existing reference to a closed workbook is
fine, as long as the reference is not edited.

- Jon