Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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!

    VB:
    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.

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