Insert and link shapes from Excel to Word that updates

[Johan90]

Hello!

So I have been working some in vba in Excel but now I need to make a more automated report in Word and cant really get a hang of updating shapes.


So for example on the front page I want a normal rectangular shape with text. the text needs to be linked to either a cell or the original shape in an Excel file. I have tried to copy paste the shape as an graphic object and run this macro but that dosent work. I found that I cant paste a shape as a link.

I would have thought this thing would have been easier sine it should be fairly common that someone needs to link stuff from excel etc



This code is what I currently have for updating text and graphs.


Sub UpdateText()'

ThisDocument.InlineShapes(1).LinkFormat.Update


ActiveDocument.Fields.Update


On Error Resume Next


Dim iShp As Word.InlineShape
For Each iShp In ActiveDocument.InlineShapes
iShp.LinkFormat.Update
Next

End Sub



Next problem:

I also have a chart that I run a macro on and format the colors on (a wind rose). The simple update macro dont work which I assumed it wouldnt since it is the formatting that changes and not the values. Any idea how I get the formatting to update?

[macropod]

Is the problem object formatted as inline or wrapped? Is it set to auto-update? Do you have Word configured to 'update automatic links at open'?

[Johan90]

Ok I think the shapes with text would be wrapped, as in floating? And, the wind rose chart would be inline.

For the first they are just normal rectangular shapes were the text is linked to a cell. The second problem object is just an inserted doughnut chart with (a few too many rings haha), I then wrote code to color all the separate sections to make a wind rose. So its a chart with static figures but one that gets its color reformatted.

Another way to put it, can Word auto update a charts color formatting on a chart that is linked to Excel?

[macropod]

Another way to put it, can Word auto update a charts color formatting on a chart that is linked to Excel?
All the formatting would be inherited from Excel. If you try applying formatting from within Word, it will be lost every time the link is refreshed.

[Johan90]

Ok I think managed to click around to a solution for the formatting issue. I used the paste as link and as a bitmap, it updates new formatting and the actual values for the graph.


But I still have no clue about getting a shape to update its text? When I go into the paste menu in word the paste as link option is closed.

[macropod]

If the entire chart is linked, including any legends, the text is inherited from the link; you can't update/edit it independently.

[Johan90]

The legends update fine in the linked charts, but I also I want to link separate shapes, for instance a front page title.


Or can I link text from the document to the head title shape? Kind of how you do in Excel by typing in a cell reference in the shape.

[macropod]

also I want to link separate shapes, for instance a front page title.
And where is that - in the workbook or in the document?

[Johan90]

In the document. The idea is to have a workbook that runs some calculations and imports some data that then can be linked into Word and printed from there. I found that it is easier to make the formal layout in word.

[macropod]

Linking within a Word document is done via bookmarks & cross-references. You can't bookmark part of a linked object, so you also can't cross-reference it; you would need to create another link to the relevant data in the source. You can however, bookmark an entire linked object and insert a cross-reference to the bookmark, so that the linked object appears in multiple locations.

[Johan90]

Do you know a guide or something that can walk me through the process? I am very unfamiliar with the Word enviroment and have limited knowledge of working in it

[macropod]

See, for example: http://www.docs.is.ed.ac.uk/skills/documents/3617/3617.pdf