Exporting a (ADODB) recordset to Excel for Word mail merge 
Author Message
 Exporting a (ADODB) recordset to Excel for Word mail merge

I have a project (VB 6) with a connection (SQL OLEDB) set to a SQL
Server (2000) database.  I am using a SQL string to extract 1 record
from 1 of 2 different tables.  Then, I open an Excel application, and
an existing workbook and sheet, and fill the 1st row with column names
(for the mail merge document), and then fill the 2nd row with the
values of the recordset.  After this, I open a Word application and an
existing document that is already set up for the mail merge, I call a
macro from VB that merges the 2 documents.

Here are my problems:

1) I don't want the client to see Excel or Word running in the
background, just the finished merged document.  It is not necessary to
save the changes to either excel or word.  I have tried to use the
.Visible = False, but my results are not correct when I go this route.

2) I'm not exactly sure which Project | References... I should be
using?  MS ActiveX 2.0, 2.5, 2.6?  Data Binding?  What else?

3) When I try to close Excel and Word from VB, and set them = Nothing,
I get an Automation error (Run-Time error '430')

Lastly,
4) When Excel is called/opened, I get a message that it is being
modified by myself, and it must open as read-only.  Why?

Here is the sub with this particular snippet of code:

'*************************************************************************
'strSQL -> SQL statement
'xlFile -> pre-determined Excel File
'wdFile -> pre-determined Word mail merge document
'cnSQL -> globally declared connection

'*************************************************************************
Public Sub CreateContract(strSQL As String, strSource As String,
xlFile As String, wdFile As String)
Dim i As Integer
Dim j As Integer
Set cnSQL = New adodb.Connection
Dim cmdContract As adodb.Command
Dim rstRecords As adodb.Recordset
cnSQL.Open deContractsSQL.cnSQLContracts
Set cmdContract = New adodb.Command

Set cmdContract.ActiveConnection = cnSQL
Set rstRecords = New adodb.Recordset
cmdContract.CommandText = strSQL

rstRecords.Open cmdContract, , , adLockOptimistic

i = 1
j = rstRecords.Fields.Count

Set xlApp = New Excel.Application
xlApp.Visible = True

Set xlBook = xlApp.Workbooks.Open(xlFile, , False, , , , True, , ,
True, False)
Set xlSheet = xlBook.Worksheets("Sheet1")

rstRecords.MoveFirst
    For i = 1 To j
        xlSheet.Cells(1, i).Value = rstRecords.Fields(i - 1).Name
    Next
rstRecords.MoveFirst
    For i = 1 To j
        xlSheet.Cells(2, i).Value = rstRecords.Fields(i - 1).Value
    Next

Set wdApp = New Word.Application
Set wdDoc = wdApp.Documents.Open(wdFile, , False)
wdApp.Visible = True
wdApp.Run "MacMerge"

xlApp.Quit
xlBook.Close False
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
rstRecords.Close
Set rstRecords = Nothing
Set cmdContract = Nothing
cnSQL.Close
Set cnSQL = Nothing



Tue, 18 Jan 2005 21:56:53 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Word Mail Merge Err - Word could not merge...

2. Exporting Access Data to a Word Mail-Merge Template

3. Mail merging with Word from within Excel

4. Suppress Excel Userform In Word Mail Merge

5. Formatting Merge Fields in Word Mail Merge

6. HTML forms, Word, e-mail and mail merge

7. creating word mailing labels with Mail Merge

8. HTML forms, Word, e-mail and mail merge

9. Mail Merge BarCodes / Mail Merge Insertion Point

10. Exporting recordset into Excel and opening Excel

11. Recordsets and mail merging

12. e-mail merge from excel

 

 
Powered by phpBB® Forum Software