Data Source Location Change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Randy - 20 Jun 2006 19:26 GMT

I have a word merge (word 2002) linked to an excel file (data source) with a
macro. I need to change the location of the data source. When I do this some
of the formatting changes in the merge doc. For example; several field in the
data source are currency formated to two decimal places. Works great as long
as I do not change the data source location. Once I change the data source
location the formatting on the merge doc changes to no decimals.

Any ideas as to why this may be happening? Any assistance will be greatly
appreaciated. I thank you in advance for help or ideas you may be able to
provide.

Signature

Randy Street
Rancho Cucamonga, CA

Reply to this Message

Peter Jamieson - 20 Jun 2006 19:38 GMT

Can you provide the macro code you use to open the data source (assuming
that is what your macro does)? The most likely reason for this is that you
have set up a connection using one method (e.g. DDE, which would open a copy
of Excel) and your code opens using another method (probably OLEDB).

Peter Jamieson

>I have a word merge (word 2002) linked to an excel file (data source) with
>a
[quoted text clipped - 10 lines]
> appreaciated. I thank you in advance for help or ideas you may be able to
> provide.

Reply to this Message

Randy - 20 Jun 2006 22:16 GMT

Here is the macro code that actually opens the merge doc...

Sub MyMacro()
Documents.Open FileName:="S:\Yardi45\3 Day Merge.doc"
With Documents("3 Day Merge.doc").MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
Documents("3 Day Merge.doc").Close _
SaveChanges:=wdDoNotSaveChanges
End Sub

Signature

Randy Street
Rancho Cucamonga, CA

> Can you provide the macro code you use to open the data source (assuming
> that is what your macro does)? The most likely reason for this is that you
[quoted text clipped - 17 lines]
> > appreaciated. I thank you in advance for help or ideas you may be able to
> > provide.

Reply to this Message

Randy - 20 Jun 2006 23:52 GMT

Signature

Randy Street
Rancho Cucamonga, CA

> Can you provide the macro code you use to open the data source (assuming
> that is what your macro does)? The most likely reason for this is that you
[quoted text clipped - 19 lines]
>
> Ooops...I don't think I provided you what you were asking...let me get back to you on that one...

Is there another way to link to an excel spreadsheet other than the merge
wizard?

Reply to this Message

Peter Jamieson - 21 Jun 2006 00:30 GMT

You can use Word VBA's OpenDataSource method. The connection will be made
using DDE, OLEDB, or ODBC depending on the parameters you provide. Beyond
that, I've run out of time for now so maybe you could use Google Groups to
search this group for examples, e.g. using "Jamieson OpenDataSource xls" or
some such.

Peter Jamieson

>> Can you provide the macro code you use to open the data source (assuming
>> that is what your macro does)? The most likely reason for this is that
[quoted text clipped - 30 lines]
> Is there another way to link to an excel spreadsheet other than the merge
> wizard?

Reply to this Message