Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: Using VBA to update chart series from an external source

  1. #1
    Join Date
    17th August 2011
    Posts
    10

    Using VBA to update chart series from an external source

    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!

    Code:
     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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th August 2011
    Posts
    10

    Re: Using VBA to update chart series from an external source

    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.

    Code:
    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Chart Series Data Source From Another Sheet
    By giloopp in forum EXCEL HELP
    Replies: 1
    Last Post: June 30th, 2008, 01:46
  2. Replies: 1
    Last Post: April 19th, 2008, 10:22
  3. Replies: 3
    Last Post: October 19th, 2007, 21:54
  4. Each Chart Source Row as a New Series
    By fire in forum EXCEL HELP
    Replies: 11
    Last Post: December 15th, 2006, 14:07

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno