Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Maniplulating the data source location of a merge, MS Word merge
I have created a merge in MS Word which uses a text file as its data source.
When I start the document, and review the merge details, it shows the Data Source as Data: C:\folder1\data.txt However, I want to transport the Word and data.txt file which could end up on another drive/folder location (but always with the Word and data.txt in the same place, not separated). What I really want is Data: ..\data.txt so it looks inside the current folder. Is this possible please? |
#2
|
|||
|
|||
You can use VBA to get the path name of the .doc and use it to construct the
path name of the .txt, then issue an OpenDataSource call in an AutoOpen macro to open that data source when the document opens. However, you should also make sure that the document is saved without a connection to the data source, and that means that you will lose any sorting or filtering and you will need to specify the appropriate bit of SQL in the OpenDataSource call. e.g., your macro might be along the following lines (I haven't tested this one and you will probably need to modify the OpenDataSource): Sub autoopen() Dim sDSFN As String sDFSN = "data.txt" With ActiveDocument.Mailmerge ' Change the document type to the one you want .MainDocumentType = wdFormLetters .OpenDataSource _ ActiveDocument.Path & "\" & sDFSN, _ SQLStatement:="SELECT * FROM " & sDFSN ' Change the destination to the one you want .Destination = wdSendToNewDocument ' Uncomment the next line if you want to do the merge .Execute End With End Sub Peter Jamieson "MCubitt" wrote in message ... I have created a merge in MS Word which uses a text file as its data source. When I start the document, and review the merge details, it shows the Data Source as Data: C:\folder1\data.txt However, I want to transport the Word and data.txt file which could end up on another drive/folder location (but always with the Word and data.txt in the same place, not separated). What I really want is Data: ..\data.txt so it looks inside the current folder. Is this possible please? |
#3
|
|||
|
|||
Thanks Peter. In fact I already use VBA to obtain current docpath and then
dictate the source, but Word errors before VBA is reached, since the merge seems to be validated upon opening. You say save without a connection to the data source, how do I do that? If I remove the data source won't I lose the merge definition, what goes where? Thanks "Peter Jamieson" wrote: You can use VBA to get the path name of the .doc and use it to construct the path name of the .txt, then issue an OpenDataSource call in an AutoOpen macro to open that data source when the document opens. However, you should also make sure that the document is saved without a connection to the data source, and that means that you will lose any sorting or filtering and you will need to specify the appropriate bit of SQL in the OpenDataSource call. e.g., your macro might be along the following lines (I haven't tested this one and you will probably need to modify the OpenDataSource): Sub autoopen() Dim sDSFN As String sDFSN = "data.txt" With ActiveDocument.Mailmerge ' Change the document type to the one you want .MainDocumentType = wdFormLetters .OpenDataSource _ ActiveDocument.Path & "\" & sDFSN, _ SQLStatement:="SELECT * FROM " & sDFSN ' Change the destination to the one you want .Destination = wdSendToNewDocument ' Uncomment the next line if you want to do the merge .Execute End With End Sub Peter Jamieson "MCubitt" wrote in message ... I have created a merge in MS Word which uses a text file as its data source. When I start the document, and review the merge details, it shows the Data Source as Data: C:\folder1\data.txt However, I want to transport the Word and data.txt file which could end up on another drive/folder location (but always with the Word and data.txt in the same place, not separated). What I really want is Data: ..\data.txt so it looks inside the current folder. Is this possible please? |
#4
|
|||
|
|||
You say save without a connection to the data source, how do I do that?
In VBA, use ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument If I remove the data source won't I lose the merge definition, what goes where? You will lose the information about the source file or database, and any sort or filter information, and propably the "current preview record" which Word seems to save when it is connected to a data source. To apply sorts and filters programmatically, you have to work out what SQL is needed, e.g. SELECT * FROM something WHERE x = 'abc' ORDER BY 1 ASC or whatever. The only thing this misses is that there is another mechanism for filtering records in Word 2002/2003 which is to select/reject individual records in the Merge Recipients dialog box. That kind of filtering is not implemented using SQL - Word just tries to remember enough info. about each record to be able to apply the criteria. I recommend that you stay away from that stuff. Peter Jamieson "MCubitt" wrote in message ... Thanks Peter. In fact I already use VBA to obtain current docpath and then dictate the source, but Word errors before VBA is reached, since the merge seems to be validated upon opening. You say save without a connection to the data source, how do I do that? If I remove the data source won't I lose the merge definition, what goes where? Thanks "Peter Jamieson" wrote: You can use VBA to get the path name of the .doc and use it to construct the path name of the .txt, then issue an OpenDataSource call in an AutoOpen macro to open that data source when the document opens. However, you should also make sure that the document is saved without a connection to the data source, and that means that you will lose any sorting or filtering and you will need to specify the appropriate bit of SQL in the OpenDataSource call. e.g., your macro might be along the following lines (I haven't tested this one and you will probably need to modify the OpenDataSource): Sub autoopen() Dim sDSFN As String sDFSN = "data.txt" With ActiveDocument.Mailmerge ' Change the document type to the one you want .MainDocumentType = wdFormLetters .OpenDataSource _ ActiveDocument.Path & "\" & sDFSN, _ SQLStatement:="SELECT * FROM " & sDFSN ' Change the destination to the one you want .Destination = wdSendToNewDocument ' Uncomment the next line if you want to do the merge .Execute End With End Sub Peter Jamieson "MCubitt" wrote in message ... I have created a merge in MS Word which uses a text file as its data source. When I start the document, and review the merge details, it shows the Data Source as Data: C:\folder1\data.txt However, I want to transport the Word and data.txt file which could end up on another drive/folder location (but always with the Word and data.txt in the same place, not separated). What I really want is Data: ..\data.txt so it looks inside the current folder. Is this possible please? |
#5
|
|||
|
|||
Thanks again Peter.
Now I could just be not too bright, but would you mind clarifying a few things please? First of all, the mail merge is very very simple. It uses an ASCII text (csv) file as its data source. There is no filtering or sorting, that's been done in the text file already. So all I do is map a few of the fields to places on the Word document. The data source is fixed at c:\folder1\data.txt but is actually generated by a job which runs in Excel and then open this (merge) document. So the point is that these files could be anywhere so the fixed data source is an issue. (if the files end up on i:\temp\ then of course opening the word file fails) There is a VBA script using document_open() which does the following: Code:
' On opening this word doc, merge then close Sub Document_Open() ' Filename rightnow = Now() newdocfilename = "AddressBook " & Year(rightnow) & "-" & Format(Month(rightnow), "00") & "-" & Format(Day(rightnow), "00") & " " & Format(Hour(rightnow), "00") & "." & Format(Minute(rightnow), "00") & "." & Format(Second(rightnow), "00") & ".doc" newdocfilename = ActiveDocument.Path & "\" & newdocfilename datasourcefilename = "data.txt" datasourcefilename = ActiveDocument.Path & "\" & datasourcefilename ActiveDocument.MailMerge.OpenDataSource Name:=datasourcefilename ActiveDocument.MailMerge.Execute ActiveDocument.SaveAs filename:=newdocfilename ActiveDocument.Close SaveChanges:=False ActiveDocument.Close SaveChanges:=False End Sub VBA is tries to connect to the data source. Your suggestion is to use Code:
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument But where & when? If I run the document up and enable macros it will run my VBA script and auto close. if I disable macros I cannot run the command you rpvided (tried in the Immediate window). So I am not sure if I misunderstood or my execution of the method was wrong! Thanks again |
#6
|
|||
|
|||
Your suggestion is to use
Code:
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument But where & when? What you need to do once and once only, before you deploy your solution, is to open your mail merge main document and run that line of code, either in the Immediate window in the VBA Editor, or as, e.g. Sub x() ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument End Sub then save the document. (In fact you can just use the Mailmerge toolbar/wizard/mailmergehelper to switch the document back to "Normal" but for people who are already using VBA it is simpler to give the one-liner.) After you have made the document a normal document and saved it, when Word opens the document you will not see the message about not being able to find the data source. When your macro executes ActiveDocument.MailMerge.OpenDataSource Name:=datasourcefilename the document will become a mail merge main document again - I think it defaults to type Form Letters, but you can set ActiveDocument.MailMerge.MainDocumentType to the value you want. The other thing you may have to consider if you distribute your solution to users of recent versions and SPs of Word is that a security issue may prevent Word from opening the data source from code unless you apply the information given in http://support.microsoft.com/default...b;en-us;825765 Peter Jamieson "MCubitt" wrote in message ... Thanks again Peter. Now I could just be not too bright, but would you mind clarifying a few things please? First of all, the mail merge is very very simple. It uses an ASCII text (csv) file as its data source. There is no filtering or sorting, that's been done in the text file already. So all I do is map a few of the fields to places on the Word document. The data source is fixed at c:\folder1\data.txt but is actually generated by a job which runs in Excel and then open this (merge) document. So the point is that these files could be anywhere so the fixed data source is an issue. (if the files end up on i:\temp\ then of course opening the word file fails) There is a VBA script using document_open() which does the following: Code:
' On opening this word doc, merge then close Sub Document_Open() ' Filename rightnow = Now() newdocfilename = "AddressBook " & Year(rightnow) & "-" & Format(Month(rightnow), "00") & "-" & Format(Day(rightnow), "00") & " " & Format(Hour(rightnow), "00") & "." & Format(Minute(rightnow), "00") & "." & Format(Second(rightnow), "00") & ".doc" newdocfilename = ActiveDocument.Path & "\" & newdocfilename datasourcefilename = "data.txt" datasourcefilename = ActiveDocument.Path & "\" & datasourcefilename ActiveDocument.MailMerge.OpenDataSource Name:=datasourcefilename ActiveDocument.MailMerge.Execute ActiveDocument.SaveAs filename:=newdocfilename ActiveDocument.Close SaveChanges:=False ActiveDocument.Close SaveChanges:=False End Sub But the problem is that when Word opens the document, before reaching the VBA is tries to connect to the data source. Your suggestion is to use Code:
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument But where & when? If I run the document up and enable macros it will run my VBA script and auto close. if I disable macros I cannot run the command you rpvided (tried in the Immediate window). So I am not sure if I misunderstood or my execution of the method was wrong! Thanks again |
#7
|
|||
|
|||
Thank you very much Peter. In the end I manually removed the merge factor,
returning it back to a "normal" document. Of course the field references remained ... which was initially my worry they'd vanish! Anyway, it worked and I can finally relase the document to a user base. Thanks again |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Specific Email Merge w/ Specific Attachements | Mailmerge | |||
Cannot Edit Data Source in Data Merge Manager | Mailmerge | |||
Mail merge data source problem | Mailmerge | |||
Mail merge error occurs when filtering Excel data source | Mailmerge | |||
Merge Data Source path | Mailmerge |