1

I have a straight forward problem: I want to edit all charts in a word document. More precisely I want to perform a search and replace in all charts (their data to be even more precise). Now my approach so far is to do something like this:

Dim appExcel as Excel.Application
Dim wb as Excel.Workbook
Dim ws as Excel.Worksheet
Dim shp as InlineShape
Dim cht as Word.Chart

For each shp in ActiveDocument.InlineShapes
  If shp.HasChart then
    set cht = shp.Chart
    'Here comes the Question: how to assign the chartdata.workbook to wb?
  end if
next shp

Does anybody have an idea? I would be so appreciative! Thanks :)

Improve this question
3
  • have you even tried to do this manually? Try recording a macro and seeing what you get? Commented Jun 20, 2016 at 20:05
  • @ScottHoltzman Don't ask "why?", rather ask "why not?" It's possible there are more charts than is practical for using macros. Commented Jun 20, 2016 at 20:42
  • Hi Scott, macro recording is no good in this case because it doesn't cover actions you do in the datasheet-window (or whatever one calls that). That's because editing the data takes place in an excel application which the word macro recorder doesn't cover. Commented Jun 21, 2016 at 21:36

3 Answers 3

Reset to default
1

To answer the question you've typed as a comment in your code: Just use Set wb = cht.chartdata.workbook

To answer the implied question in the text - "search and replace data" - you can access the underlying chart data through the object model. The key if you need to do any resizing is that the data is in a ListObject (Excel table). Here's a bit of sample code I have that drills down to the cells in the data:

Set ils = ActiveDocument.InlineShapes(index)
Set c = ils.Chart
Set wb = c.ChartData.Workbook
Set ws = wb.Worksheets(1)
Set lo = ws.ListObjects(1)
lo.Resize wb.Application.Range("A1:D7")
ws.Cells(6, 1).value = "New category"
ws.Cells(6, 2).value = 6.8
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Hey Cindy thanks for your answer! Please check out the answer I supplied from my other account and let me know your thoughts! Best regards, Max
0

@CindyMeister: Hey this is the original poster from a different account. Thanks for your answer! However I did try your approach already and it doesn't seem to work for my office installation, nor does it for any other I have tried (I use Office 2013 on a win10 pc myself, I've tried to run the code on machines running Office 2010/2013 on win7/win10). The problem arises whenever you try to run the code you supplied more then once on the same chart. What happens then is you get the following runtime error (probably not an entirely correct translation from German here): the method 'workbook' for the object 'chartdata' has failed runtimeerror -2147467259 (80004005). As I said, this problem only appears after the first run. So your code does run, but only once! That said, I have found a solution that seems almost to easy which works and even runs much faster on my machine. Here it is:

Dim strA As String: strA = "Search"
Dim strb As String: strb = "Replace"

Dim cht As Word.Chart
Dim doc As Word.Document
Dim ils As Word.InlineShape

Set doc = ActiveDocument
Set ils = doc.InlineShapes(1)
Set cht = ils.Chart

If cht.ChartData.Workbook.Sheets(1).Cells(1, 2) = strA Then
    cht.ChartData.Workbook.Sheets(1).Cells.Replace What:=strA, Replacement:=strb, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
Else
    cht.ChartData.Workbook.Sheets(1).Cells.Replace What:=strb, Replacement:=strA, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False
End If

cht.ChartData.Workbook.Close

cht.Refresh

Set cht = Nothing
Set ils = Nothing

So as long as you do not assign chartdata.workbook to an Excel.Workbook variable but instead use chartdata.workbook directly you are fine. This is tested on multiple machines all running office 2013 and either win10 or win7. It doesnt work with office 2010 or sooner though. I didn't have the chance to test it on a machine running office 2016 yet unfortunately.

Can you make anything of this?

Improve this answer

3 Comments

There remains one problem I can not figure out. I have to declare an Excel.Range in order to do stuff I have to do. Now as soon as I expand my code to cover that functionality (with something like a set rng = ...) the same problem I described above appears. So it seems you cannot set excel-objects if you want to run some code on chartdata.workbook more then once.
Edit: I did run it on a machine with office 2016 and it works. Also I wasnt entirely correct when I said you are fine as long as you dont use something like set Excel.Workbook = chartdata.workbook. This you can do, but as soon as you do something with your Excel.Workbook you get problems. I have found a not so elegant workaround for using an Excel.Range but still this behavior seems very odd to me.
In the if, 95% of the code is duplicated. Please, extract two variables to set strA or b and use them as arguments in a single replace, after the if.
0

You first need to Activate the Word Chart object, this should work

    Dim oWordChartAsInlineShape As InlineShape
    Set oWordChartAsInlineShape = oDocument.InlineShapes(1)

    Dim oWordChart As Word.Chart
    Set oWordChart = oWordChartAsInlineShape.Chart

    oWordChart.ChartData.Activate

    Dim oChartWorkbook As Excel.Workbook
    Set oChartWorkbook = oWordChart.ChartData.Workbook

    Debug.Print oWordChart.ChartData.Workbook.Sheets(1).Cells(1, 1)
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.