Microsoft Excel has various features and one of them is generating reports automatically. You can create interactive spreadsheets to simplify the process of entering data for others into the workbook, while also automating report generation. Both of these features require sufficient knowledge of Visual Basic. The steps to perform both tasks are described below.
Step
Method 1 of 2: Creating an Interactive Spreadsheet
Step 1. Determine the layout of the spreadsheet
The layout of the spreadsheet must be done so that others can find the fields needed to enter data.
Spreadsheet layouts can be laid out horizontally or vertically. Most users find it easier to work with a vertical layout, especially if the spreadsheet will be printed
Step 2. Create text labels for the spreadsheet
Write a label at the top of each column, and to the left of each cell in the column you plan to use as a data entry.
Step 3. Press alt=""Image" and F11 keys together.</h4" />
This key combination will open the Microsoft Visual Basic editor.
Step 4. Double-click "This Workbook" in the "Project-VBA Project" pane at the top left
A window for writing code will appear in the main section of the editor.
Step 5. Select "Procedure" from the Insert menu
The Add Procedure dialog box will appear.
Step 6. Enter the name of the procedure in the Name field
Enter a meaningful name for the procedure, such as "SumExpenses" if the spreadsheet will be used to report travel expenses. Click OK to close the dialog box.
- Procedure names cannot contain spaces, but can use an underscore (_) to replace spaces.
- After the Add Procedure dialog box closes, a line will appear labeled "Public Sub" followed by the name of the procedure. Below that line is a space and the words "End Sub."
Step 7. Enter the code for each input field in the spreadsheet
You will write two lines of code for each entry.
- The first line of code is in the form of "Range("cellname"). Select", "cellname" represents the cell where the input is entered. Fill in the name of the cell that is immediately to the right of the text label. If the text label is in cell A2, the field for input is cell B2 (Range("B2"). Select). The quotation marks before and after the cell name are still written at this stage, but are no longer included in the complete code statement.
- The code in the second line is "ActiveCell. Value = InputBox("InputPrompt")". "InputPrompt" represents the text that will appear to notify the user of the type of data that should be entered into the input cell. For example, if the input cell will be filled with food expenses, replace "InputPrompt" with "Enter total expenses for meals, including tips." (The quotation marks for the input instruction text are still included, while the quotation marks before and after the command do not need to be written.)
Step 8. Enter the code for each calculation field
Again the two lines used are the same as before, but this time ActiveCell. Value is a calculation of a numeric function, for example SUM, where the function InputBox function is used to display input instructions.
Step 9. Add a line of code to save your interactive spreadsheet
The format is "ActiveWorkbook. SaveAs Filename:="Filename.xls". "Filename" is the name of the interactive spreadsheet. (The quotation marks before and after "Filename.xls" remain written, while the quotation marks for all commands are not required.)
If you are using Microsoft Excel version 2007 or later, use ".xlsx" instead of ".xls". However, if there are interactive spreadsheet users with Excel 2003 version and below, they will not be able to use the spreadsheet without a reader plug-in
Step 10. Press alt=""Image" and Q keys simultaneously.</h4" />
The Visual Basic editor will close.
Step 11. Press alt=""Image" and F8 keys at the same time.</h4" />
The Macro dialog box will appear.
Step 12. Click the procedure name in the Macros list
If the procedure created is the only one in the list, it will be selected automatically.
Step 13. Click the Options button
You will be asked to enter a keyboard character to use as a shortcut with the Ctrl key. Choose a meaningful letter that hasn't been used as a shortcut character, such as "e" for "entry."
Step 14. Click "OK" to close the Macro Options dialog
Now you can distribute interactive spreadsheets to users. After opening it, the user can use the shortcut keys to make entries and follow the instructions you created to fill in the data.
Method 2 of 2: Automate Report Generation
Step 1. Create a report in a PivotTable
PivotTables are designed to summarize data so you can compare numbers and identify trends. The PivotTable must relate to data that is in one of the data processors or imported from a specific database.
Step 2. Write a Visual Basic script to open and close the report
The script should be able to perform the various functions listed below. Each function will be described followed by the code given in parentheses to implement it. When writing actual code, write it in a single block, replacing the name in the example with your own, and don't include parentheses at the beginning and end of the code sample.
- Open the spreadsheet in read-only mode. [DIM XLAppSet XLApp = CreateObject("Excel. App")xlapp.visible=falsexlapp.workbooks.open \excelloc\filename.xls, 3,]
- Reload the data and save the report, in this example it is saved as a PDF with the date caption. [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 the output document format is different, replace the ".pdf" format with the proper extension as desired.
- Close the spreadsheet without saving it, then close Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]
- Use ".xlsx" instead of ".xls" at the end of the spreadsheet if the spreadsheet is saved in Excel 2007 and a later XML-based format.
Step 3. Write a batch script to start the Visual Basic script
The purpose of writing the script is so that Visual Basic scripts can run automatically. Without batch script, VB scripts have to be run manually.
The script is written in the following format, replacing with your own folder name and filename for the name given in this example: [cscript /nologo \fileloc\script.vbs]
Step 4. Write a batch script to ensure that the output file exists as created
Your script should perform the below functions. Each function will be followed by the code given in parentheses to implement it. When writing actual code, write it in a single block, replacing the name in the example with the name you need, and don't include parentheses at the beginning and end of the sample code.
- Check if there is an output file. [For /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] If the output file format is not PDF, replace ".pdf" with the appropriate extension.
- If the output file/report exists, send it as an email to the person who needs it. [If exist \pdfloc\%rreport% (sendmail -f [email protected] -t [email protected] -u Scheduled Report -m Report %%report% is attached. -a \pdfloc\%rreport% -s yourserver:port -xu username -xp password)]
- If the output file/report does not exist in the specified location, create a procedure so that it can send you a message that the delivery failed. [Else (sendmail -f [email protected] -t [email protected] -u Report did not run -m file %rreport% does not exist in \pdfloc\ -s yourserver:port -xu username -xp password)]
Step 5. Make sure that the "Desktop" folder is on the computer
You must verify the existence of the Desktop folder for both 32-bit and 64-bit systems. Otherwise, Excel and the spreadsheet must be opened manually.
- 32-bit system location: c:\windows\system32\config\systemprofile
- 64-bit system location: c:\windows\syswow64\config\systemprofile
Step 6. Schedule tasks to run scripts as needed
Batch scripts must be executed sequentially and continuously, even if no one is using the computer. Privileges must be set to the highest possible setting.
