Ways to Automate Excel Reports
How to Automate Excel Reports for Excel Dashboard Reporting: Automating of data is very essential because reports are very long and manual entry of data is cumbersome. Among the many features of Microsoft Excel is the ability to automate reports.
To simplify your ability to enter data into workbook you need to create an interactive spreadsheet. You can also automate generation of its reports.
How to Automate Excel Reports: Creation of an Interactive Spreadsheet
You need to decide on the layout of your spreadsheet.
The spreadsheet should be laid out in a way that others using it can find those fields they are looking for quickly to enter data. In this case you can decide to lay your spreadsheet horizontally or vertically. For most users the vertical arrangement is much easier to use.
Next create the text labels for the spreadsheet. At this point you may want a label at the top of every column. Also label in the cell at the left of each cell in the column here you plan to put your entry fields. Next press the Alt + F11 keys and open Microsoft Visual Basic editor. At the Project-VBA Project pane in the upper left, double click This Workbook dialogue box.
Doing this will open the Visual Basic editor. From the Insert menu select Procedure and at the Add Procedure dialogue box, enter name of the procedure in the Name field then press OK. The name cannot include spaces, underscores may be used instead.
On closing the Add Procedure box, Public Sub line will be seen followed by your procedure’s name. And below the line you will see a space and the End Sub words.
To enter code for each input field in the spreadsheet you will need to write two lines of code for every input. First a code line as Range (Cellname) Select, the cellname represents the cell where the input field will go. It should be the cell immediately to the right of text label. Second line of code is in the form, ActiveCell.Value=InputBox (InputPrompt), the Input prompt representing the text to appear to tell the user the kind of data to enter in the input cell.
Again use the two lines as described before to enter code for each calculation field, but the ActiveCell.Value this time is the calculation or numeric function like SUM, at the InputBox function for displaying input prompt.
Then add a line code in the form ActiveWorkbook.SaveAs Filename: =Filename.xls. Filename represents name of your interactive spreadsheet. Hit Alt + Q keys, then Alt + F8 keys. Following this will be clicking the name of your procedure in the Macro list. Click Options button and hit the Ctrl key. Choose meaningful letter that has not been used then click Ok.
Automating Report Generation
Make the report into PivotTable that is connected to data elsewhere in the spreadsheet or imported from another database. Open spreadsheet in read-only mode. [DIMXLAppSe XLApp = CreateObject (Excel.App) xlapp.visible=falsexapp.workbooks.open\excellocfilename.xls, 3,].
Then refresh and save. Then [Truexlapp.activeworkbook.RefreshAllxlapp.activeworkbook.ExportAsFixedFormat xlTypePDF, \pdflocreportname_ & DatePart(yyyy,Now()) & – & Right(0 & Date Part(m, Now()),2) & – Right(0 & DatePart(d, Now()),2) & .pdf”] and close spreadsheet without saving.
Write batch script to start Visual Basic Script. Then write script to verify output file exists as created. Verify the Desktop folder exists on your computer and schedule a task to run the scripts as necessary.
For non-tecnical users: Download ready to use excel dashboard templates and simplify your management reporting without any coding and programming.