I am having difficulty with some script that I have written, probably because I am learning this stuff as I go along!! The script below is basically to extract some data from a v large spreadsheet and populate that data into a letter via mail merge (this seems to me to be the best option). However Excel seems to hang for 2-3 mins before opening word, am I missing something fundamental? The other main problem is that the mail merge will only execute once after installation, in subsequent attempts to run the script the mail merge does not fully complete (i.e. the template document opens but not the merged doc). However to confuse matters if you go through the de-bugger 1 step at a time the mail merge completes.

If somebody could give me a few pointers on how to speed things up or even solve the above puzzle I would be v grateful. FYI I am running Office 2k Win XP Pro & have an Athalon 750 processor.

The script is:

'to open and populate the spreadsheet from which the mailmerge retreaves data

Application.Calculation = xlCalculationManual

Dim wdApp As Word.Application

strFilePath = "C:\trisigma\OfferLetters\" & Sheets("input").Range("C3").Value


Sheets("infoforol").Select
Range("A2:G2").Select
Selection.Copy

Workbooks.Open Filename:= _
"C:\TriSigma\HENDRED CALCULATOR\Export_to_offerlet.xls"
Range("A2:G2").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

ActiveWorkbook.Save
ActiveWorkbook.Close

'' THIS IS WHERE IT HANGS ''

On Error Resume Next
'try to establish link to open instance of word
Set wdApp = GetObject(, "word.application")
'if this fails, open Word

If wdApp Is Nothing Then
Set wdApp = GetObject("", "word.application")
End If

With wdApp

.Documents.Open Filename:="C:\trisigma\compensation.letter.doc"
.Visible = True


Range("A2:G2").Select
Range("F2").Activate
Selection.Interior.ColorIndex = xlNone
Selection.Font.Bold = False
Selection.HorizontalAlignment = xlLeft



With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

For Each docReport In appWord.Documents
If docReport.Name Like "Form Letters*" Then
docReport.Activate
Exit For
End If
Next

.ActiveDocument.SaveAs strFilePath





'Release object variable
Set wdApp = Nothing

sdowen,

Seems to me that you're going in reverse. If you goal is to extract data from a spreadsheet and run a mail merge process -- shouldn't you be running it from Word rather than Excel?

Fact is, you could simply turn on the Macro Recorder and record the process in Word. Then examine the recorded code and make variable whatever you needed for future processing.

Simply:

a) First set up the document with Fields (to which the spreadsheet data will be merged -- the field names should match the column names containing the data in the spreadsheet -- note: replace space with underscore characters)

a) Turn on the macro recorder (giving the macro a meaningful name)

b) Select the spreadsheet as the data source

c) Run the mail merge.

d) Stop the macro.

[Now you can examine the code and commands generated by Word.]

That's how I would do it, anyway.

CarlR

Hi,

I am trying to automate the Mail merge functionality of MS Word 2000, Excel 2000 as its datasource using ASP.Net/VB.Net but when I execute my code the following exception is generated :(:

System.Runtime.InteropServices.COMException (0x800A1722): Word was unable to open the data source. at Word.MailMerge.OpenDataSource(String Name, Object& Format, Object& ConfirmConversions, Object& ReadOnly, Object& LinkToSource, Object& AddToRecentFiles, Object& PasswordDocument, Object& PasswordTemplate, Object& Revert, Object& WritePasswordDocument, Object& WritePasswordTemplate, Object& Connection, Object& SQLStatement, Object& SQLStatement1) at FPA.JobIntegration.btnMailMerge_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\FPA\JobIntegration.aspx.vb:line 96


Please have a look at the code pasted below and do the needful.


Private Sub btnMailMerge_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMailMerge.Click
        Dim wrdApp As Word.Application
        Dim wrdDoc As Word._Document
        Dim wrdDoc1 As Word.MailMergeDataSource
        Dim wrdSelection As Word.Selection
        Dim wrdMailMerge As Word.MailMerge
        Dim wrdMergeFields As Word.MailMergeFields
        Dim fileWORDName, fileEXCELName As String

        Try
            wrdApp = New Word.Application

            wrdApp.ChangeFileOpenDirectory("C:\Inetpub\wwwroot \FPA\Templates\")
            fileWORDName = "ThankYou-Shalini.doc"

            wrdDoc = wrdApp.Documents.Open(FileName:=fileWORDName, ConfirmConversions:=False, ReadOnly _
            :=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate _
            :="", Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="" _
            , Format:=Word.WdOpenFormat.wdOpenFormatAuto)

            wrdMailMerge = wrdDoc.MailMerge

            If wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdNotAMergeDocument Then
                wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
            End If

            wrdApp.Visible = True
            wrdDoc.ActiveWindow.Activate()
            wrdDoc.ActiveWindow.Visible = True

            fileEXCELName = "C:\Inetpub\wwwroot\FPA\Archive\FullfillmentReport 1.xls![FullfillmentReport]"


            wrdMailMerge.OpenDataSource(Name:=fileEXCELName, _
                ReadOnly:=False, LinkToSource:=True, _
                Connection:="DSN=Excel Files;DBQ=" & fileEXCELName & ";DriverId=790;MaxBufferSize=2048;PageTimeout= 5;", _
                SQLStatement:="SELECT * FROM `Data`")


            ' Perform mail merge.
            wrdMailMerge.MainDocumentType = Word.WdMailMergeMainDocType.wdFormLetters
            wrdMailMerge.Destination = Word.WdMailMergeDestination.wdSendToNewDocument
            wrdMailMerge.SuppressBlankLines = True
            wrdMailMerge.DataSource.FirstRecord = Word.WdMailMergeDefaultRecord.wdDefaultFirstRecord
            wrdMailMerge.DataSource.LastRecord = Word.WdMailMergeDefaultRecord.wdDefaultLastRecord
            wrdMailMerge.Execute(True)

            ' Close the original form document.
            'wrdDoc.Saved = True
            'wrdDoc.Close(False)

        Catch ex As Exception
            Response.Write(ex.ToString)
            Dim AllWORDProcess() As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessesByName("WIN WORD")
            Dim WordProcess As New System.Diagnostics.Process
            For Each WordProcess In AllWORDProcess
                WordProcess.Kill()
            Next
            WordProcess.Close()

            Dim AllEXCELProcess() As System.Diagnostics.Process = System.Diagnostics.Process.GetProcessesByName("Exc el")
            Dim ExcelProcess As New System.Diagnostics.Process
            For Each ExcelProcess In AllEXCELProcess
                ExcelProcess.Kill()
            Next
            ExcelProcess.Close()
        Finally
            wrdMailMerge = Nothing
            wrdDoc = Nothing
            wrdApp = Nothing
        End Try

    End Sub