Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Need Excel VBA to cycle through chart objects and change data source

    Hello - I have an Excel 2010 file that has several worksheets in it. Each worksheet has several bar, pie or column charts on them with the data source being the 2011DataPull worksheet. I need a macro to cycle through the sheets and change the data source of each chart or graph etc. to the 2012DataPull worksheet, the ranges will be the same - i just need the charts to point to a different worksheet.

    for each worksheet in workbook
    change the data range of each chart object from
    ='2011DataPull'!...
    to = '2012DataPull'!...
    next worksheet

    Any help is greatly appreciated!

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    hi jha,

    You may not need a macro - a simple Find/Replace might do the job. if the objects are in-line, simply press Alt-F9, then use the before/after filenames as the Find/Replace expressions. Then Alt-F9, Ctrl-A, F9 should update the display.
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    I've tried Alt-F9 keys but nothing happens. When I use Replace and enter 2011DataPull for find and enter 2012DataPull in the replace box and I select workbook, it doesn't search within the data source for each bar chart or pie chart in the workbook. It only changes my paste links not the data source of the chart objects.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Sorry jha - the Alt-F9 applies to Word. That said, a Find/Replace that changes 2011DataPull to 2012DataPull change the data links in all cells. Are you saying, though, that the charts are directly referencing cells in an external workbook, rather than referencing cells in the active workbook?
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have about 20 charts on 7 different sheets that reference the 2011DataPull worksheet within the same excel file. I need to change them all to reference a new worksheet (but the exact same ranges) named 2012DataPull. Thanks for the help - Joan

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts
    Hi Joan,

    Try:
    Code:
    Sub Demo()
    Dim wkSht As Worksheet, Obj As OLEObject
    For Each wkSht In ThisWorkbook
      For Each Obj In wkSht.OLEObjects
        If Obj.OLEType = xlOLELink Then
          Obj.SourceName = Replace(SourceName, "2011DataPull", "2012DataPull")
        End If
      Next
    Next
    End Sub
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  7. #7
    New Lounger
    Join Date
    Feb 2011
    Posts
    14
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Possibly easier longterm approach?

    If at any time you were thinking of redeveloping this workbook, I'd be tempted to look at restructuring it to bring in the data from the DataPull file on a single SourceData sheet (using links if you like) and to build the charts from there. That way the charts aren't referencing anything outside their home file, which should make it easier to see and maintain what they are looking at. and you could change the data source simply by editing the link source.

Tags for this Thread

Posting Permissions

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