Updating/Format Objects

ptmuldoon · #1 01-21-2015, 01:32 PM

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 }

Now, I am thinking it may be possible to search the fieldcodes for the phrase 'Chart X' where X is the chart number. and if found, to resize the object height and width?

Guessed · #2 01-22-2015, 12:42 AM

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

gmayor · #3 01-22-2015, 12:54 AM

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

ptmuldoon · #4 01-22-2015, 06:19 AM

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.

Guessed · #5 01-22-2015, 08:39 PM

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.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

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