Home |
Search |
Today's Posts |
#1
|
|||
|
|||
DataSource in the folder of the main document
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) |
#2
|
|||
|
|||
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 "George Lagger" wrote in message ... 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) |
#3
|
|||
|
|||
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 "Peter Jamieson" пишет: 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 "George Lagger" wrote in message ... 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) |
#4
|
|||
|
|||
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 "George Lagger" wrote in message ... 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 "Peter Jamieson" ?????: 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 "George Lagger" wrote in message ... 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) |
#5
|
|||
|
|||
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! "Peter Jamieson" пишет: 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 "George Lagger" wrote in message ... 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 "Peter Jamieson" ?????: 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 "George Lagger" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can you save individual document pages as seperate word files? | Mailmerge | |||
Trying to use a .dot document as main document | Mailmerge | |||
I can't see my sub folder under the main contact folder | Microsoft Word Help | |||
Mail Merging into the current or target document | Mailmerge | |||
Document object in protected form | Tables |