Using VBA to update chart series from an external source

[DaveWhiting]

I have a report that I have picked up from another user and I'm trying to avoid doing all the long-winded bits that take forever, like updating charts. The one bit I am having trouble with is that the source data from the charts comes from a different external source each week. I have come up with the following code, which works fine if the workbook with the current source data is already open, but comes up with Run-time error '1004' if it is not.

I am assuming the cause is that the chart wants to update after each change so is trying to update with reference to an unavailable source. I am already using a workaround in that I first use the macro to update the graph with the data it is currently using (thereby opening the workbook with the current source data), but at some point I am going to have to hand these tasks on and it may not always be clear where the graphs are getting the source data.

Anyway, I am hoping someone could help with one of two solutions:

1 - If I can find a way of getting the path and filename the chart is using I could use that to open the current source data
2 - If there is a way of telling it to update all the series at the same time, thereby not having it refer to an unavailable source

My first solution was to just copy and paste the data from the source file but I have been tasked to find another way!

Option Explicit 
Sub UpdateFPNCharts() 
    Dim ReportFile As String, FPNChartSource As String 
    Dim Col As Integer, SlashPos As Integer 
     ' Return the name of the open workbook as a string variable
    ReportFile = ActiveWorkbook.Name 
     ' Get the new file to open
    FPNChartSource = Application.GetOpenFilename(, , "Select FPN file to use...") 
    If FPNChartSource = "False" Then Exit Sub 
    Workbooks.Open (FPNChartSource) 
     ' Return the column number of the last column in the source data
    Col = Cells(1, 1).End(xlToRight).Column 
     ' Select the original file
    Windows(ReportFile).Activate 
     ' Get the filename and add an open square bracket for use in naming the source _
    For the chart data 
        SlashPos = InStrRev(FPNChartSource, "\", Len(FPNChartSource), vbTextCompare) 
        FPNChartSource = Mid(FPNChartSource, 1, SlashPos) & "[" & Mid(FPNChartSource, SlashPos + 1, Len(FPNChartSource)) 
        ActiveSheet.ChartObjects("Chart 259").Activate 
        ActiveChart.ChartArea.Select 
        ActiveChart.SeriesCollection(1).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(1).Values = _ 
        "='" & FPNChartSource & "]FPN'!R23C96:R23C" & Col 
        ActiveChart.SeriesCollection(1).Name = _ 
        "='" & FPNChartSource & "]FPN'!R23C1" 
        ActiveChart.SeriesCollection(2).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(2).Values = _ 
        "='" & FPNChartSource & "]FPN'!R16C96:R16C" & Col 
        ActiveChart.SeriesCollection(2).Name = _ 
        "='" & FPNChartSource & "]FPN'!R16C1" 
        ActiveChart.SeriesCollection(3).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(3).Values = _ 
        "='" & FPNChartSource & "]FPN'!R18C96:R18C" & Col 
        ActiveChart.SeriesCollection(3).Name = _ 
        "='" & FPNChartSource & "]FPN'!R18C1" 
        ActiveChart.SeriesCollection(4).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(4).Values = _ 
        "='" & FPNChartSource & "]FPN'!R20C96:R20C" & Col 
        ActiveChart.SeriesCollection(4).Name = _ 
        "='" & FPNChartSource & "]FPN'!R20C1" 
        ActiveSheet.ChartObjects("Chart 260").Activate 
        ActiveChart.ChartArea.Select 
        ActiveChart.SeriesCollection(1).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(1).Values = _ 
        "='" & FPNChartSource & "]FPN'!R9C96:R9C" & Col 
        ActiveChart.SeriesCollection(1).Name = _ 
        "='" & FPNChartSource & "]FPN'!R9C1" 
        ActiveChart.SeriesCollection(2).XValues = _ 
        "='" & FPNChartSource & "]FPN'!R2C96:R2C" & Col 
        ActiveChart.SeriesCollection(2).Values = _ 
        "='" & FPNChartSource & "]FPN'!R13C96:R13C" & Col 
        ActiveChart.SeriesCollection(2).Name = _ 
        "='" & FPNChartSource & "]FPN'!R13C1" 
    End Sub 

[DaveWhiting]

I have come up with a solution as below. It's not very pretty but it means that when a new data source is selected, the filepath is saved in a cell which is hidden behind one of the charts. The code then uses the filepath stored in this cell to open the current source file. I've also tidied it up a little and added slightly better comments. If anyone knows of a better solution, I'd be eager to learn another way.

Option Explicit 
Sub UpdateFPNCharts() 
    Dim strReportFile As String, strFPNChartSource As String, strCurrSource As String 
    Dim intCol As Integer, intSlashPos As Integer 
    Dim objCurrSource As Workbook 
     ' Return the name of the open workbook as a string variable
    strReportFile = ActiveWorkbook.Name 
    Windows(strReportFile).Activate 
     ' Open workbook named in cell B355 (This is hidden behind the chart)
    strCurrSource = Cells(355, 2) 
    Set objCurrSource = Workbooks.Open(strCurrSource) 
     ' Get the new file to open
    strFPNChartSource = Application.GetOpenFilename(, , "Select FPN file to use...") 
     ' Update cell B355 with the new source data FilePath
    Cells(355, 2) = strFPNChartSource 
    If strFPNChartSource = "False" Then Exit Sub 
    Workbooks.Open (strFPNChartSource) 
     ' Return the column number of the last column in the source data
    intCol = Cells(1, 1).End(xlToRight).Column 
     ' Select the original file
    Windows(strReportFile).Activate 
     ' Get the filename and add a preceding open square bracket for use in naming the source _
    For the chart data 
        intSlashPos = InStrRev(strFPNChartSource, "\", Len(strFPNChartSource), vbTextCompare) 
        strFPNChartSource = Mid(strFPNChartSource, 1, intSlashPos) & "[" & Mid(strFPNChartSource, intSlashPos + 1, Len(strFPNChartSource)) 
         ' Select chart
        ActiveSheet.ChartObjects("Chart 259").Activate 
        ActiveChart.ChartArea.Select 
         ' Update the series based on the new source data (Current data starts in Column 96, _
         '   this may change in future)
         '   Formulae formatted as  "='[SourceFileName]'R1C1:R2C2"
        With ActiveChart.SeriesCollection(1) 
            .XValues = "='" & strFPNChartSource & "]FPN'!R2C96:R2C" & intCol 
            .Values = "='" & strFPNChartSource & "]FPN'!R23C96:R23C" & intCol 
            .Name = "='" & strFPNChartSource & "]FPN'!R23C1" 
        End With 
        With ActiveChart.SeriesCollection(2) 
            .XValues = "='" & strFPNChartSource & "]FPN'!R2C96:R2C" & intCol 
            .Values = "='" & strFPNChartSource & "]FPN'!R16C96:R16C" & intCol 
            .Name = "='" & strFPNChartSource & "]FPN'!R16C1" 
        End With 
        With ActiveChart.SeriesCollection(3) 
            .XValues = "='" & strFPNChartSource & "]FPN'!R2C96:R2C" & intCol 
            .Values = "='" & strFPNChartSource & "]FPN'!R18C96:R18C" & intCol 
            .Name = "='" & strFPNChartSource & "]FPN'!R18C1" 
        End With 
        With ActiveChart.SeriesCollection(4) 
            .XValues = "='" & strFPNChartSource & "]FPN'!R2C96:R2C" & intCol 
            .Values = "='" & strFPNChartSource & "]FPN'!R20C96:R20C" & intCol 
            .Name = "='" & strFPNChartSource & "]FPN'!R20C1" 
        End With 
         
        ActiveSheet.ChartObjects("Chart 260").Activate 
        ActiveChart.ChartArea.Select 
        With ActiveChart.SeriesCollection(1) 
            .XValues = "='" & strFPNChartSource & "]FPN'!R2C96:R2C" & intCol 
            .Values = "='" & strFPNChartSource & "]FPN'!R9C96:R9C" & intCol 
            .Name = "='" & strFPNChartSource & "]FPN'!R9C1" 
        End With 
        With ActiveChart.SeriesCollection(2) 
            .XValues = "='" & strFPNChartSource & "]FPN'!R2C96:R2C" & intCol 
            .Values = "='" & strFPNChartSource & "]FPN'!R13C96:R13C" & intCol 
            .Name = "='" & strFPNChartSource & "]FPN'!R13C1" 
        End With 
        Set objCurrSource = Nothing 
    End Sub