Variables and Merges and stuff

[Mark Eidemiller]

My intent is to merge a Word document with an Excel spreadsheet, modifying
the filter parameters on the merge so that it will isolate a select group
from the spreadsheet. Usually it's a snap, but I want to be able to use
InputBox to get the filter parameters.

My problem is, they don't fit in the SELECT statement. Where am I going
wrong?

(FYI, I've edited some of the filenames due to company security)

sARD = InputBox("What is the ARD?", "Specify ARD")
sDate = InputBox("What is the date of the report?", "Specify report date")

Documents.Open FileName:="""<report filename>""", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False,
_
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto
ActiveDocument.MailMerge.DataSource.QueryString = _

(This is the statement giving me problems, with the END and ARD variables.)

"SELECT * FROM <spreadsheet filename> WHERE ((END >= sDate) AND (ARD
= sARD)) ORDER BY ARD, LOCATION, EMPLOYEE_NAME" _
& ""

Any help will be greatly appreciated.

Mark

 

[Cindy M -WordMVP-]

Hi Mark,

My intent is to merge a Word document with an Excel spreadsheet, modifying
the filter parameters on the merge so that it will isolate a select group
from the spreadsheet. Usually it's a snap, but I want to be able to use
InputBox to get the filter parameters.

My problem is, they don't fit in the SELECT statement. Where am I going
wrong?

You need to concatenate the string variables into the string. The way it
stands now, what you see is literally what you're sending to Excel. So if
there's no record containing sARD and sDate as entries in the End and ARD
fields, you won't get any records Try it more like this:

"SELECT * FROM <spreadsheet filename> WHERE ((END>=" & sDate & ") _
AND (ARD=" & sARD & ")) ORDER BY ARD, LOCATION, EMPLOYEE_NAME" _
& ""

Note, however, that if END or ARD are fields of the datatype string and not
numeric that you'll need to put a single apostrophe just inside the double
quotes on either side. Chances are, you should also be able to delete the & ""
at the very end.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Mark Eidemiller]

I tried that, but forgot to include the original # surrounding the date, and
' surrounding the ARD. Once I put those in, it worked!

Thanks.