Thank you for this tip, I think it's will be very useful for me. I'm trying to retrofit it to use in our current expense report template.
I was wondering how I could make the "SaveAs Filename" unique. Would it be possible to have it include the System Date perhaps as part of the filename? So in the example the filename would be, "Travel Expense Report 2007-10-23.xls"
Also, Is there a way to automatically email the report to a set address when it's complete?
Thank you once again sharing this tip.
PS
I'd be nice, if there was forum like this that would teach people VB for Apps, by sending tips, examples, and coding conventions weekly.
Discussion on:
View:
Show:
For saving create this macro:
Private Sub Workbook_Open()
fname = "Travel expense report "& format(now,"YYYY-MM-DD")
ThisWorkbook.SaveAs Filename:=fname
End Sub
You may need or want to put a path into the filename as well.
For opening an email client with the file attached:
Application.Dialogs(xlDialogSendMail).Show
However, that will not force somebody to send it.
John
Private Sub Workbook_Open()
fname = "Travel expense report "& format(now,"YYYY-MM-DD")
ThisWorkbook.SaveAs Filename:=fname
End Sub
You may need or want to put a path into the filename as well.
For opening an email client with the file attached:
Application.Dialogs(xlDialogSendMail).Show
However, that will not force somebody to send it.
John
For the above file I want to save it in a different network folder depending on the name of a certain field. For instance C1 could be their name. Is it possible to tell it to check the name and save it to a certain folder?
If cell c1 is their name, how can I include that into the filename.
fname = "(value of C1)'s travel expense report "& format(now,"YYYY-MM-DD")
Thanks
Michael
fname = "(value of C1)'s travel expense report "& format(now,"YYYY-MM-DD")
Thanks
Michael
Here is one way that might help you in other areas as well. This method uses concat to build the filename. Someone asked about a path as well. I will include one that will save to a machine that is accessible on a network. If you can map the following, then this network path will work:
//computer_name/data -- this must be shared
so here it is:
'sets the save path
var_path="//computer_name/data/"
'gets the name on sheet 1 cell C1
var_name=worksheets("sheet 1").range("C1").value
'gets the date in format 2007-07-04
var_date=format(now,"YYYY-MM-DD")
'sets the title for the file
var_title="'s travel expense report "
'builds the entire filename for the save and includes the path
fname=var_path & var_name & var_title & var_date
...etc...
Yes, I know... go ahead and chastise me for including comments in my example. But that is just me. Trying to teach good habits at the same time.
//computer_name/data -- this must be shared
so here it is:
'sets the save path
var_path="//computer_name/data/"
'gets the name on sheet 1 cell C1
var_name=worksheets("sheet 1").range("C1").value
'gets the date in format 2007-07-04
var_date=format(now,"YYYY-MM-DD")
'sets the title for the file
var_title="'s travel expense report "
'builds the entire filename for the save and includes the path
fname=var_path & var_name & var_title & var_date
...etc...
Yes, I know... go ahead and chastise me for including comments in my example. But that is just me. Trying to teach good habits at the same time.
fname = var_path & var_name & var_title & var_date
ThisWorkbook.SaveAs Filename:=fname
End Sub
When I run the Macro I get a Run time error '1004':
Application-defined or object-defined error.
ThisWorkbook.SaveAs Filename:=fname
End Sub
When I run the Macro I get a Run time error '1004':
Application-defined or object-defined error.
Here is mine... it is working... was playing with LINUX and had the wrong slashes. See if this works.
Sub email()
'
' Written by Ken Johnson
' saves the file to a computer and directory at var_path
' Then it sends an email to the recipient with the file attached
'
'sets the save path
var_path = "\\LAPTOP\data\"
'gets the name on sheet 1 cell C1
var_name = Worksheets("Sheet1").Range("C1").Value
'gets the date in format 2007-07-04
var_date = Format(Now, "YYYY_MM_DD")
'sets the title for the file
var_title = "'s travel expense report "
'builds the entire filename for the save and includes the path
fname = var_path & var_name & var_title & var_date
ThisWorkbook.SaveAs Filename:=fname
ActiveWorkbook.SendMail Recipients:="my.name@net.net", Subject:="Monthly from " & var_name
'
End Sub
Sub email()
'
' Written by Ken Johnson
' saves the file to a computer and directory at var_path
' Then it sends an email to the recipient with the file attached
'
'sets the save path
var_path = "\\LAPTOP\data\"
'gets the name on sheet 1 cell C1
var_name = Worksheets("Sheet1").Range("C1").Value
'gets the date in format 2007-07-04
var_date = Format(Now, "YYYY_MM_DD")
'sets the title for the file
var_title = "'s travel expense report "
'builds the entire filename for the save and includes the path
fname = var_path & var_name & var_title & var_date
ThisWorkbook.SaveAs Filename:=fname
ActiveWorkbook.SendMail Recipients:="my.name@net.net", Subject:="Monthly from " & var_name
'
End Sub
I am trying to automate the filling out of an Excel form by using another excel form, then FileSaveAs the one I am filling into.
How do I get the original Excel file to run this code FOR the the one that I am filling out?
How do I get the original Excel file to run this code FOR the the one that I am filling out?
This is actually pretty easy using another method as well. This will utilize whatever default email program the user has. Their email will open with the recipient(s), subject, and spreadsheet already populated.
ActiveWorkbook.SendMail Recipients:="john@corptest.com", Subject:="Monthly from" & var_name
This way, all they have to do is press send. You could always precede this with a messagebox to NOT ignore.
msgbox("Please click on send next.")
ActiveWorkbook.SendMail Recipients:="john@corptest.com", Subject:="Monthly from" & var_name
This way, all they have to do is press send. You could always precede this with a messagebox to NOT ignore.
msgbox("Please click on send next.")
All this does is come up with annoying input boxes for each expense - why not have a form with all the expenses listed? You could also have the form come up when the workbook is opened (after checking that none of the cells have values in them)
Excel is just a framework for the VB behind the scenes. You have great control with Forms that has code behind it all.
I use forms anywhere there is any sort of repetitive data entry. They are quite easy and logical as well as FAR more user friendly. They take a small amount of learning but is the correct solution for stopping all the popups.
The site Excel Reports, the #2 Excel website, has a great Excel tutorial. Highly recommended for people creating reports in Excel.
For more information, just follow this links:
Anchor Text 1: Excel Reports
URL 1: http://www.excelreports.info/
And for tutorials, just follow this link:
Anchor Text 2: Excel tutorial
URL 2: http://www.excelreports.info/2011/03/excel-reports-tutorial.html
For more information, just follow this links:
Anchor Text 1: Excel Reports
URL 1: http://www.excelreports.info/
And for tutorials, just follow this link:
Anchor Text 2: Excel tutorial
URL 2: http://www.excelreports.info/2011/03/excel-reports-tutorial.html
I tried this but maybe I have a different version of (Microsoft Visual Basic 6.5) and once hitting alt F11, the VB window opens but step 11 canot be completed: In the Project-VBAProject pane, double-click ThisWorkbook under VBAProject. There is no "VBA Project" or "ThisWorkbook". I have literally looked everywhere and this option is not here. What do I need to do?
this discussion is allmost 5 years old, a zombie, and will probably be overlooked.
Try posting your problem in Q&A!
Try re-posting in the Q&A
section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here:
http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content
There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'.
Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". .Try re-posting in the Q&A
section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here:
http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content
There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'.
Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". .
Try posting your problem in Q&A!
Try re-posting in the Q&A
section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here:
http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content
There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'.
Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". .Try re-posting in the Q&A
section.The 'Discussion' forum is for matters of general discussion, not specific problems in search of a solution. The 'Water Cooler' is for non-technical discussions. You can submit a question to 'Q&A' here:
http://www.techrepublic.com/forum/questions/post?tag=mantle_skin;content
There are TR members who specifically seek out problems in need of a solution. Although there is some overlap between the forums, you'll find more of those members in 'Q&A' than in 'Discussions' or 'Water Cooler'.
Be sure to use the voting buttons to provide your feedback. Voting a '+' does not necessarily mean that a given response contained the complete solution to your problem, but that it served to guide you toward it. This is intended to serve as an aid to those who may in the future have a problem similar to yours. If they have a ready source of reference available, perhaps won't need to repeat questions previously asked and answered. If a post did contain the solution to your problem, you can also close the question by marking the helpful post as "The Answer". .
- Keyboard Shortcuts:
- Prev
- Next
- Toggle
