Other VBA Mail Merge Options besides DDE -> Can't get to work

[BAC]

XP Pro;Office 2003 Pro

I receive 50 Excel Workbooks each quarter with one worksheet and between 10
and 1500 records on each worksheet. My task is to mail merge each workbook
with a sales promotion letter and save the merged letters into a .doc file
with the same name as the .xls from which the data is drawn.

Using DDE it takes about 1.5 -> 2 hours to run the mail merge with the
following Merge code in the Word Master Document:

Sub Merge_Letters()

.... Code...

For Each f In ff
If Right(f.Name, 4) = ".xls" Then
wd_name = Left(f.Name, Len(f.Name) - 4) & ".doc"
sh_name = "Sheet1"
On Error GoTo 0
try_Again:

With ActiveDocument.MailMerge
.OpenDataSource Name:=xl_files & f.Name, _
Connection:=[sh_name], SubType:=8
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.DDETerminateAll
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

End If 'Is .xls

Next f

....More Code...
End Sub


I have tried to replace the "With Mail Merge" DDE part above with:

With ActiveDocument.MailMerge
.OpenDataSource Name:=f.Name, _
Connection:= _
"DSN=Excel Files;DBQ=C:\WorkingFiles\BusinessLeasing\Execs\" &
f.Name &
";DefaultDir=C:\WorkingFiles\BusinessLeasing\Execs;DriverId=790;MaxBufferSize=2048;PageTimeout=5;" _
, SQLStatement:= _
"SELECT `'Sheet1$'`.Address, `'Sheet1$'`.City,
`'Sheet1$'`.`Client Manager`, `'Sheet1$'`.`Conf# Code`, `'Sheet1$'`.Contact,
`'Sheet1$'`.`Duns # `, `'Sheet1$'`.`Duns Number`, `'Sheet1$'`.F19,
`'Sheet1$'`" _
, SQLStatement1:= _
".`LEGAL NAME`, `'Sheet1$'`.`Market Executive`,
`'Sheet1$'`.Name, `'Sheet1$'`.Personalize, `'Sheet1$'`.Phone,
`'Sheet1$'`.Phone1, `'Sheet1$'`.RSM, `'Sheet1$'`.Score, `'Sheet1$'`.SIC4,
`'Sheet1$'`.State," _
, SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

But without success.

Is there another way to Access data for a Mail Merge that will speed up this
process, or can you offer any suggestions for fixing the SQL above so it'll
work?

TIA

BAC

 

[Peter Jamieson]

Try

With ActiveDocument.MailMerge
.OpenDataSource _
Name:=f.Name, _
SQLStatement:="SELECT * FROM [Sheet1$]"
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
..ActiveDocument.SaveAs xl_files & wd_name
..ActiveDocument.Close

(This will use the default connection method in Word 2003 (OLE DB) which is
probably no worse than using ODBC, which is what your existing code is
trying to do. You don't need the connection parameter because Word 2003
defaults to OLE DB. You should get all the available columns by using SELECT
*. You do not need to specify a Subtype because Word 2003 gets that right in
this case.)

FWIW what is probably wrong with your existing .OpenDataSOurce is that the
SQL lacks a "FROM" clause (it does not matter that you have specified the
workbook in the Connection string and specified the sheet name for each
column). However, I haven't looked in detail..

Before committing to using OLE DB (or ODBC) when getting data from Excel I
suggest you check out my page at http://tips.pjmsn.me.uk/t0003.htm

--
Peter Jamieson
http://tips.pjmsn.me.uk

XP Pro;Office 2003 Pro

I receive 50 Excel Workbooks each quarter with one worksheet and between
10
and 1500 records on each worksheet. My task is to mail merge each workbook
with a sales promotion letter and save the merged letters into a .doc file
with the same name as the .xls from which the data is drawn.

Using DDE it takes about 1.5 -> 2 hours to run the mail merge with the
following Merge code in the Word Master Document:

Sub Merge_Letters()

... Code...

For Each f In ff
If Right(f.Name, 4) = ".xls" Then
wd_name = Left(f.Name, Len(f.Name) - 4) & ".doc"
sh_name = "Sheet1"
On Error GoTo 0
try_Again:

With ActiveDocument.MailMerge
.OpenDataSource Name:=xl_files & f.Name, _
Connection:=[sh_name], SubType:=8
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.DDETerminateAll
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

