merging cell values from excel

[Lisa]

Hello,

I am having problems getting some exact cell values to
merge from cells in excel into a word document (Word
2002). The formula for the cells in question are:
=(formula1)&"Years" & " " & (formula2)& "Months"
which shows in the cells as "2 Years 3 Months" etc. I
sucessfully used currency/number/date switches for other
cells but can't get these particular cells to merge
correctly.

I have tried to follow instructions from Word Help which
tells me to select the data source as MS Excel Worksheets
via DDE (*.xls). Once I select this option in the
dialogue box a second box asks me to select "Named or
Cell Range:". The only option given to me is for "Entire
Spreadsheet" which when selected merges the 1st worksheet
only. I need to merge from the 2nd worksheet - I could
do this when I used the default data source of OLE DB
Database Files. How do I merge a particualr worksheet
using DDE? Or any other ideas?

Hope that makes sense!

Many Thanks,

Lisa

 

[Peter Jamieson]

Unfortunately, DDE does not let you access the 2nd worksheet.

I tried a (very) simple experiment using your formula and an OLEDB
connection and had no problems and wondered
a. what problem you were seeing
b. whether it makes any difference if the Excel column's cell formats are
set to "Text"

The only other idea is essentially to freeze the column values before
merging (one way would be to use Save As to save a copy of the spreadsheet,
select the column, then use Edit|Copy, Edit|Paste Special and select
Paste|Values. If you still find that you need to use DDE, you could freeze
the whole sheet, remove all the other sheets, and mergefrom that). A pain,
but perhaps less painful than other approaches.