Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge using Visual Basic ActiveX
I am running a Mail Merge initiated from a DTS package using Visual Basic
ActiveX The word document for the mail merge uses a datasource (.odc file) against a sql server 2000 database. Now if I run the mailmerge from the directory where the word doc and .odc file reside, it works fine. If I run the DTS package that invokes the below script locally it also runs fine, however it fails when it runs as a scheduled job at the statement .Destination = wdSendToNewDocument in the script below. I suspect it's an issue with referencing that datasouce. Can someone suggest the code I would need to establish the database connection and sql statement within the below script versus relying on the connection to the .odc file Thanks in advance '************************************************* ***********' Visual Basic ActiveX Script '************************************************* ***********Function Main() Dim WordFileTemplateName Dim WordFileOutputName Dim appword WordFileTemplateName = "\\xyz\MailMerge\Hold\BillMM.doc" WordFileOutputName = "\\xyz\MailMerge\Hold\Billout.doc" Set appword = CreateObject("word.application") appword.Visible = False appword.Documents.Open WordFileTemplateName With appword.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True .Execute Pause=True End With With appword.ActiveDocument .SaveAs(WordFileOutputName) End With appword.Quit (False) 'without saving set appword = nothing Main = DTSTaskExecResult_Success End Function |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge using Visual Basic ActiveX
You are almost certainly correct that, in essence, the document has failed
to connect to the data source, but there are a few possibilities, e.g.: a. yes, you just need to add the necessary code to connect. In this case I'd suggest that the following method call might be enough: appword.ActiveDocument.MailMerge.OpenDataSource _ Name:="the full path name of the .odc file" However, you might need appword.ActiveDocument.MailMerge.OpenDataSource _ Name:="the full path name of the .odc file", _ SQLStatement:="your SQL statement, eg. SELECT * FROM mytable" b. however, it may be that all you need do is take account of the following article or maybe you need (a) as well(ignore the heading, just try the registry change if you can, unless you've done that already of course, and see what happens): http://support.microsoft.com/kb/825765 c. when /you/ run the script, you have the necessary permissions to access the relevant data in the SQL Server database, but the scheduled job runs under a login/username that does not have those permissions. In that case you either need to change that user's permissions or find some way to run the job under a user that does have the necessary permissions. d. there is indeed something in the .odc that works for "you", but not for the scheduled job. However - I don't think this is likely - there is no possibility of telling without seeing the content of the ..odc. You could paste it in a message here - if you do, just leave out the stuff below "/odc:OfficeDataConnection" Peter Jamieson "ref" wrote in message ... I am running a Mail Merge initiated from a DTS package using Visual Basic ActiveX The word document for the mail merge uses a datasource (.odc file) against a sql server 2000 database. Now if I run the mailmerge from the directory where the word doc and .odc file reside, it works fine. If I run the DTS package that invokes the below script locally it also runs fine, however it fails when it runs as a scheduled job at the statement .Destination = wdSendToNewDocument in the script below. I suspect it's an issue with referencing that datasouce. Can someone suggest the code I would need to establish the database connection and sql statement within the below script versus relying on the connection to the .odc file Thanks in advance '************************************************* ***********' Visual Basic ActiveX Script '************************************************* ***********Function Main() Dim WordFileTemplateName Dim WordFileOutputName Dim appword WordFileTemplateName = "\\xyz\MailMerge\Hold\BillMM.doc" WordFileOutputName = "\\xyz\MailMerge\Hold\Billout.doc" Set appword = CreateObject("word.application") appword.Visible = False appword.Documents.Open WordFileTemplateName With appword.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True .Execute Pause=True End With With appword.ActiveDocument .SaveAs(WordFileOutputName) End With appword.Quit (False) 'without saving set appword = nothing Main = DTSTaskExecResult_Success End Function |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MailMerge using Visual Basic ActiveX
Peter
The issue was indeed that the scheduled job was not finding the .odc file as is was residing in a folder on the network. When I put a copy of the .odc file on the server's c drive it worked fine. I did not try it yet but I am sure that your suggestion would be the best solution == appword.ActiveDocument.MailMerge.OpenDataSource _ Name:="the full path name of the .odc file" Thanks "Peter Jamieson" wrote: You are almost certainly correct that, in essence, the document has failed to connect to the data source, but there are a few possibilities, e.g.: a. yes, you just need to add the necessary code to connect. In this case I'd suggest that the following method call might be enough: appword.ActiveDocument.MailMerge.OpenDataSource _ Name:="the full path name of the .odc file" However, you might need appword.ActiveDocument.MailMerge.OpenDataSource _ Name:="the full path name of the .odc file", _ SQLStatement:="your SQL statement, eg. SELECT * FROM mytable" b. however, it may be that all you need do is take account of the following article or maybe you need (a) as well(ignore the heading, just try the registry change if you can, unless you've done that already of course, and see what happens): http://support.microsoft.com/kb/825765 c. when /you/ run the script, you have the necessary permissions to access the relevant data in the SQL Server database, but the scheduled job runs under a login/username that does not have those permissions. In that case you either need to change that user's permissions or find some way to run the job under a user that does have the necessary permissions. d. there is indeed something in the .odc that works for "you", but not for the scheduled job. However - I don't think this is likely - there is no possibility of telling without seeing the content of the ..odc. You could paste it in a message here - if you do, just leave out the stuff below "/odc:OfficeDataConnection" Peter Jamieson "ref" wrote in message ... I am running a Mail Merge initiated from a DTS package using Visual Basic ActiveX The word document for the mail merge uses a datasource (.odc file) against a sql server 2000 database. Now if I run the mailmerge from the directory where the word doc and .odc file reside, it works fine. If I run the DTS package that invokes the below script locally it also runs fine, however it fails when it runs as a scheduled job at the statement .Destination = wdSendToNewDocument in the script below. I suspect it's an issue with referencing that datasouce. Can someone suggest the code I would need to establish the database connection and sql statement within the below script versus relying on the connection to the .odc file Thanks in advance '************************************************* ***********' Visual Basic ActiveX Script '************************************************* ***********Function Main() Dim WordFileTemplateName Dim WordFileOutputName Dim appword WordFileTemplateName = "\\xyz\MailMerge\Hold\BillMM.doc" WordFileOutputName = "\\xyz\MailMerge\Hold\Billout.doc" Set appword = CreateObject("word.application") appword.Visible = False appword.Documents.Open WordFileTemplateName With appword.ActiveDocument.MailMerge .Destination = wdSendToNewDocument .MailAsAttachment = False .MailAddressFieldName = "" .MailSubject = "" .SuppressBlankLines = True .Execute Pause=True End With With appword.ActiveDocument .SaveAs(WordFileOutputName) End With appword.Quit (False) 'without saving set appword = nothing Main = DTSTaskExecResult_Success End Function |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Visual basic runtime error 5216, there is a printer error | Microsoft Word Help | |||
Visual Basic text box | Mailmerge | |||
Visual basic, Word 2002, DVZaddin.dll file is missing. | Microsoft Word Help | |||
How do I stop Visual Basic Macros from word? | Microsoft Word Help | |||
how do i compile a visual basic project in word? | Microsoft Word Help |