DoCmd.TransferSpreadsheet question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Everyone
I need to send names and addresses to an excel spreadsheet to use as a
header for mail merge labels. The user can choose patients on a form and opt
to create lables only for patients/only for their doctors/or for patients and
their doctors. So I have 3 queries that can be exported to excel. Because
the three queries have different names, their results are ending up on
different pages of the excel sheet corresponding to the query name. Is there
a way to specify the actual sheetname in the TransferSpreadsheet command or
perhaps to assign the query definitions to a single query name so that the
results of all three queries are sent to the same page in the spreadsheet

DoCmd.TransferSpreadsheet acExport, 5, "qry_GroupMergePatientUPractitioner",
strHeaderPath & strDocName, -1

Any help would be greatly apprectiated.
Thanks in advance
Anna
 
Anna,

An undocumented feature of the TransferSpreadsheet method in export mode is
that you CAN use the Range argument (Access help says you can't, at least in
A2K), and your argument will become the name of the worksheet, if the
workbook does not have a named range with the same name:

DoCmd.TransferSpreadsheet acExport, 5, "qry_GroupMergePatientUPractitioner",
strHeaderPath & strDocName, -1, "SheetName"

The problem in your case is that if you do that three times with the same
Range argument, each execution will just overwrite the previous one, so you
will end up with just the last query isntead of all three one under the
other, which I suppose is what you want.
The trick here is to do one export instead of one or two or three, which
goes back to your design really. To begin with, do you keep both docs and
patients in a single table to differentiate between the two, or do you have
separate tables? Ideally it should be the former; if it's the latter, you
should consider changing your design to a single table (unless the two are
fundamentally different). If you need to stick with the two different
tables, then make a Union query to gather all names and addresses together.
The SQL for it wouod look something like:

SELECT tblPatients.Name, tblPatients.Address, "Patient" AS Type
FROM tblPatients
UNION SELECT tblDoctors.Name, tblDoctors.Address, "Doctor" AS Type
FROM tblDoctors

This type of query would return a list of all records in both tables (fields
name and Address), with a third field called Type, and values Patient or
Doctor depending on which table the record comes from. It is actually just
trying to imitayte what you would have in a single table design.

So, whether a single table or a Union query on two tables, you can have a
single query on it, with a criterion like:

Like [Please enter P for patients, D for Doctors, nothing for both] & "*"

on the Type field. When running this query, the user will get an input box
with the text in the square brackets as a prompt, and the results will be as
described in the prompt. Simple and efficient. Alternatively, the user might
provide their selection on the scope on a form (listbox, combobox, option
group) which the query reads as a criterion. Assume a form named Form1, with
a listbox named List0 on it, with three entries: Doctors, Patients, Both.
The criterion in the query would be:

Like Forms![Form1]![List0] & "*"

If you go this way, just add a command button on the same form to run the
code (a check to make sure a selection has been made would make a nice
addition).

HTH,
Nikos
 
Um..

I'm not sure exactly what you're doing here, but if you're just printing
address labels, why not just do that in Access? There's a pretty
user-friendly wizard to create mailing labels under Reports - New.
 
Thanks Nikos and McDermott for the information and suggestions. I appreciate
the syntax information for specifying the excel range name.
I do actually want the addresses overwritten each time I run the query, so I
ended up finding a way to assign different SQL statements (Patients only,
Practitioners only or Patients and Practitioners) to a query definition so
that I could always use the same name for the query and hence the
TransferSpreadsheet function always sends to the same place.

MacDermott, I didn't know that Access had a labels wizard, thanks. I am
working with labels now but I hope to extend it to other group mail merge
mail merge eventually, for Access 97 and 2000 users.

thanks again for your help

Anna
 
Glad things are working for you.
Actually, a Word mail merge can use an Access table or query as its
datasource;
there shouldn't be any need to involve Excel in this process.
 
Back
Top