#1
|
|||
|
|||
Updating/Format Objects
In my report document, I have macro to update all the OLE links to a new file. Now after it updates, I always need to go back to 4 links that are linking to graphs/charts in Excel and resize them. And those link codes look slightly different with the 'Chart number' included with the field code. A sample of one of those graphs/links in word looks like this:
Code:
{ LINK Excel.SheetMacroEnabled.12 C:\\Data\\AnyFileNameTest.xlsm "Receivable Charts![AnyFileNameTest.xlsm]Receivable Charts Chart 4" \p } |
#2
|
||||
|
||||
It would be a lot easier to resize the chart original in Excel. If the size is important, you may be able to duplicate the chart on a hidden worksheet in the same workbook where you can resize the chart and then use that version to paste into Word.
Assuming that you can't do that, the next easiest option would be to bookmark each of the graphics so you don't need to query the field code values. If this was done, the following code could be adapted to work. Dim aPic As InlineShape Set aPic = ActiveDocument.Bookmarks("Chart1").Range.InlineSha pes(1) With aPic .Width = 400 .Height = 200 End With |
#3
|
||||
|
||||
You should be able to do that e.g. by using a sub which you can call for each chart to set the relative size to what is required.
Code:
Sub Macro1() ScaleLinkedChart "Chart 4", 75 End Sub Sub ScaleLinkedChart(strChart As String, lng_Size As Long) 'strChart is the chart name e.g. 'Chart 4' 'lng_size is the percentage of the original size e.g. 75 = 75% Dim ofld As Field Dim oShape As InlineShape For Each ofld In ActiveDocument.Fields If ofld.Type = wdFieldLink Then If InStr(1, ofld.Code, strChart) > 0 Then Set oShape = ofld.InlineShape oShape.ScaleHeight = lng_Size oShape.ScaleWidth = lng_Size Exit For End If End If Next ofld lbl_Exit: Set ofld = Nothing Set oShape = Nothing Exit Sub End Sub
__________________
Graham Mayor - MS MVP (Word) (2002-2019) Visit my web site for more programming tips and ready made processes www.gmayor.com |
#4
|
|||
|
|||
Thanks guys.
I'm going to experiment with both approaches later today. I've found that no matter how much I change the chart sizes in excel, that when I update them in Word, they never quite look the same. I'll try out both solutions and report back later tonight. |
#5
|
||||
|
||||
Another solution which could also work and avoids the need for macros is to place the graphics inside a table cell. If the table cell is set to a width smaller than the native size of the chart, it will automatically resize to fit the table cell width.
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Can't add ActiveX Objects. | MSOfficeGuy | PowerPoint | 5 | 12-14-2014 07:37 AM |
Format Painter doesn't work on Table Format? | Joey Cheung | Word Tables | 1 | 08-20-2014 02:24 AM |
Convert equation objects to inline objects | sumjoh | Word VBA | 1 | 01-29-2013 08:38 PM |
Shape Objects | Space Cowboy | PowerPoint | 4 | 10-04-2010 07:14 PM |
Updating excel link alters table format in PP | dbmagnus | PowerPoint | 0 | 06-03-2010 12:12 PM |