export to .xls button file naming

[yaro.kobzar]

Hi everyone,
I'm have created a button on a report that will export the report to an excel datasheet.
Here is what I have as the code...

DoCmd.OutputTo acOutputReport, "Class Roster By Instructor ID", acFormatXLS, "S:\BasicSkills\BaSIS Output\ClassRoster" & Format(Date, "yyyymmdd") & ".xls"

Here is the problem: I was wondering if it's possible to set the name of the instructor in the file name? The field Instructor/Team Name contains the name of the instructor and I was wondering if it can put that in the name of the file too...

Right now, I get ClassRoster20110526.xls
Is it possible to get something like Class Roster Anderson 05262011.xls?

 

[Trevor G]

You can refer to the value of the field if it is on a form so it would be something like this. I use the word me to find the object name and to confirm it exists.

DoCmd.OutputTo acOutputReport, "Class Roster By Instructor ID", acFormatXLS, "S:\BasicSkills\BaSIS Output\ClassRoster" & me.FieldName.value & Format(Date, "yyyymmdd") & ".xls"

 

[yaro.kobzar]

Awesome thanks it worked!
Here's the file name that was created: Class RosterPrehn05272011

Is there anyway to put a space between Class Roster and Prehn and a space between instructor name and the date?

 

[cyrusv]

You can try using this,

DoCmd.OutputTo acOutputReport, "Class Roster By Instructor ID", acFormatXLS, "S:\BasicSkills\BaSIS Output\ClassRoster" & me.FieldName.value & " + " " + " & Format(Date, "yyyymmdd") & ".xls"

 

[Trevor G]

Awesome thanks it worked!
Here's the file name that was created: Class RosterPrehn05272011

Is there anyway to put a space between Class Roster and Prehn and a space between instructor name and the date?

Add an extra space after the Class Roster and then & me.FieldName& " "&your date field so it will look like this

DoCmd.OutputTo acOutputReport, "Class Roster By Instructor ID", acFormatXLS, "S:\BasicSkills\BaSIS Output\ClassRoster " & me.FieldName.value &" "& Format(Date, "yyyymmdd") & ".xls"