Mail Merge with Variables (Sequal Formatting?)

[Keith]

I have Office 2003 and I have tried every variation I can think of to
use a variable for the worksheet name (to reflect the database) in a
mail merge. I hesitate to try a different connection, because the way
it is currently set up, it will read more than 255 characters (which
is a requirement).

I'll include my code below (up to the point I have a problem).
SQLStatement:="SELECT * FROM 'strExcelWkshtName'", _

I have also tried these (and many, many other) variations.
SQLStatement:="SELECT * FROM strExcelWkshtName", _
SQLStatement:="SELECT * FROM" & strExcelWkshtName, _
SQLStatement:="SELECT * FROM" & 'strExcelWkshtName', _

This statement (without the variable) works fine.
SQLStatement:="SELECT * FROM `'Planned Close Version 200-DB$'`", _

Notice that the variable strExcelFileName is used twice in the mail
merge and that works fine.
Just before my mail merge statements, I have 'commented out' the mail
merge line for the worksheet (database) that does not contain the
variable and works great.
====================================================================


Dim appXL As Excel.Application
Dim strExcelFileName As String, strExcelWkshtName As String

' Merge the data from the spreadsheet into a new word document based
off of this template.
'
Set appXL = GetObject(, "excel.application")
With appXL.ActiveWorkbook
strExcelFileName = .FullName
strExcelWkshtName = .ActiveSheet.Name & "$"
MsgBox strExcelFileName
MsgBox strExcelWkshtName
End With
'
' Merge the Word Template with the Excel data to create the User
Release Notes.
'


' , SQLStatement:="SELECT * FROM `'Planned Close Version 200-DB
$'`", _

ActiveDocument.MailMerge.MainDocumentType = wdCatalog
ActiveDocument.MailMerge.OpenDataSource Name:=strExcelFileName, _
ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User
ID=Admin;Data Source=strExcelFileName;Mode=Read;Extended
Properties=""HDR=YES;IMEX=1;"";Jet OLEDB:System database", _
SQLStatement:="SELECT * FROM 'strExcelWkshtName'", _
SQLStatement1:="", SubType:=wdMergeSubTypeAccess
========================================================================

Anybody have any ideas?

Keith

 

[Peter Jamieson]

The main thing you're missing is a space after the "FROM"

In Word 2003, try

ActiveDocument.MailMerge.MainDocumentType = wdCatalog
ActiveDocument.MailMerge.OpenDataSource _
Name:=strExcelFileName, _
SQLStatement:="SELECT * FROM [" & strExcelWkshtName & "]"

(none of the other parameters except possibly SubType have an impact on what
happens).

Peter Jamieson

 

[Keith]

Peter,
You are a genius. I copied exactly what you had and replaced that
area of my code. This worked wonderfully. I also checked to see if I
am still getting more than 255 characters. After I randomly looked
for a large field, I found one with more than 2500 characters so that
works fine.

Please go home and put a star on your refrigerator, as you have earned
it.


Keith

 

[Peter Jamieson]

I forgot to say: you might see the 255 character limit, but only if the
first 8 rows in that column have text of 255 characters or shorter (see
http://tips.pjmsn.me.uk/t0003.htm for a somewhat gruelling explanation)

Peter Jamieson

 

[Keith]

Peter,
Thank you very much for that link. I am sure I will have future
issues that this information will help me with. I will have to study
it in much greater detail. I had not come across any of this
information in my prior searches, so thank you for link.


Keith