By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 1,772 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Append record information to Macro output file name

P: 76
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!
Jan 9 '09 #1
Share this Question
Share on Google+
5 Replies


MindBender77
100+
P: 234
@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:
Expand|Select|Wrap|Line Numbers
  1. ="c:\" & InputBox("Enter the employee name that's in the file name:") & "_Survey.xls"
  2.  
Hope this helps,
Bender
Jan 9 '09 #2

DonRayner
Expert 100+
P: 489
I don't think it's possible to do using a macro. Take a look at using the OutputTo command in VBA.
Jan 9 '09 #3

P: 76
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)
Jan 9 '09 #4

MindBender77
100+
P: 234
The syntax I provided for using the inputbox will work with "OutputTo" as well.

The arguements are:
Expand|Select|Wrap|Line Numbers
  1. Object Type: Query
  2. Object Name:  Name of Your Query
  3. Output Format: Microsoft Excel (*.xls)
  4. Output File: ="c:\" & InputBox("Enter the employee name that's in the file name:") & "_Survey.xls"
  5.  
HTH,
Bender
Jan 9 '09 #5

P: 76
Yes it did!
Thank you!!!!
Jan 9 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.