Report Automation Template Using Excel Macro

Excel automation is one of the features in Excel. Excel automation can automate the generation of reports.

Excel automation

This is what you are expecting right? Yes.

No problem, Excel will walk you through the following entire process.

If you are new to Excel macros then read my previous article Create Login Application in Excel Macro Using Visual Basic. Because you must understand some basic things of Excel macros, such as how to enable the developer tab and how to assign a macro for Excel objects.

Chapter 1

In this chapter you will learn how to make a report automation template look attractive using an Excel macro in a spreadsheet.

excel macro in spreadsheet
Step 1

Create Shapes in the spreadsheet.

  1. Start the Excel application.

  2. On the Insert tab, click the Shapes collection.

  3. Choose the Rounded rectangle in the Rectangles collection.

    Rectangles collection
  4. Select the Rectangle shape, then draw two shapes in the spreadsheet.

  5. Select the shape then click on the format tab and make some changes in the shape effects so it looks as in the following diagram.

    shape effects look

Step 2

Insert an embedded object in the spreadsheet.

  1. On the insert tab click object to create a Word document embedded object.

    create word document

  2. Click the create from file tab then click browse to select the report template.

  3. Check the display as icon.

    display as icon

Step 3

Assign a macro for objects.

  1. Select the rectangle shape1 then double-click the object with selection mode.

  2. Right-click the shape1 to choose the Assign macro.

    Assign macro
  3. Click the New button on the Assign macro window.

    Click new button

  4. Follow the sample process to assign the macro shape2 object.

  5. Open the Visual Basic Editor.

  6. In the Project Explorer, right-click module1.

  7. Add the following code between Sub RoundedRectangle1_Click() End sub.
    1. Public Irow As Integer  
    2. Public i As Integer  
    3. Public j As Integer  
    4. Dim word Document As Object  
    5. Dim document1 As Object  
    6. Dim objword As Object  
    7. Dim objdoc As Object  
    8. Dim objSelection As Object  
    9. Dim oleObj As OLEObject  
    10. Irow = Sheet1.Cells (Rows. Count, "A").End (xlUp).Row - 7  
    11. Set oleObj = Sheets ("Sheet1").OLEObjects (1)  
    12. OleObj.Verb xlVerbOpen  
    13. Set objword = oleObj.Object.Application  
    14. With objword  
    15.     .Visible = False  
    16.     Set objdoc = .Documents (1)  
    17. End With  
    18. Objword. Documents (objdoc).Activate  
    19. objword.Selection.WholeStory  
    20. objword.Selection.Copy  
    21. Set document1 = objword.Documents.Add  
    22. Word. Documents (document1).Activate  
    23. Word.Selection.EndKey wdStory  
    24. Word.Selection.PasteAndFormat wdPasteDefault  
    25. Word.Selection.WholeStory  
    26. j = 8  
    27. For i = 1 To irow  
    28.     Set document1 = objword.Documents.Add  
    29.     Word.Documents (document1).Activate  
    30.     Word.Selection.EndKey wdStory  
    31.     Word.Selection.PasteAndFormat wdPasteDefault  
    32.     Word.Selection.WholeStory  
    33.     With document1  
    34.         .Content.Find.Execute "<Name>", ReplaceWith: =Cells (j, 1).Value, Replace: =wdReplaceAll  
    35.         .Content.Find.Execute "<Add1>", ReplaceWith: =Cells (j, 2).Value, Replace: =wdReplaceAll  
    36.         .Content.Find.Execute "<Add2>", ReplaceWith: =Cells (j, 3).Value, Replace: =wdReplaceAll  
    37.         .Content.Find.Execute "<Add3>", ReplaceWith: =Cells (j, 4).Value, Replace: =wdReplaceAll  
    38.         .Content.Find.Execute "<Email>", ReplaceWith: =Cells (j, 5).Value, Replace: =wdReplaceAll  
    39.         .Content.Find.Execute "<City>", ReplaceWith: =Cells (j, 6).Value, Replace: =wdReplaceAll  
    40.         .Content.Find.Execute "<State>", ReplaceWith: =Cells (j, 7).Value, Replace: =wdReplaceAll  
    41.         .Content.Find.Execute "<Pin>", ReplaceWith: =Cells (j, 8).Value, Replace: =wdReplaceAll  
    42.         .Content.Find.Execute "<IntDate>", ReplaceWith: =Cells (j, 9).Value, Replace: =wdReplaceAll  
    43.         .Content.Find.Execute "<Designation>", ReplaceWith: =Cells (j, 10).Value, Replace: =wdReplaceAll  
    44.         .Content.Find.Execute "<sal>", ReplaceWith: =Cells (j, 11).Value, Replace: =wdReplaceAll  
    45.         .Save As "C:\Users\Karthikeyan.K\Desktop\Sample\" & Cells (j, 1).Value & ".pdf", 17  
    46.         j = j + 1  
    47.     End With  
    48.     Next i  
    49.     Set objword = Nothing  
    50.     Set document1 = Nothing  
  8. Add the following code between Sub RoundedRectangle2_Click() End sub.
    1. Dim OutApp as Object  
    2. Dim OutMail As Object  
    3. j = 8  
    4. Irow = Sheet1.Cells (Rows. Count, "A").End (xlUp).Row - 7  
    5. Set OutApp = Create Object ("Outlook. Application")  
    6. Set OutMail = OutApp.CreateItem (0)  
    7. On Error Resume Next  
    8. With OutApp  
    9.     .Visible = False  
    10. End With  
    11. With Application  
    12.     .Screen Updating = False  
    13.     .Enable Events = False  
    14. End With  
    15. For i = 1 To irow  
    16.     With OutMail  
    17.         .To = Cells (j, 5).Value  
    18.         .CC = "[email protected]"  
    19.         .BCC = ""  
    20.         .Subject = "Offer Letter"  
    21.         .Body = "Hello World!"  
    22.         .Attachments. Add ("C:\Users\Karthikeyan.K\Desktop\Sample\" & Cells (j, 1).Value & ".pdf")  
    23.         .Send  
    24.         j = j + 1  
    25.     End With  
    26.     i = i + 1  
    27.     Next i  
    28.     On Error Go To 0  
    29.     Set OutMail = Nothing  
    30.     Set OutApp = Nothing  
    31.     With Application  
    32.         .Screen Updating = True  
    33.         .Enable Events = True  
    34.     End With  

Test the user form as in the following:

  1. Exit the Visual Basic Editor then enter the labels and data as shown below into rows.

  2. Create a directory with name “Sample” on your desktop.

    (Hint: Change the directory path in the macro code.)

    The following figure shows the final template in the spreadsheet.

    final template in spreadsheet

  3. Click the Print All button to check the directory.

    check the directory

    The following figure shows the final report generation.

    final report generation

  4. Click the Send mail button to send the reports to their email address.

(Hint: Before sending the email, configure your Microsoft Outlook.)