Home |
Search |
Today's Posts |
#1
|
|||
|
|||
mailmerge with txtfile
Any suggestions how to execute a a direct mailmerge with the following data
from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#2
|
|||
|
|||
I can't think of a way to do it directly - you will need to use code to
transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#3
|
|||
|
|||
Thank you for your effort, but im dealing with users who need to mailmerge
directly with the txtfile in question, because they are novice users who recently started using MS Word and because the advantage of using vba to automize the routine would be lost if they had to go through so many steps to execute a mailmerge. One way to do it is to open the textfile with Excel, copy the data into an Excel sheet and merge with it which works fine, but even though this solves the problem I still think it would be too difficult for my users because of their low IT level. I still think its possible to do it directly with vba because im converting templates from WordPerfect to Word and it works in WordPerfect. Any suggestions are welcome.. "Peter Jamieson" wrote: I can't think of a way to do it directly - you will need to use code to transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#4
|
|||
|
|||
OK, I did not recognise the format as DIF. Can the software used to produce
these files generate a format that Word can use more easily? My assumption is that Excel does not know what to do with the LABEL structure and throws it away, but as long as you always know what columns /should/ be there then it may be possible to deal with that problem by using a separate header source file in Word. Some questions: a. which version(s) of Word do your users have? b. do they all also have Excel (and are they using the same version(s) as Word? c. do the users need to be able to select the file themselves, or can the merge document be created for them with the data source etc. already attached? If the merge document can be created for them, you can try the following 1. create a header file, e.g. called myheader.txt, containing: "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" (Yes, duplicate the line - Word generally recognises header labels more easily if you do) 2. in Word 2003, use Tools|Customize|Commands to drag the MailMergeHelper comand from the All Commands category to a toolbar. Check Tools|Options|General|"Confirm conversion at open" 3. use MailMergeHelper to set up the mail merge type and select the header source. Then go into the Open Data Source dialog (Select Data Source in Word 2002/2003. Select All files (*.*), then locate and select the DIF file. In Word 2000/97, check the "Select method". Then, in any version of Word, you should be asked to confirm the format. You will probably have to select the Show All checkbox, then select the Excel files via DDE option. Excel should start and open the DIF file. 4. If that works, save and close the mail merge main document and try re-opening it. I have not checked all of this, but the main elements of it work here with Word 2003. Obviously it is not a simple process that could easily be followed by a user, which is why I asked question (c) above. It may be possible to automate the process using a VBA OpenDataSource call and put that inside an AutoOpen macro. It may also be possible to open the data source using OLEDB, which would eliminate the additional complexity of opening Excel, but I have not checked that yet, partly because if it does work, it can only work in Word 2002/2003. Also, in earlier versions of Word, there was an Excel Converter which may be able to read the file directly - if you see this option, try it. However, this converter has been removed from the current set of Office Text file converters so it is not a viable long-term option. Peter Jamieson "bilisa" wrote in message ... Thank you for your effort, but im dealing with users who need to mailmerge directly with the txtfile in question, because they are novice users who recently started using MS Word and because the advantage of using vba to automize the routine would be lost if they had to go through so many steps to execute a mailmerge. One way to do it is to open the textfile with Excel, copy the data into an Excel sheet and merge with it which works fine, but even though this solves the problem I still think it would be too difficult for my users because of their low IT level. I still think its possible to do it directly with vba because im converting templates from WordPerfect to Word and it works in WordPerfect. Any suggestions are welcome.. "Peter Jamieson" wrote: I can't think of a way to do it directly - you will need to use code to transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#5
|
|||
|
|||
answers:
a. Word 2003 b. Yes, Excel 2003 c. I have created a Word template and attached a source to it. This works because the member-system that produces the source-output file gives it a standard name and saves it to the c-drive. Here is a sample of my code: 'merge button Private Sub flet_knap_Click() Dim filnavn As String filnavn = "C:\Microsoft Excel Worksheet.xls" 'source 'this attaches the source file to the active document With ActiveDocument.MailMerge .MainDocumentType = wdLetter .OpenDataSource Name:=filnavn, ReadOnly:=True, Connection:="" End With ' executes mailmerge Set myMerge = ActiveDocument.MailMerge If myMerge.State = wdMainAndDataSource Then myMerge.Execute "Peter Jamieson" wrote: OK, I did not recognise the format as DIF. Can the software used to produce these files generate a format that Word can use more easily? My assumption is that Excel does not know what to do with the LABEL structure and throws it away, but as long as you always know what columns /should/ be there then it may be possible to deal with that problem by using a separate header source file in Word. Some questions: a. which version(s) of Word do your users have? b. do they all also have Excel (and are they using the same version(s) as Word? c. do the users need to be able to select the file themselves, or can the merge document be created for them with the data source etc. already attached? If the merge document can be created for them, you can try the following 1. create a header file, e.g. called myheader.txt, containing: "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" (Yes, duplicate the line - Word generally recognises header labels more easily if you do) 2. in Word 2003, use Tools|Customize|Commands to drag the MailMergeHelper comand from the All Commands category to a toolbar. Check Tools|Options|General|"Confirm conversion at open" 3. use MailMergeHelper to set up the mail merge type and select the header source. Then go into the Open Data Source dialog (Select Data Source in Word 2002/2003. Select All files (*.*), then locate and select the DIF file. In Word 2000/97, check the "Select method". Then, in any version of Word, you should be asked to confirm the format. You will probably have to select the Show All checkbox, then select the Excel files via DDE option. Excel should start and open the DIF file. 4. If that works, save and close the mail merge main document and try re-opening it. I have not checked all of this, but the main elements of it work here with Word 2003. Obviously it is not a simple process that could easily be followed by a user, which is why I asked question (c) above. It may be possible to automate the process using a VBA OpenDataSource call and put that inside an AutoOpen macro. It may also be possible to open the data source using OLEDB, which would eliminate the additional complexity of opening Excel, but I have not checked that yet, partly because if it does work, it can only work in Word 2002/2003. Also, in earlier versions of Word, there was an Excel Converter which may be able to read the file directly - if you see this option, try it. However, this converter has been removed from the current set of Office Text file converters so it is not a viable long-term option. Peter Jamieson "bilisa" wrote in message ... Thank you for your effort, but im dealing with users who need to mailmerge directly with the txtfile in question, because they are novice users who recently started using MS Word and because the advantage of using vba to automize the routine would be lost if they had to go through so many steps to execute a mailmerge. One way to do it is to open the textfile with Excel, copy the data into an Excel sheet and merge with it which works fine, but even though this solves the problem I still think it would be too difficult for my users because of their low IT level. I still think its possible to do it directly with vba because im converting templates from WordPerfect to Word and it works in WordPerfect. Any suggestions are welcome.. "Peter Jamieson" wrote: I can't think of a way to do it directly - you will need to use code to transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#6
|
|||
|
|||
I followed the instructions you gave by creating a header file and I selected
the header source with this command: .OpenHeaderSource Name:=myheader (myheader is a string that contains the path to myheader.txt). The result was the source filed opened in excel but the document was not merged with the data!!?? My source code now looks like this (if added comments to make it easy to understand): With ActiveDocument.MailMerge .MainDocumentType = wdLetter 'type of mailmerge .OpenHeaderSource Name:=myheader 'select the header source .OpenDataSource Name:=filnavn, ReadOnly:=True, Connection:="" 'open data 'source End With ' executes mailmerge Set myMerge = ThisDocument.MailMerge 'ActiveDocument.MailMerge If myMerge.State = wdMainAndDataSource Then myMerge.Execute "Peter Jamieson" wrote: A OK, I did not recognise the format as DIF. Can the software used to produce these files generate a format that Word can use more easily? My assumption is that Excel does not know what to do with the LABEL structure and throws it away, but as long as you always know what columns /should/ be there then it may be possible to deal with that problem by using a separate header source file in Word. Some questions: a. which version(s) of Word do your users have? b. do they all also have Excel (and are they using the same version(s) as Word? c. do the users need to be able to select the file themselves, or can the merge document be created for them with the data source etc. already attached? If the merge document can be created for them, you can try the following 1. create a header file, e.g. called myheader.txt, containing: "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" (Yes, duplicate the line - Word generally recognises header labels more easily if you do) 2. in Word 2003, use Tools|Customize|Commands to drag the MailMergeHelper comand from the All Commands category to a toolbar. Check Tools|Options|General|"Confirm conversion at open" 3. use MailMergeHelper to set up the mail merge type and select the header source. Then go into the Open Data Source dialog (Select Data Source in Word 2002/2003. Select All files (*.*), then locate and select the DIF file. In Word 2000/97, check the "Select method". Then, in any version of Word, you should be asked to confirm the format. You will probably have to select the Show All checkbox, then select the Excel files via DDE option. Excel should start and open the DIF file. 4. If that works, save and close the mail merge main document and try re-opening it. I have not checked all of this, but the main elements of it work here with Word 2003. Obviously it is not a simple process that could easily be followed by a user, which is why I asked question (c) above. It may be possible to automate the process using a VBA OpenDataSource call and put that inside an AutoOpen macro. It may also be possible to open the data source using OLEDB, which would eliminate the additional complexity of opening Excel, but I have not checked that yet, partly because if it does work, it can only work in Word 2002/2003. Also, in earlier versions of Word, there was an Excel Converter which may be able to read the file directly - if you see this option, try it. However, this converter has been removed from the current set of Office Text file converters so it is not a viable long-term option. Peter Jamieson "bilisa" wrote in message ... Thank you for your effort, but im dealing with users who need to mailmerge directly with the txtfile in question, because they are novice users who recently started using MS Word and because the advantage of using vba to automize the routine would be lost if they had to go through so many steps to execute a mailmerge. One way to do it is to open the textfile with Excel, copy the data into an Excel sheet and merge with it which works fine, but even though this solves the problem I still think it would be too difficult for my users because of their low IT level. I still think its possible to do it directly with vba because im converting templates from WordPerfect to Word and it works in WordPerfect. Any suggestions are welcome.. "Peter Jamieson" wrote: I can't think of a way to do it directly - you will need to use code to transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#7
|
|||
|
|||
The main problem is that although you can open the data source manually and
specify "Excel via DDE", when you open the data source file using VBA code, there is no way to specify "use Excel via DDE" /unless/ the file extension of the filename you are opening (filnavn in your code) uniquely identifies the file as an Excel file. Neither .txt nor .dif will do. But in fact it is enough to rename the text file to a .xls file. e.g., here I used something like: Dim myheader As String Dim filnavn As String myheader="C:\w\myheader.txt" filnavn="C:\w\mydifdata.xls" With ActiveDocument.MailMerge ' Unlink the header and data source .MainDocumentType = wdNotAMergeDocument .MainDocumentType = wdFormLetters .OpenHeaderSource _ Name:=myheader ' You probably do not need the SQLStatement parameter. ' wdMergeSubType2000 makes the method follow the old ' Word 2000 code path - in that case the file extension .xls ' says "use Excel", the lack of an ODBC connection string in ' "Connection" says "use DDE", and "Entire Spreadsheet" ' specifies the range. It is possible that if you are not using ' an English Language version of Office that you may need to ' change "Entire Spreadsheet" to match the language version you ' are using .OpenDataSource _ Name:=filnavn, _ Connection:="Entire Spreadsheet", _ SubType:=wdMergeSubTypeWord2000 ' Do it .Destination = wdSendToNewDocument .Execute End With I may post a bit more, but that will have to do for now. Peter Jamieson "bilisa" wrote in message ... I followed the instructions you gave by creating a header file and I selected the header source with this command: .OpenHeaderSource Name:=myheader (myheader is a string that contains the path to myheader.txt). The result was the source filed opened in excel but the document was not merged with the data!!?? My source code now looks like this (if added comments to make it easy to understand): With ActiveDocument.MailMerge .MainDocumentType = wdLetter 'type of mailmerge .OpenHeaderSource Name:=myheader 'select the header source .OpenDataSource Name:=filnavn, ReadOnly:=True, Connection:="" 'open data 'source End With ' executes mailmerge Set myMerge = ThisDocument.MailMerge 'ActiveDocument.MailMerge If myMerge.State = wdMainAndDataSource Then myMerge.Execute "Peter Jamieson" wrote: A OK, I did not recognise the format as DIF. Can the software used to produce these files generate a format that Word can use more easily? My assumption is that Excel does not know what to do with the LABEL structure and throws it away, but as long as you always know what columns /should/ be there then it may be possible to deal with that problem by using a separate header source file in Word. Some questions: a. which version(s) of Word do your users have? b. do they all also have Excel (and are they using the same version(s) as Word? c. do the users need to be able to select the file themselves, or can the merge document be created for them with the data source etc. already attached? If the merge document can be created for them, you can try the following 1. create a header file, e.g. called myheader.txt, containing: "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" (Yes, duplicate the line - Word generally recognises header labels more easily if you do) 2. in Word 2003, use Tools|Customize|Commands to drag the MailMergeHelper comand from the All Commands category to a toolbar. Check Tools|Options|General|"Confirm conversion at open" 3. use MailMergeHelper to set up the mail merge type and select the header source. Then go into the Open Data Source dialog (Select Data Source in Word 2002/2003. Select All files (*.*), then locate and select the DIF file. In Word 2000/97, check the "Select method". Then, in any version of Word, you should be asked to confirm the format. You will probably have to select the Show All checkbox, then select the Excel files via DDE option. Excel should start and open the DIF file. 4. If that works, save and close the mail merge main document and try re-opening it. I have not checked all of this, but the main elements of it work here with Word 2003. Obviously it is not a simple process that could easily be followed by a user, which is why I asked question (c) above. It may be possible to automate the process using a VBA OpenDataSource call and put that inside an AutoOpen macro. It may also be possible to open the data source using OLEDB, which would eliminate the additional complexity of opening Excel, but I have not checked that yet, partly because if it does work, it can only work in Word 2002/2003. Also, in earlier versions of Word, there was an Excel Converter which may be able to read the file directly - if you see this option, try it. However, this converter has been removed from the current set of Office Text file converters so it is not a viable long-term option. Peter Jamieson "bilisa" wrote in message ... Thank you for your effort, but im dealing with users who need to mailmerge directly with the txtfile in question, because they are novice users who recently started using MS Word and because the advantage of using vba to automize the routine would be lost if they had to go through so many steps to execute a mailmerge. One way to do it is to open the textfile with Excel, copy the data into an Excel sheet and merge with it which works fine, but even though this solves the problem I still think it would be too difficult for my users because of their low IT level. I still think its possible to do it directly with vba because im converting templates from WordPerfect to Word and it works in WordPerfect. Any suggestions are welcome.. "Peter Jamieson" wrote: I can't think of a way to do it directly - you will need to use code to transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
#8
|
|||
|
|||
It worked - thanks a lot
"Peter Jamieson" wrote: The main problem is that although you can open the data source manually and specify "Excel via DDE", when you open the data source file using VBA code, there is no way to specify "use Excel via DDE" /unless/ the file extension of the filename you are opening (filnavn in your code) uniquely identifies the file as an Excel file. Neither .txt nor .dif will do. But in fact it is enough to rename the text file to a .xls file. e.g., here I used something like: Dim myheader As String Dim filnavn As String myheader="C:\w\myheader.txt" filnavn="C:\w\mydifdata.xls" With ActiveDocument.MailMerge ' Unlink the header and data source .MainDocumentType = wdNotAMergeDocument .MainDocumentType = wdFormLetters .OpenHeaderSource _ Name:=myheader ' You probably do not need the SQLStatement parameter. ' wdMergeSubType2000 makes the method follow the old ' Word 2000 code path - in that case the file extension .xls ' says "use Excel", the lack of an ODBC connection string in ' "Connection" says "use DDE", and "Entire Spreadsheet" ' specifies the range. It is possible that if you are not using ' an English Language version of Office that you may need to ' change "Entire Spreadsheet" to match the language version you ' are using .OpenDataSource _ Name:=filnavn, _ Connection:="Entire Spreadsheet", _ SubType:=wdMergeSubTypeWord2000 ' Do it .Destination = wdSendToNewDocument .Execute End With I may post a bit more, but that will have to do for now. Peter Jamieson "bilisa" wrote in message ... I followed the instructions you gave by creating a header file and I selected the header source with this command: .OpenHeaderSource Name:=myheader (myheader is a string that contains the path to myheader.txt). The result was the source filed opened in excel but the document was not merged with the data!!?? My source code now looks like this (if added comments to make it easy to understand): With ActiveDocument.MailMerge .MainDocumentType = wdLetter 'type of mailmerge .OpenHeaderSource Name:=myheader 'select the header source .OpenDataSource Name:=filnavn, ReadOnly:=True, Connection:="" 'open data 'source End With ' executes mailmerge Set myMerge = ThisDocument.MailMerge 'ActiveDocument.MailMerge If myMerge.State = wdMainAndDataSource Then myMerge.Execute "Peter Jamieson" wrote: A OK, I did not recognise the format as DIF. Can the software used to produce these files generate a format that Word can use more easily? My assumption is that Excel does not know what to do with the LABEL structure and throws it away, but as long as you always know what columns /should/ be there then it may be possible to deal with that problem by using a separate header source file in Word. Some questions: a. which version(s) of Word do your users have? b. do they all also have Excel (and are they using the same version(s) as Word? c. do the users need to be able to select the file themselves, or can the merge document be created for them with the data source etc. already attached? If the merge document can be created for them, you can try the following 1. create a header file, e.g. called myheader.txt, containing: "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" "MDLNR","NAVN","ADRESSE1","ADRESSE2","POSTNR","BYE N" (Yes, duplicate the line - Word generally recognises header labels more easily if you do) 2. in Word 2003, use Tools|Customize|Commands to drag the MailMergeHelper comand from the All Commands category to a toolbar. Check Tools|Options|General|"Confirm conversion at open" 3. use MailMergeHelper to set up the mail merge type and select the header source. Then go into the Open Data Source dialog (Select Data Source in Word 2002/2003. Select All files (*.*), then locate and select the DIF file. In Word 2000/97, check the "Select method". Then, in any version of Word, you should be asked to confirm the format. You will probably have to select the Show All checkbox, then select the Excel files via DDE option. Excel should start and open the DIF file. 4. If that works, save and close the mail merge main document and try re-opening it. I have not checked all of this, but the main elements of it work here with Word 2003. Obviously it is not a simple process that could easily be followed by a user, which is why I asked question (c) above. It may be possible to automate the process using a VBA OpenDataSource call and put that inside an AutoOpen macro. It may also be possible to open the data source using OLEDB, which would eliminate the additional complexity of opening Excel, but I have not checked that yet, partly because if it does work, it can only work in Word 2002/2003. Also, in earlier versions of Word, there was an Excel Converter which may be able to read the file directly - if you see this option, try it. However, this converter has been removed from the current set of Office Text file converters so it is not a viable long-term option. Peter Jamieson "bilisa" wrote in message ... Thank you for your effort, but im dealing with users who need to mailmerge directly with the txtfile in question, because they are novice users who recently started using MS Word and because the advantage of using vba to automize the routine would be lost if they had to go through so many steps to execute a mailmerge. One way to do it is to open the textfile with Excel, copy the data into an Excel sheet and merge with it which works fine, but even though this solves the problem I still think it would be too difficult for my users because of their low IT level. I still think its possible to do it directly with vba because im converting templates from WordPerfect to Word and it works in WordPerfect. Any suggestions are welcome.. "Peter Jamieson" wrote: I can't think of a way to do it directly - you will need to use code to transform the data into something that Word can use. In this case, you really need to know the structure. Doing it in code would take some effort, but it would not be all that hard to do it by hand as long as the file is not huge, e.g. if I make a few guesses about the format, a. open the file in Word b. remove the "header", including the heading labels and everything else down to the first piece of data ("12321323434"). c. use Edit Replace to - replace ^p1,0 by ^t - replace ^p-1,0^pBOT^p1,0 by ^p d. you should now have one row per record and a tab between each field. e. optionally, use Table|Convert text to table to convert the data to a Word table f. add your header row (in table format if you did (e) g. save the file as a Word .doc h. use that as the data source. Peter Jamieson "bilisa" wrote in message ... Any suggestions how to execute a a direct mailmerge with the following data from a textfile? TABLE 0,1 "" VECTORS 0,6 "" TUPLES 0,10000 "" LABEL 1,0 "MDLNR" LABEL 2,0 "NAVN" LABEL 3,0 "ADRESSE1" LABEL 4,0 "ADRESSE2" LABEL 5,0 "POSTNR" LABEL 6,0 "BYEN" DATA 0,0 "" -1,0 BOT 1,0 "12321323434" 1,0 "some name" 1,0 "" 1,0 "some road" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 "123213213" 1,0 "some name" 1,0 "" 1,0 "some address" 1,0 "some zipcode" 1,0 "some city" -1,0 BOT 1,0 |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mailmerge with multipule letters | Mailmerge | |||
Textboxes in Mailmerge? | Mailmerge | |||
Why does my mailmerge catalog from outlook only print odd numbere. | Mailmerge | |||
Problems (again) getting Outlook contacts in Word 2002 Mailmerge | Mailmerge | |||
Included Property in Word 2003 MailMerge | Mailmerge |