Macro to return the height and width of an object in the clipboard

[Nicky]

I would like a macro that returns the width and height of an excel chart copied as a picture to the clipboard.

The reason I want to do this is that I want to paste pictures into textboxes in Word and ensure consistency of appearance. So, I’ll set the width of each textbox to a full text column width, adjust the pasted picture’s width to slightly less than the width of the box (maintaining aspect ratio so it doesn’t distort), then set the height of the textbox to a little bit more than the new height of the picture inside it.

So, in order to set the textbox’s size I’d like to know the dimensions of the picture to go inside it before it’s pasted.

Apologies if this looks more like a Word than Excel question

[Andy Pope]

Not sure you can get the dimensions once on the clipboard.

If you know what object was copied you can get the dimensions of the object.

When you say paste into textboxes do you actually mean place the pasted chart over the area occupied by the textbox? If so can you not just resize the pasted object in Word?

[Nicky]

Thanks Andy

I want a macro to standardise the appearance of charts in documents with lots of them: the same width of charts in a textbox, the textbox taking the full width of the column but no more. I also want to apply standard wrapping, positioning, margins, captions etc to the textboxes.

Because I have several staff contributing to documents, with varying Excel and Word capabilities, a single macro to do all these things will make life much easier in standardising documents’ appearance.

I have already created a macro to do this for one set of documents we produce using templates which fix the column width in Word and chart size in Excel.

However, other Word document templates we use have different margins and import charts with different dimensions.

In order to create a similar macro, I was hoping to

• set the textbox width equal to the text column width, resize the chart’s width so it just fits in the textbox (but keeping the aspect ratio fixed so it doesn’t distort),

• then set the textbox’s height to accommodate the height of the chart plus space for the caption. This is the point at which I need the chart’s height and width, or at least their ratio.

I could resize the textbox and chart after pasting, but have found that gets messy if the source chart is much smaller or larger than the textbox it’s pasted into.

[Andy Pope]

So if your code is copying the chart to the clipboard you can determine the dimensions of the chart object, yes?

[Nicky]

Hi Andy

I hadn't thought if that - I've been using one set of macros to create, format and copy the charts in Excel, another in Word to paste the charts and create and format the textboxes. It'll be a bit complicated (for my capabilities) writing a macro in one application to run actions in another, but I've done it before. I'll give it a try and let you know how I get on.

thank you for your help.