WORD Mailmerge with Excel DataSource - loss of formatting

[LabrGuy Bob R]

Hello, I touched on this subject before but haven't gotten a solution. I am
using Excel and Word 2003 with Win XP Pro.
I am using a database that is in Excel and consists of various fields of
text, numbers, currency and dates/times.

My issue is when I have this data in excel, I've formatted the cells to
display the format that I want, i.e. currency/currency, date/date, time/time
etc.
When I link the mail merge document to the data base, some of these cells
display as they are formatted in Excel, some don't. For those that do I've
used some switches on the WORD document for specific formatting. The trouble
is some of them work great, and the information is exact, others (No matter
what switch in WORD is used, no matter if I format it as text in the excel
database, etc) give me a single "0". I've tried re-inserting the fields
other places and there's no change. This issue is consistent with all my
worksheets.

I am at wits end and don't have an answer.
Is there something that I might be missing to solve this problem, is there a
limit on specific data types to use, is there a way to force things? is
there a special problem with results of formulas?? OR can anyone think of
something that will work. I've got cells with the same kind of data, in the
same merge document, showing correct data & my zero's.

Any help will be greatly appreciated.
Thank you
Bob Reynolds

 

[Peter Jamieson]

Hi Bob,

To recap, previously we discussed two things:
a. the formatting, and using DDE to connect to try to solve that problem
b. programmatic connection using DDE.

Did (a) work? If so, did (b) fail? Or what? (and if possible, why?)

Does this have to work for any old spreadsheet, or just specific
spreadsheets?

If it's specific spreadsheets, do any of them have more than 63 columns? Do
all the users who need this solution have Excel on their machines?

Peter Jamieson

 

[LabrGuy Bob R]

Thanks for asking Peter,
the code in the beginning is binding the code over ok with word, and as I
said I have all but about 5 or 6 of these that won't work, no matter what.
This is a specific spreadsheet and it has a twin in the workbook for more
information..
All users have the same version of Excel on their machines...

Now for the poss problem if I'm reading you right. The first database has
174 columns and the second one has 186 columns. Is this a problem or where
it's at????
Thanks again
BOB

 

[Peter Jamieson]

Hi Bob,

Now for the poss problem if I'm reading you right. The first database has
174 columns and the second one has 186 columns. Is this a problem or where
it's at????

In fact I asked the question because the next thing I was going to suggest
was automated cut/paste into a Word table and using that as a data source
instead, but the column count makes a difference to how workable that is
(Word's table column limit is 63, so any more than that and you have to
paste as plain text which means that you will get problems with things like
double quotes, multiline text and delimiter dialog boxes)

So I hadn't even considered the column count being the /cause/ of the
problem. It isn't a problem I've seen before but I never work with that many
columns.

I suppose the obvious place to start is to experiment by cutting columns
from the larger sheet.

Peter Jamieson

 

[BobR]

Thanks Peter,
at any given time only about 15%-25% of either of the database's are being
used. I'm just confusted why some are working and others are not. I've gone
so far as to remove the column and place the information somewhere else in
the database in another column and that didn't work. I got two of the merges
to work by 1. removing the field 2. shutting down the excel workbook and
then restarting it 3. placing the field back into the database in all caps.
I don't seen anything that I did to cause the merge to work, but it did for
two of the fields.....
that's the frustrating part..

Phase two of the question: Scenario I trigger several word documents to open
and find the data source as I've explained before. They are still
functioning but I also need to simply open a word document from within excel
and not do a merge. So What I'm looking for is a simple little piece of code
to trigger in excel and simply open an existing word document (nothing to do
with a database)
Wouoldn't have an idea would you..

Thanks so much and if I ever find out what's causing this I'll re-post to
here for everyone......
Bob Reynolds

 

[Peter Jamieson]

Hi Bob,

Yes, it is sometimes difficult to see any pattern in situations like this,
particularly with a product like Excel where the formatting of a cell may
affect how Word sees the data.

So What I'm looking for is a simple little piece of code to trigger in
excel and simply open an existing word document (nothing to do with a
database)
Wouoldn't have an idea would you..

There's an example at
http://word.mvps.org/FAQs/InterDev/ControlWordFromXL.htm .

Derived from that you need something like the following (not tested).

Sub ControlWordFromXL()
Dim bWordWasNotRunning As Boolean
Dim objWord As Word.Application
Dim objDoc As Word.Document

'Get existing instance of Word if it's open; otherwise create a new one

On Error Resume Next

Set objWord = GetObject(, "Word.Application")
If Err Then
Set objWord = New Word.Application
bWordWasNotRunning = True
End If

On Error GoTo Err_Handler

objWord.Visible = True
objWord.Activate
Set objDoc = objWord.Documents.Open _
Name:="the full path name of the .doc you want to open"

Set objWord = Nothing
Set objDoc = Nothing

'quit
Exit Sub

Err_Handler:
MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error:
" _
& Err.Number
If bWordWasNotRunning Then
objWord.Quit
End If

End Sub