Featured Posts

<< >>

The Art of Automating Reports in Excel (Part-3)

Automating Reports in Excel: Expose the worksheet in read-only mode. [DIM XLAppSet XLApp = CreateObject("Excel.App")xlapp.visible=falsexlapp.workbooks.open \\excelloc\filename.xls,3,] Restore the information and save the report, in this example as a PDF with

The Art of Automating Reports in Excel (Part-2)

Assign code for individual calculation field. You will once more employ the similar 2 lines as explained above, but in this situation your ActiveCell. Value represents numeric function, like, SUM,

The Art of Automating Reports in Excel (Part-1)

One of the numerous attributes of MS Excel is its capability for automating reports. Users can easily generate high quality spreadsheets for simplifying others’ capability to feed information into its

The Art of Automating Reports in Excel (Part-3)

Automating Reports in Excel: Expose the worksheet in read-only mode. [DIM XLAppSet XLApp = CreateObject("Excel.App")xlapp.visible=falsexlapp.workbooks.open \\excelloc\filename.xls,3,]

Restore the information 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 end product related document is to be in a altered format, try to add standby with the help of accurate extension lead for that format for “.pdf”.

Close the worksheet, but do not save it, then close Excel. [xlQualityStandardxlapp.activeworkbook.close Falsexlapp.quit]

Automating Reports in Excel: Employ “.xlsx” in place of “.xls” in the worksheet suffix if your worksheet was saved in Excel 2007 and former XML format.

  1. Compose a batch text for starting the V B script. This is important for the VS script to run in an automatic way. In absence of the batch text, the VB script must be run through manual methods. Your text would be in this format, by substituting your own folder and filename for those available  here, and overlooking the brackets: [cscript /nologo \\fileloc\script.vbs]

  2. Compose a batch text for verifying that the end result file survives as in the way it is generated. Your script must exhibits the tasks explained below. Each function will be tracked by code specified in brackets its implementation. When you compose the real code, generate it in a particular block, by substituting your own names for the sample names, and without enclosing the enclosing brackets.

Check to view that theend result file be existent. [For /f "tokens=2-4 delims=/ " %%a in ('date /t') do set rreport=reportname_%%c-%%a-%%b.pdf)] If the end result file’s format is not in the PDF form, then view it by correct the file format by substituting for “.pdf” wherever provided.

If the end result file/report be existent, email it to the people who require 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 end result file/report does not be existent at the stated location, have the method send you a communication 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)]

  1. Perform the verification that the “Desktop” folder is available on the PC. You have to confirm the Desktop folder’s actuality for individually a 32-bit and a 64-bit system. If you don’t accomplish this, Excel and your spreadsheet have to be released manually.

32-bit system location: c:\windows\system32\config\systemprofile

64-bit system location: c:\windows\syswow64\config\systemprofile

  1. Program an assignment for running the scripts as required.

  2. This whole process will automate the Excel report.

The Art of Automating Reports in Excel (Part-2)

  1. Assign code for individual calculation field. You will once more employ the similar 2 lines as explained above, but in this situation your ActiveCell. Value represents numeric function, like, SUM, instead of the InputBox function is employed to show an input function.

  2. Increase a line of code for saving your shared worksheet. The format will be like, “ActiveWorkbook.SaveAs Filename:=”Filename.xls”,” here “Filename”showcase the name of your worksheet. Take account of the quotes all over the place like “Filename.xls,” but not across the entire entry.

Automating Reports in Excel: In case If you are using Excel 2007 or later, you can use it by adding additional alphabet in the end of the word like, “.xlsx” for “.xls,” but in case if other users who will be using spreadsheet have Excel 2003 or former, they would not  be able to employ the worksheet in the absence of plug-in reader suite.

  1. Hit the Alt and Q keys at the same time. This will result in closing of the VS editor.

  2. Hit the Alt and F8 keys at the same time. This will help in opening the Macro dialog box.

  3. Hit it off the name of your process in the Macro list. If yours is the single process from the list, it will be designated in an automatic way.

  4. Hit the Alternatives tab. You will be encouraged to feed a keyboard based character for using it as a keyboard shortcut in addition to the Ctrl key. Select a significant letter which is not till now employed as a shortcut character, for example “e” for “entry.”

  5. Hit “OK” for closing the Macro alternative dialog box. You can now hand out your interactive worksheet to those who will make use out of it. After opening it, they can employ the shortcut key for enabling entry and following the stimulators you generated for filling in their information.

Method 2: Automating Reports in Excel

  1. Build your report into a PivotTable. PivotTables are constructed for summarizing information for helping you in comparing numbers and identifying rends. Your PivotTable should be associated to information somewhere else in your worksheet or imported from a file.

  2. Compose a VB script for opening and closing the report. Your characters must accomplish the tasks enumerated below. Each task will be defined properly monitored by the code specified in brackets for implementing it. When you compose the definite code, write it in a single block, substituting it by your own names for the sample names, and do not take account of the brackets that surround the complete illustration.

The Art of Automating Reports in Excel (Part-1)

From Microsoft Office resources

From Microsoft Office resources

One of the numerous attributes of MS Excel is its capability for automating reports. Users can easily generate high quality spreadsheets for simplifying others’ capability to feed information into its books, and users can also systematize the creation of its reports. Equally these characteristics need some familiarity and understanding of Visual Basics. The steps to accomplish both jobs are defined below for automating reports in Excel.

Method 1:  Generating an Interactive Spreadsheet – Automating Reports in Excel

  1. Select the layout of worksheet. Your worksheet should be arranged properly so that others employing it can find the areas they require swiftly for entering data. You can spread your worksheet in a horizontal or vertical way. Most of the users will discover a vertically laid layout stress-free to use, mainly if you are planning to print the worksheet.

  2. Generate typescript labels for the worksheet. You will need a label at the top most part of each column, in addition to a label in the cell to the left side of every cell in the column where you are planning to set the item fields.

  3. Now Press both the Alt and F11 keys at the same time. This opens the MS VS editor.

  4. By Double-clicking “Workbook name” in the “Project-VBA Project” pane from the upper left side. This will help in opening a code generating window in the key area of the editor.

  5. Choose “Method” from the Insert menu. This shows the Add Method dialog box.

  6. Fill a name for the process in the Name field. Offer the process a significant name, such as “SumExpenses” if your intuitive spreadsheet will be employed for generating report for some sort of expenses. After this click OK for closing the dialog box. Your method name cannot contain spaces, but you can employ an underscore ( _ ) instead of  a space. When the Add Process dialog box ends, you will get a line labeled “Public Sub” trailed by the name of your process. Underneath that line, you will find a space and the words “End Sub.”

  7. Give code for each input field in the worksheet. You will give 2 lines of code for every entry. The initial line of code get the form “Range(“cellname”).Select”, at this place “cellname” signify the cell in which the response field will exist.

This should be the cell directly to the right of a script label; if you have a script label in cell A2, you would put an input field in cell B2 (Range(“B2″).Select). Take account of the quotes all over the place of the cell name, but not for those which are around the whole code account.

The 2nd line of code get the form “ActiveCell.Value = InputBox(“InputPrompt”)”, where “InputPrompt” showcase script that will seem like they need  to inform the user about the type of information to enter in the input cell.