VB Excel Macro for merging letters

[Kalechibki]

I'm writing a VB Macro in Excel that opens up a word document (A form letter), adds the file that the user is working on as the data source, and then prints the merged document. I'm running into a few snags.

The first is that when I try to do this, it tries to reopen the excel spreadsheet I'm working on, and that delays the merge, and it opens a dialogue box that asks if I want to open the same file as a readonly, notify me when the other instance is closed, or to cancel the operation. The Merge still works with the file in Read-only, it just takes forever.

The second is that I can't figure out how to use the connection property of OpenDataSource (The code I'm currently using is below). I've tried several things to get the area of the Excel Spreadsheet I want as the table, and when it compiles, it doesn't like what it sees. The part of the Excel File I'm using to transfer values is A2:AA2 on sheet1, with the headings for these values in A1:AA1. I'm using the Office 2000 suite, with all three service packs installed.

The third is trying to pass in a value to the Macro. The macro is being activated by pressing a button on a toolbar. I have thus far been able to figure out how to pass in the value. I know that if the macro is a formula, you can set a cell equal to the macro name, but I don't see how that can work in this case because this isn't a formula. I've renamed the cell Ltype.

Any help anyone can provide on my three questions would be
wonderful!

Brian

Note that an underscore at the end of the line means that the next line is really a part of that line.

Sub WordMailMerge()
    
    Dim wdapp As Object
    Ltype = J50
    Set wdapp = CreateObject("Word.Application")
    If Ltype = 1 Then
           wdapp.Application.Documents.Open_
                 FileName:="C:\Documents and_
                 Settings\bcavanau\Desktop\testmerge2.doc"
            MsgBox "Letter Type is a rolled letter"
    ElseIf Ltype = 0 Then
            wdapp.Application.Documents.Open_
                 FileName:="C:\Documents and_
                 Settings\bcavanau\Desktop\testmerge2.doc"
            MsgBox "Letter Type is a check letter"
    Else
        MsgBox "Error in Letter type.  Ending Macro."
        End
    End If
    
    wdapp.Application.Documents.Open_
         FileName:="C:\Documents and_
         Settings\bcavanau\Desktop\testmerge2.doc"
    wdapp.Visible = True
    wdapp.Application.Documents(1).Activate
    MsgBox "'" & wdapp.Application.ActiveDocument.Name_
          & "' was opened."
    
    Set myDoc = wdapp.Application.ActiveDocument
    Set myMerge = wdapp.Application.ActiveDocument.MailMerge
    With myDoc.MailMerge
        .MainDocumentType = wdFormLetters
        .OpenDataSource Name:="C:\Documents and_
            Settings\bcavanau\Desktop\LumpSumBenefit.xls",_
            LinkToSource:=False, AddToRecentFiles:=False,_
            Revert:=False
        .OpenHeaderSource Name:="C:\Documents and_
            Settings\bcavanau\Desktop\LumpSumHeader.doc",_
            AddToRecentFiles:=False, Revert:=False_
    End With
    With myMerge.DataSource
        .firstrecord = 1
        .lastrecord = 1
    End With
    With myMerge
        .Destination = wdSendtoNewDocument
        .Execute
    End With
    MsgBox "'" & wdapp.Application.ActiveDocument.Name & _
         "' is done merging."
    Set wdapp = Nothing
End Sub