Software

Save time in Excel with automated reports

You can use Excel programming to automate a lot of your employees' monthly reporting. For example, if your employees submit a monthly travel expense report, learn how you can develop an interactive application that does a lot of the work for them.

You can use Excel programming to automate a lot of your employees' monthly reporting. For example, if your employees submit a monthly travel expense report, you can develop an interactive application, such as the one that follows, that does a lot of the work for them. Follow these steps:

  1. Open a blank workbook.
  2. In A1, enter Category.
  3. In B1, enter Expense.
  4. In A2, enter Meals.
  5. In A3, enter Airfare.
  6. In A4, enter Ground Transportation.
  7. In A5, enter Tips.
  8. In A6, enter Lodging.
  9. In A7, enter Total.
  10. Press [Alt][F11].
  11. In the Project-VBAProject pane, double-click ThisWorkbook under VBAProject.
  12. Go to Insert | Procedure.
  13. Click in the Name text box and enter SumExpenses, then click OK.
  14. Enter the following code at the prompt:

    Range("B2").Select

    ActiveCell.Value = InputBox("Enter Total Meals including tips.")

    Range("B3").Select

    ActiveCell.Value = InputBox("Enter Round-trip Airfare")

    Range("B4").Select

    ActiveCell.Value = InputBox("Enter total for taxis, tolls, rented cars, etc")

    Range("B5").Select

    ActiveCell.Value = InputBox("Enter Other Tips")

    Range("B6").Select

    ActiveCell.Value = InputBox("Enter Hotel expense")

    Range("B7").Select

    ActiveCell.Value = "=Sum(B2..B6)"

    ActiveWorkbook.SaveAs Filename:="Travel Expense Report.xls"
  15. Press [Alt]Q.
  16. Press [Alt][F8].
  17. Click on SumExpenses in the Macro list.
  18. Click the Options button.
  19. In the Ctrl+ box, enter e, then click OK.

At the end of the month, you can send a copy of the worksheet to each employee. After opening it, they can press [Ctrl]E and then follow the prompts to fill in the report.

Miss an Excel tip?

Check out the Microsoft Excel archive, and catch up on other Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

18 comments
gringoaz
gringoaz

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?

Richard Noel
Richard Noel

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.

wizard57m-cnet
wizard57m-cnet

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". .

NayaGuru
NayaGuru

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)

yogi_john
yogi_john

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

kenneth.johnson9
kenneth.johnson9

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.

kfyap1
kfyap1

How do u do all that with excel?

kenneth.johnson9
kenneth.johnson9

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.")

mboyle
mboyle

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

mboyle
mboyle

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?

kenneth.johnson9
kenneth.johnson9

Excel is just a framework for the VB behind the scenes. You have great control with Forms that has code behind it all.

greaterdesigns
greaterdesigns

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?

Richard Noel
Richard Noel

Kenneth, Your code works great! Thank you, RN

kenneth.johnson9
kenneth.johnson9

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

mboyle
mboyle

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.

kenneth.johnson9
kenneth.johnson9

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