DataSource in the folder of the main document

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

George Lagger - 21 Jan 2005 10:31 GMT

My main document often migrates from one location to another along with the
source Excel file. While merging it keeps remembering an old path to the
source. So it either finds the file with wrong data or doesn't find it at
all. Can I by any means make it look for the data source file in the same
place the main document resides?
(using Office 2003)

Reply to this Message

Peter Jamieson - 24 Jan 2005 12:26 GMT

I don't believe there is a way to do this without resorting to a macro,
which would probably mean that
a. your document would need to be saved as a non-mailmerge document
b. the macro would need to set the document to be a mailmerge document of
the type you needed, then
c. open the data source (this may be ewasy or difficult depending on the
version fo word, the data source etc.
d. restore any sort/filter options you might need. Again, this may or may
not be straightforward depending on what options you have set

In short, if you are comfortable with macros, it may be feasible. If you are
not, it's probably simpler and more reliable to reconnect the data source by
hand.

Peter Jamieson

> My main document often migrates from one location to another along with
> the
[quoted text clipped - 3 lines]
> place the main document resides?
> (using Office 2003)

Reply to this Message

George Lagger - 25 Jan 2005 10:09 GMT

Thank you for your guideline. I've done my macro homework since no one of my
colleagues would reconnect the data source by hand and single-handed. And
there is one more question:
While opening the source with the macro, a user has to go through the same
dialogue as if doing it by hand, e.g. he has to choose a sheet in the excel
file containing data. Is there a way to make macro do it in 'silent' mode,
eliminating all the dialogue with a user?

PS: again, it's word 2003

> I don't believe there is a way to do this without resorting to a macro,
> which would probably mean that
[quoted text clipped - 19 lines]
> > place the main document resides?
> > (using Office 2003)

Reply to this Message

Peter Jamieson - 25 Jan 2005 18:03 GMT

As long as you know the sheet name, you should be able to specify it in the
SQLStatement. For example, the following OpenDataSource call should open the
first sheet in a typical Excel workbook where none of the sheets has been
renamed:

ActiveDocument.MailMerge.OpenDataSource _
Name:= "C:\Documents and Settings\myname\My Documents\My Data
Sources\kt.xls", _
Connection:= "", _
SQLStatement:="SELECT * FROM `Sheet1$`"

(Notice that you do not usually need anything in the Connection parameter,
and that if you found out what the OpenDataSource should look like by
recording a macro, the Connection parameter that Word records is typically
invalid as it is truncated. It may still work, though). Also, you don't
usually seem to need the SubType parameter, although wdMergeSubTypeAccess is
the one Word records).

If you do that, Word should not prompt the user for the sheet name.

Peter Jamieson

> Thank you for your guideline. I've done my macro homework since no one of
> my
[quoted text clipped - 40 lines]
>> > place the main document resides?
>> > (using Office 2003)

Reply to this Message

George Lagger - 27 Jan 2005 13:55 GMT

Thank you A LOT!-)
The recorded macro showed a parameter in OpenDataSource which I could not
find in HELP :

SubType:=wdMergeSubTypeAccess

it was inserted after SQLStatement:="SELECT * FROM `Sheet1$`",
and that did the trick!

> As long as you know the sheet name, you should be able to specify it in the
> SQLStatement. For example, the following OpenDataSource call should open the
[quoted text clipped - 62 lines]
> >> > place the main document resides?
> >> > (using Office 2003)

Reply to this Message