How to Automate Reports in Excel
Method 1 of 2: Creating an Interactive Spreadsheet
- 1Decide on the layout of your spreadsheet. Your spreadsheet should be laid out so that others using it can find the fields they need quickly to enter data.
- You can lay out your spreadsheet horizontally or vertically. Most users will find a vertical layout easier to work with, particularly if you plan to have them print out the spreadsheet.
Ad - 2Create text labels for the spreadsheet. You'll want a label at the top of each column, as well as a label in the cell to the left of each cell in the column where you plan to place the entry fields.
- 3Press the Alt and F11 keys simultaneously. This opens the Microsoft Visual Basic editor.
- 4Double-click "ThisWorkbook" in the "Project-VBA Project" pane in the upper left. This opens a coding window in the main section of the editor.
- 5Select "Procedure" from the Insert menu. This displays the Add Procedure dialog box.
- 6Enter a name for the procedure in the Name field. Give the procedure a meaningful name, such as "SumExpenses" if your interactive spreadsheet will be used for reporting travel expenses. Click OK to close the dialog.
- Your procedure name cannot include spaces, but you can use an underscore ( _ ) in place of a space.
- Once the Add Procedure dialog box closes, you will see a line labeled "Public Sub" followed by the name of your procedure. Beneath that line will be a space and the words "End Sub."
- 7Enter code for each input field in the spreadsheet. You'll write two lines of code for each input.
- The first line of code takes the form "Range("cellname").Select", where "cellname" represents the cell in which the input field will go. This should be the cell immediately to the right of a text label; if you have a text label in cell A2, you'd put an input field in cell B2 (Range("B2").Select). Include the quotes around the cell name, but not those around the complete code statement.
- The second line of code takes the form "ActiveCell.Value = InputBox("InputPrompt")", where "InputPrompt" represents text that will appear to tell the user what kind of data to enter in the input cell. For example, if the input cell is to enter meal expenses, you'd replace "InputPrompt" with "Enter total of all meals, including tips." (Include the quotes around the input prompt text, but not those enclosing the entire command.)
- The first line of code takes the form "Range("cellname").Select", where "cellname" represents the cell in which the input field will go. This should be the cell immediately to the right of a text label; if you have a text label in cell A2, you'd put an input field in cell B2 (Range("B2").Select). Include the quotes around the cell name, but not those around the complete code statement.
- 8Enter code for each calculation field. You again use the same two lines as described above, but this time your ActiveCell.Value is a calculation or numeric function, such as SUM, in place of the InputBox function used to display an input prompt.
- 9Add a line of code to save your interactive spreadsheet. The format is "ActiveWorkbook.SaveAs Filename:="Filename.xls"," where "Filename" represents the name of your interactive spreadsheet. (Include the quotes around "Filename.xls," but not those around the complete example.)
- If you have Excel 2007 or later, you can substitute the suffix ".xlsx" for ".xls," but if some of the people who will use your interactive spreadsheet have Excel 2003 or earlier, they won't be able to use the spreadsheet without a plug-in reader program.
- 10Press the Alt and Q keys simultaneously. This closes the Visual Basic editor.
- 11Press the Alt and F8 keys simultaneously. This opens the Macro dialog box.
- 12Click the name of your procedure in the Macro list. If yours is the only procedure in the list, it will be selected automatically.
- 13Click the Options button. You will be prompted to enter a keyboard character to use as a keyboard shortcut with the Ctrl key. Choose a meaningful letter not already used as a shortcut character, such as "e" for "entry."
- 14Click "OK" to close the Macro Options dialog. You can now distribute your interactive spreadsheet to those who will use it. After opening it, they can use the shortcut key to enable entry and follow the prompts you created to fill in their data.
Method 2 of 2: Automating Report Generation
- 1Make your report into a PivotTable. PivotTables are designed to summarize data to let you compare numbers and identify trends. Your PivotTable should be connected to data elsewhere in your spreadsheet or imported from a database.
- 2Write a Visual Basic script to open and close the report. Your script must perform the functions listed below. Each function will be described followed by the code given in brackets to implement it. When you write the actual code, write it in a single block, substitute your own names for the example names, and do not include the brackets that enclose the entire example.
- Open the spreadsheet in read-only mode. [DIM XLAppSet XLApp = CreateObject("Excel.App")xlapp.visible=falsexlapp.workbooks.open \\excelloc\filename.xls,3,]
- Refresh the data and save the report, in this example as a PDF with a date stamp. [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, \\pdfloc\reportname_ & DatePart("yyyy,Now()) & "-" & Right("0" & DatePart("m",Now()),2) & "-" Right("0" & DatePart("d",Now()),2) & ".pdf"] If your output document is to be in a different format, substitute the correct extension for that format for ".pdf".
- Close the spreadsheet without saving it, then close Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Use ".xlsx" in place of ".xls" in the spreadsheet suffix if your spreadsheet was saved in Excel 2007 and later's XML-based format.
- 3Write a batch script to start the Visual Basic script. This is necessary for the Visual Basic script to be run automatically. Without the batch script, the VB script must be run manually.
- Your script would be in this format, substitution your own folder and filename for those given here, and omitting the brackets: [cscript /nologo \\fileloc\script.vbs]
- 4Write a batch script to verify the output file exists as created. Your script must perform the functions described below. Each function will be followed by code given in brackets to implement it. When you write the actual code, write it in a single block, substitute your own names for the example names, and do not include the enclosing brackets.
- Check to see that the output file exists. [For /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] If the output file's format is not a PDF, substitute the correct file format for ".pdf" where given.
- If the output file/report exists, email it to the people who need it. [If exist \\pdfloc\%rreport% ( sendemail -f sender@senderdomain.com -t recipient@recipientdomain.com -u Scheduled Report -m Report %%report% is attached. -a \pdfloc\%rreport% -s yourserver:port -xu username -xp password)]
- If the output file/report doesn't exist at the specified location, have the procedure send you a message that delivery failed. [ Else ( sendemail -f sender@senderdomain.com -t sender@senderdomain.com -u Report did not run -m file %rreport% does not exist in \\pdfloc\ -s yourserver:port -xu username -xp password)]
- 5Verify the "Desktop" folder exists on the computer. You have to verify the Desktop folder's existence for both a 32-bit and a 64-bit system. If you don't do this, Excel and your spreadsheet have to be opened manually.
- 32-bit system location: c:\windows\system32\config\systemprofile
- 64-bit system location: c:\windows\syswow64\config\systemprofile
=====================================================================
The Project Window in the VBE of Excel
Note: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.
As you can see, the Project window shows you all the workbooks that are open ("Book1") in the example below) and their components. You can use the + and - signs to show the details.
A new Excel workbook includes three sheets and another component named "ThisWorkbook". As we will see later in lesson 9 on events "ThisWorkbook" is a component in which you will store the macros ( also called VBA procedures) that should start automatically when the workbook is opened.
Working within the Project Window
We will now complete a brief exercise to learn how easy it is to work within the Project Window.
Exercise 2 (Create your first macro and use it)
Step 1: Using the ALT/F11 key go back to Excel.
Step 2: Add a sheet. Right-click on the tab of Sheet2 and select "Insert".
Step 3: In the dialog window that appears, click on "OK".
Step 4: Using the "ALT/F11" key, go back to the Visual Basic Editor and see that a sheet has been added to the workbook. Notice that the worksheets are sorted alphabetically in the Project window even if they are not in the workbook.
If you have purchased and downloaded the course on Excel Macros and opened the Excel file "vba-tutorial-editor.xls" plus a new workbook you will see this:
In the picture above you can see that the VBAProject named "Book1.xls" has 3 sheets and ThisWorkbook.The workbook "vba-tutorial-editor.xls" has 7 sheets, two userforms, two modules plus the "ThisWorkbook" object.
- Userforms are dialog windows (see example image below) that you develop to communicate with the users of your Excel programs and ask them to supply information or make choices.
- Modules are folders in which you save one or many of your macros. You can export and save these modules to be used later in other workbook.
In the complete lesson 2 you will learn how to add any type of components and how to remove, import, export and manage them from the Project window.
Close the VBE and close Excel without saving anything.
Here is a sample of what you will find in chapter 3
of the downloadable Tutorial on Excel macros
of the downloadable Tutorial on Excel macros
The Properties Window in the VBE of Excel
Note: Print this page, open Excel and a open a new workbook. Use ALT/F11 to open the Visual Basic Editor as you learned in lesson 1.
The Properties window shows you the properties of the component that is selected in the Project Window (single click). For example in the new workbook if you single click on "Sheet1" in the Project Window you see the properties of sheet1 in the Properties Window like in the image below.
As you can see, a worksheet has 12 properties that you can change in this Properties window. Notice that there are 2 "Name" properties. On the first line there is the programmatical name of the sheet (Sheet1). You will discover later the advantages and disadvantages of changing this property. The second "Name" property (9th line) is the name (or caption) that appears on the tab of the sheet in Excel.
Changing the "Name" Property
Ref http://www.excel-vba.com/excel-vba-solutions-beginners.htm
=======================================================================
Excel VBA (Visual Basic for Applications) is the name of the programming language of Excel.
1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how to create a simple macro.
2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.
3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA.
4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important object of Excel VBA.
5 Variables: This chapter teaches you how to declare, initialize and display a variable in Excel VBA.
6 If Then Statement: Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.
7 Loop: Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range of cells with just a few codes lines.
8 Macro Errors: This chapter teaches you how to deal with macro errors in Excel.
9 String Manipulation: In this chapter, you'll find the most important functions to manipulate strings in Excel VBA.
10 Date and Time: Learn how to work with dates and times in Excel VBA.
11 Events: Events are actions performed by users which trigger Excel VBA to execute code.
12 Array: An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by using the array name and the index number.
13 Function and Sub: In Excel VBA, a function can return a value while a sub cannot.
14 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object gives access to a lot of Excel related options.
15 ActiveX Controls: Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc.
16 Userform: This chapter teaches you how to create an Excel VBA Userform.
Ref http://www.excel-easy.com/vba.html
Email ITClassSL@gmail.com
Will provide MS Excel report automation creating charts reports, MS Outlook automation and classes
