Automate Reports in Excel

One of the many features of Microsoft Excel is its ability to automate reports. You can create interactive spreadsheets to simplify others' ability to enter data into its workbooks, and you can also automate the generation of its reports. Both of these features require some knowledge of Visual Basic. The steps to perform both tasks are described below.

Steps

Creating an Interactive Spreadsheet

  1. Decide 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.
  2. Create 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.
  3. Press the Alt and F11 keys simultaneously. This opens the Microsoft Visual Basic editor.
  4. Double-click "This Workbook" in the "Project-VBA Project" pane in the upper left. This opens a coding window in the main section of the editor.
  5. Select "Procedure" from the Insert menu. This displays the Add Procedure dialog box.
  6. Enter 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."
  7. Enter 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.)
  8. Enter 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.
  9. Add 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.
  10. Press the Alt and Q keys simultaneously. This closes the Visual Basic editor.
  11. Press the Alt and F8 keys simultaneously. This opens the Macro dialog box.
  12. Click the name of your procedure in the Macro list. If yours is the only procedure in the list, it will be selected automatically.
  13. Click 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."
  14. Click "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.

Automating Report Generation

  1. Make 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.
  2. Write 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 XML-based format.
  3. Write 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]
  4. Write 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)]
  5. Verify 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
  6. Schedule a task to run the scripts as necessary. The batch scripts should be run sequentially on an ongoing basis, regardless of whether someone is using the computer or not. Privilege should be set to the highest setting possible.



Tips

  • Scheduled tasks are best executed from a server, usually from a system account with administrator privileges. The only drawback to executing tasks from a system account is that a user interface is not available; however, automated tasks typically are intended to run in the background, outside a user's notice.

Related Articles

Sources and Citations

Retrieved from "https://kipkis.com/index.php?title=Automate_Reports_in_Excel&oldid=349772"