End If 'Is .xls

Next f

...More Code...
End Sub


I have tried to replace the "With Mail Merge" DDE part above with:

With ActiveDocument.MailMerge
.OpenDataSource Name:=f.Name, _
Connection:= _
"DSN=Excel Files;DBQ=C:\WorkingFiles\BusinessLeasing\Execs\" &
f.Name &
";DefaultDir=C:\WorkingFiles\BusinessLeasing\Execs;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
_
, SQLStatement:= _
"SELECT `'Sheet1$'`.Address, `'Sheet1$'`.City,
`'Sheet1$'`.`Client Manager`, `'Sheet1$'`.`Conf# Code`,
`'Sheet1$'`.Contact,
`'Sheet1$'`.`Duns # `, `'Sheet1$'`.`Duns Number`, `'Sheet1$'`.F19,
`'Sheet1$'`" _
, SQLStatement1:= _
".`LEGAL NAME`, `'Sheet1$'`.`Market Executive`,
`'Sheet1$'`.Name, `'Sheet1$'`.Personalize, `'Sheet1$'`.Phone,
`'Sheet1$'`.Phone1, `'Sheet1$'`.RSM, `'Sheet1$'`.Score, `'Sheet1$'`.SIC4,
`'Sheet1$'`.State," _
, SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

But without success.

Is there another way to Access data for a Mail Merge that will speed up
this
process, or can you offer any suggestions for fixing the SQL above so
it'll
work?

TIA

BAC

 

[BAC]

OUTSTANDING!

reduced the run time for Dec data from 1hr 23 mins to only 18 mins!
Thanx
BAC

Try

With ActiveDocument.MailMerge
.OpenDataSource _
Name:=f.Name, _
SQLStatement:="SELECT * FROM [Sheet1$]"
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
..ActiveDocument.SaveAs xl_files & wd_name
..ActiveDocument.Close

(This will use the default connection method in Word 2003 (OLE DB) which is
probably no worse than using ODBC, which is what your existing code is
trying to do. You don't need the connection parameter because Word 2003
defaults to OLE DB. You should get all the available columns by using SELECT
*. You do not need to specify a Subtype because Word 2003 gets that right in
this case.)

FWIW what is probably wrong with your existing .OpenDataSOurce is that the
SQL lacks a "FROM" clause (it does not matter that you have specified the
workbook in the Connection string and specified the sheet name for each
column). However, I haven't looked in detail..

Before committing to using OLE DB (or ODBC) when getting data from Excel I
suggest you check out my page at http://tips.pjmsn.me.uk/t0003.htm

--
Peter Jamieson
http://tips.pjmsn.me.uk

XP Pro;Office 2003 Pro

I receive 50 Excel Workbooks each quarter with one worksheet and between
10
and 1500 records on each worksheet. My task is to mail merge each workbook
with a sales promotion letter and save the merged letters into a .doc file
with the same name as the .xls from which the data is drawn.

Using DDE it takes about 1.5 -> 2 hours to run the mail merge with the
following Merge code in the Word Master Document:

Sub Merge_Letters()

... Code...

For Each f In ff
If Right(f.Name, 4) = ".xls" Then
wd_name = Left(f.Name, Len(f.Name) - 4) & ".doc"
sh_name = "Sheet1"
On Error GoTo 0
try_Again:

With ActiveDocument.MailMerge
.OpenDataSource Name:=xl_files & f.Name, _
Connection:=[sh_name], SubType:=8
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.DDETerminateAll
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

End If 'Is .xls

Next f

...More Code...
End Sub


I have tried to replace the "With Mail Merge" DDE part above with:

With ActiveDocument.MailMerge
.OpenDataSource Name:=f.Name, _
Connection:= _
"DSN=Excel Files;DBQ=C:\WorkingFiles\BusinessLeasing\Execs\" &
f.Name &
";DefaultDir=C:\WorkingFiles\BusinessLeasing\Execs;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
_
, SQLStatement:= _
"SELECT `'Sheet1$'`.Address, `'Sheet1$'`.City,
`'Sheet1$'`.`Client Manager`, `'Sheet1$'`.`Conf# Code`,
`'Sheet1$'`.Contact,
`'Sheet1$'`.`Duns # `, `'Sheet1$'`.`Duns Number`, `'Sheet1$'`.F19,
`'Sheet1$'`" _
, SQLStatement1:= _
".`LEGAL NAME`, `'Sheet1$'`.`Market Executive`,
`'Sheet1$'`.Name, `'Sheet1$'`.Personalize, `'Sheet1$'`.Phone,
`'Sheet1$'`.Phone1, `'Sheet1$'`.RSM, `'Sheet1$'`.Score, `'Sheet1$'`.SIC4,
`'Sheet1$'`.State," _
, SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

