Append record information to Macro output file name

[artemetis]

I have a macro that is exporting records for a particular employee to an xls.
The default output filename is qryEmpItems.xls
Is there a way to have the macro append the employee name to the actual xls filename?

I'm looking for an end result such as qryEmpItems_EMPLOYEENAME.xls
Or better yet EMPLOYEENAME_Survey.xls

Not sure if this is a possibility.
Thanks!

[MindBender77]

@artemetis
I think the easiest way is to call an inputbox from your macro. I'm assuming your using transferspreadsheet. Add this to the file name argument:

="c:\" & InputBox("Enter the employee name that's in the file name:") & "_Survey.xls"
 

Hope this helps,
Bender

[DonRayner]

I don't think it's possible to do using a macro. Take a look at using the OutputTo command in VBA.

[artemetis]

I'm trying that now, with some error.

I was using the OutputTo method.

When I use your approach, it errs out stating the file does not exist?

Ultimately, I'd like to have the 25 different files for each employee.
I don't mind the prompt box though.
:o)

[MindBender77]

The syntax I provided for using the inputbox will work with "OutputTo" as well.

The arguements are:

Object Type: Query
Object Name:  Name of Your Query
Output Format: Microsoft Excel (*.xls)
Output File: ="c:\" & InputBox("Enter the employee name that's in the file name:") & "_Survey.xls"
 

HTH,
Bender

[artemetis]

Yes it did!
Thank you!!!!