CODEWITHSUNDEEP
excelms-wordolevba
Xam Eseerts
Xam Eseerts

Reputation: 367

Vba: Edit charts in Word via Excel.Application

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

Upvotes: 1

Views: 2379

Answers (3)

Chris
Chris

Reputation: 221

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)

Upvotes: 0

Xam Eseerts
Xam Eseerts

Reputation: 367

@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?

Upvotes: 0

Cindy Meister
Cindy Meister

Reputation: 25663

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

Upvotes: 1

Related Questions