Problem with Excel sheet embedded in Word resizing

[Adrian Jones]

Hi, I have a problem with an Excel workbook, embedded in Word, changing its
size, apparently at random.

I have an Excel 2007 object, embedded in a table in a Word 2007 template.

The Excel object consists of a worksheet and a chart.

I’m then using Access 2007 to create a document from this template,
populating other fields in the table and cells in the excel worksheet.

From Access I have a Word document object oDoc. I use this to pick up the
Excel worksheet (oWorksheet) with:

Set oShape =
oDoc.Bookmarks.Item(strChartBookmarks(“Chart_Nationalâ€)).Range.InlineShapes(1)
Set oleObject = oShape.OLEFormat
oleObject.Activate
Set oWorksheet = oleObject.Object.Worksheets("Data")

I then populate the cells with:

oWorksheet.Cells(row,col).value = aValue

All of this works, but with one problem. While the worksheet is being
populated, the object expands. So that it no-longer fits within the Word
table’s cell. I’ve tried resizing it back again, but changing the size of the
InlineShape:

oShape.Width = 170

just distorts the chart.

I’ve tried picking up the Excel object with:

Set oExcel = oleObject.Object.Parent
oExcel.ActiveChart.ChartArea.Width = 154

but this makes no difference at all.

Does anyone have any ideas as to how I can resize the Excel object back to
the correct size without it distorting? Or, better still, prevent it from
changing its size when data is entered in its worksheet?

I can manually re-size the objects, but there will be three in each document
and about 1,000 documents by the time the report run is finished, so not
really a practical option.

Thanks in advance,

Adrian