But without success.

Is there another way to Access data for a Mail Merge that will speed up
this
process, or can you offer any suggestions for fixing the SQL above so
it'll
work?

TIA

BAC

 

[Peter Jamieson]

Quick result!

--
Peter Jamieson
http://tips.pjmsn.me.uk

OUTSTANDING!

reduced the run time for Dec data from 1hr 23 mins to only 18 mins!
Thanx
BAC

Try

With ActiveDocument.MailMerge
.OpenDataSource _
Name:=f.Name, _
SQLStatement:="SELECT * FROM [Sheet1$]"
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
..ActiveDocument.SaveAs xl_files & wd_name
..ActiveDocument.Close

(This will use the default connection method in Word 2003 (OLE DB) which
is
probably no worse than using ODBC, which is what your existing code is
trying to do. You don't need the connection parameter because Word 2003
defaults to OLE DB. You should get all the available columns by using
SELECT
*. You do not need to specify a Subtype because Word 2003 gets that right
in
this case.)

FWIW what is probably wrong with your existing .OpenDataSOurce is that
the
SQL lacks a "FROM" clause (it does not matter that you have specified the
workbook in the Connection string and specified the sheet name for each
column). However, I haven't looked in detail..

Before committing to using OLE DB (or ODBC) when getting data from Excel
I
suggest you check out my page at http://tips.pjmsn.me.uk/t0003.htm

--
Peter Jamieson
http://tips.pjmsn.me.uk

XP Pro;Office 2003 Pro

I receive 50 Excel Workbooks each quarter with one worksheet and
between
10
and 1500 records on each worksheet. My task is to mail merge each
workbook
with a sales promotion letter and save the merged letters into a .doc
file
with the same name as the .xls from which the data is drawn.

Using DDE it takes about 1.5 -> 2 hours to run the mail merge with the
following Merge code in the Word Master Document:

Sub Merge_Letters()

... Code...

For Each f In ff
If Right(f.Name, 4) = ".xls" Then
wd_name = Left(f.Name, Len(f.Name) - 4) & ".doc"
sh_name = "Sheet1"
On Error GoTo 0
try_Again:

With ActiveDocument.MailMerge
.OpenDataSource Name:=xl_files & f.Name, _
Connection:=[sh_name], SubType:=8
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.DDETerminateAll
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

End If 'Is .xls

Next f

...More Code...
End Sub


I have tried to replace the "With Mail Merge" DDE part above with:

With ActiveDocument.MailMerge
.OpenDataSource Name:=f.Name, _
Connection:= _
"DSN=Excel Files;DBQ=C:\WorkingFiles\BusinessLeasing\Execs\"
&
f.Name &
";DefaultDir=C:\WorkingFiles\BusinessLeasing\Execs;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
_
, SQLStatement:= _
"SELECT `'Sheet1$'`.Address, `'Sheet1$'`.City,
`'Sheet1$'`.`Client Manager`, `'Sheet1$'`.`Conf# Code`,
`'Sheet1$'`.Contact,
`'Sheet1$'`.`Duns # `, `'Sheet1$'`.`Duns Number`, `'Sheet1$'`.F19,
`'Sheet1$'`" _
, SQLStatement1:= _
".`LEGAL NAME`, `'Sheet1$'`.`Market Executive`,
`'Sheet1$'`.Name, `'Sheet1$'`.Personalize, `'Sheet1$'`.Phone,
`'Sheet1$'`.Phone1, `'Sheet1$'`.RSM, `'Sheet1$'`.Score,
`'Sheet1$'`.SIC4,
`'Sheet1$'`.State," _
, SubType:=wdMergeSubTypeOther
.Destination = wdSendToNewDocument
.Execute
End With 'Activedocument
.ActiveDocument.SaveAs xl_files & wd_name
.ActiveDocument.Close

But without success.

Is there another way to Access data for a Mail Merge that will speed up
this
process, or can you offer any suggestions for fixing the SQL above so
it'll
work?

TIA

BAC