cres1121
asked on
Formating on a docmd.output to from Access subform Query to an excel spread sheet.
I have a wonderful subform that queries our sales and then filters on our salespeople. So it is a form and a subform.
Thru lots of reading, I can now send the subform filtered query to an excel format but I am having a little formatting issue.
This is the code I pasted together.
Private Sub Command37_Click()
Dim reportname As String
Dim theFilePath As String
reportname = "Salesmen Contract Expiration 90 days"
theFilePath = "Z:\unnamed service database\Reports\"
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
DoCmd.OutputTo acOutputForm, "sfrmexpiration", acFormatXLSx, theFilePath, True
MsgBox "Look on the z: drive in Unnamed Service Database\reports."
End Sub
It creates the report and puts it where I want it to go on the z drive and names it this
Salesmen Contract Expiration 90 days _2017-05-04.xlsx
Okay now, this is what I would like it to do....
I would like it to add the name of the filtered sales person to the file name. The field name is [cbSalesman] I would like to add that to the file name.
Thru lots of reading, I can now send the subform filtered query to an excel format but I am having a little formatting issue.
This is the code I pasted together.
Private Sub Command37_Click()
Dim reportname As String
Dim theFilePath As String
reportname = "Salesmen Contract Expiration 90 days"
theFilePath = "Z:\unnamed service database\Reports\"
theFilePath = theFilePath & reportname & "_" & Format(Date, "yyyy-mm-dd") & ".xlsx"
DoCmd.OutputTo acOutputForm, "sfrmexpiration", acFormatXLSx, theFilePath, True
MsgBox "Look on the z: drive in Unnamed Service Database\reports."
End Sub
It creates the report and puts it where I want it to go on the z drive and names it this
Salesmen Contract Expiration 90 days _2017-05-04.xlsx
Okay now, this is what I would like it to do....
I would like it to add the name of the filtered sales person to the file name. The field name is [cbSalesman] I would like to add that to the file name.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER