Using a parameter to name the spreadsheet with transferspreadsheet

[Patrick]

Hey!

I'm using the transferspreadsheet method to mass export query results from
access 2003. A bit of what I'm doing is:

DoCmd.TransferSpreadsheet acExport, , "qryMROSort", "C:\name.xls"

What I want to do is name the spreadsheet after the passed parameter from
the form to the query etc. If the value passed to the query is "MyName", i'd
like the .xls to be named MyName.xls. Something like:

DoCmd.TransferSpreadsheet acExport, , "qryMROSort",
"C:\"[forms]![switchboard]![MROchoice]".xls"

but it doesn't compile.

I've tried a variety of options - none which work.

Thank you!

 

[Nikos Yannacopoulos]

Patrick,

You're on the right track, just fix your syntax:

DoCmd.TransferSpreadsheet acExport, , "qryMROSort", "C:\" &
[forms]![switchboard]![MROchoice] & ".xls"

(all in one row, even though it will probably get wrapped in your
newsreader).

HTH,
Nikos

 

[Patrick]

Nikos,

Thank you! [stupid embarassed look on my face. . .]

Patrick

 

[FBueller]

Is there a way within an ADP to set up a file dialog box form control to
allow the user to select the file to import and bind it to a form control?

Nikos,

Thank you! [stupid embarassed look on my face. . .]

Patrick

Patrick,

You're on the right track, just fix your syntax:

DoCmd.TransferSpreadsheet acExport, , "qryMROSort", "C:\" &
[forms]![switchboard]![MROchoice] & ".xls"

(all in one row, even though it will probably get wrapped in your
newsreader).

HTH,
Nikos

 

[John Nurick]

See http://www.mvps.org/access/api/api0001.htm for code to display the
standard Open File dialog.

Is there a way within an ADP to set up a file dialog box form control to
allow the user to select the file to import and bind it to a form control?

Nikos,

Thank you! [stupid embarassed look on my face. . .]

Patrick

Patrick,

You're on the right track, just fix your syntax:

DoCmd.TransferSpreadsheet acExport, , "qryMROSort", "C:\" &
[forms]![switchboard]![MROchoice] & ".xls"

(all in one row, even though it will probably get wrapped in your
newsreader).

HTH,
Nikos