views:

392

answers:

3

Hi Folks,

I am trying to use a VBA macro (for Office 2003) to do the following:

  1. In Excel, the user will select a range of cells
  2. In Word the user will call the macro (via a button or shortcut) to insert the selected Excel range as an embedded object

The code is not the problem so far, my problems are:

  • Given that the user is working in a Word document, most likely will use the same fonts in Excel
  • When Excel range was inserted in Word and they both use the same font names and sizes, they look different inside Word ( fonts look as if they stretched a bit)
  • Styling Cell borders in Excel is not like styling cell borders in Word

I do appreciate any advice on this regards

A: 

Is it possible to have the macro creating a table out of the cells (as normally happens when you manually copy them), rather than inserting an Excel Object.

CodeSlave
A: 

When you paste as an Excel Worksheet Object, what Word is actually displaying is an image created by Excel. Notice that you can't select any text, for example. Word appears to be distorting the image ever so slightly, so that the fonts won't line up.

Edit: I can't speak for Office 2003, but Office 2007 defaults to the HTML format using PasteExcelTable. The exact macro statement is

Selection.PasteExcelTable False, False, False

This will give formatting that is compatible with Word, but unfortunately the data is not live and won't get updated as the spreadsheet is changed. If your requirements don't include live update, try this method.

Mark Ransom
A: 

The best visual results results can be achieved by using Selection.CopyPicture(Appearance, Format); however, you will not be able to edit the data inside Word as you will only get a picture.

0xA3