Mail merge with Excel?

D

Damon

Hi,

I have written a class module in VB6 which merges data from a recordset into
a word document; the datasource for the merge is a word document with 63
fields. I need to be able to use an Excel Spreadsheet instead of the word
document as a datasource. I have tried modifying part of my class merge to
do this but I come across a coiple of obstacles, part of the class merge is
below, if anyone can help fill in the bits that are causing me trouble I
would be most grateful.

Private Sub CreateMailMergeDataFile(getfields() As String)
On Error GoTo Err_CreateMailMergeDataFile

Dim wrdDataDoc As excel.Workbook

' Open a data source at mergedoc containing the field data
wrdDoc.MailMerge.OpenDataSource name:=wrdDataDocName
(This always produces a box called Select Table in which it shows Sheet1$
and I have to press ok for it to carry on, how can I stop this from
happening?)

' Open the file to insert data
Set wrdDataDoc = excel.Workbooks.Open(wrdDataDocName)

' Fill in the data
FillRow wrdDataDoc, getfields

Exit_CreateMailMergeDataFile:
Exit Sub
Err_CreateMailMergeDataFile:
MsgBox Err.Number & " " & Err.Description
Resume Exit_CreateMailMergeDataFile
End Sub

(This is what is looks like when using a word document as data source)
Private Sub FillRow(Doc As Word.Document, getfields() As String)
Dim i As Integer
Dim arraysize As Long

arraysize = UBound(getfields)

For i = 0 To arraysize
With Doc.Tables(1)
.Cell(2, i + 1).Range.InsertAfter getfields(i)
End With
Next i

End Sub

(I need to ammend this to cycle through and put the data into the excel
spreadsheet, it falls over when it gets to .Cell)
Private Sub FillRow(Doc As excel.Workbook, getfields() As String)
Dim i As Integer
Dim arraysize As Long

arraysize = UBound(getfields)

For i = 0 To arraysize
With Doc.Sheets(1)
.Cell(2, i + 1).Range.InsertAfter getfields(i)
End With
Next i

End Sub


Thanks again for any assistance.

Damon
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top