transferspreadsheet - naming the output file

[Guest]

Hi all,

I have created a macro to export the results of a query into Excel. The
macro works fine, and exports perfectly well.

However, I would like the file name to auto generate based on the two dates
that are central to the query I have written, namely a date range I use to
pull out all the patients who have their operation cancelled (field name:
"Date of Cancellation") for a specified period. I use a "between" function on
the query to do this.

Is there a way I can title the report "CancOps_xx to yy" where xx and yy are
the two dates entered when the query is run?

Thanks,

David Hawes

 

[Nikos Yannacopoulos]

David,

My proposed way to do this involves a form with two unbound controls,
where the user provides the two dates, and a command button which fires
some simple VBA code for the TransferSpreadsheet action (instead of a
macro). The idea is that (a) the query reads the two dates from two
unbound controls on the form, instead of taking them as parameters, and
(b) the code is run by means of a command button on the same form, and
reads the two dates from the form also. The code could look something like:

Dim strFile As String
strFile = "C:\SomeFolder\CancOps_" & Me.ctlDateFrom & _
" to " & Me.ctlDateTo
Docmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "YourQueryName" ,strFile

I hope my naming assumptions are clear, so you can substitute with the
actual object names.

HTH,
Nikos