Report Automation Template Using Excel Macro

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



“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.


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.
   
   
   
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.
   
   

Step 2

Insert an embedded object in the spreadsheet.

1. On the insert tab click object to create a Word document embedded object.
   
   
   
   
2. Click the create from file tab then click browse to select the report template.
   
   
3. Check the 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.
   
   
   
3. Click the New button on the Assign macro window.
   
   
   
   
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.
   

   Public Irow As Integer
   Public i As Integer
   Public j As Integer
   Dim word Document As Object
   Dim document1 As Object
   Dim objword As Object
   Dim objdoc As Object
   Dim objSelection As Object
   Dim oleObj As OLEObject
   Irow = Sheet1.Cells (Rows. Count, "A").End (xlUp).Row - 7
   Set oleObj = Sheets ("Sheet1").OLEObjects (1)
   OleObj.Verb xlVerbOpen
   Set objword = oleObj.Object.Application
   With objword
       .Visible = False
       Set objdoc = .Documents (1)
   End With
   Objword. Documents (objdoc).Activate
   objword.Selection.WholeStory
   objword.Selection.Copy
   Set document1 = objword.Documents.Add
   Word. Documents (document1).Activate
   Word.Selection.EndKey wdStory
   Word.Selection.PasteAndFormat wdPasteDefault
   Word.Selection.WholeStory
   j = 8
   For i = 1 To irow
       Set document1 = objword.Documents.Add
       Word.Documents (document1).Activate
       Word.Selection.EndKey wdStory
       Word.Selection.PasteAndFormat wdPasteDefault
       Word.Selection.WholeStory
       With document1
           .Content.Find.Execute "<Name>", ReplaceWith: =Cells (j, 1).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<Add1>", ReplaceWith: =Cells (j, 2).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<Add2>", ReplaceWith: =Cells (j, 3).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<Add3>", ReplaceWith: =Cells (j, 4).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<Email>", ReplaceWith: =Cells (j, 5).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<City>", ReplaceWith: =Cells (j, 6).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<State>", ReplaceWith: =Cells (j, 7).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<Pin>", ReplaceWith: =Cells (j, 8).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<IntDate>", ReplaceWith: =Cells (j, 9).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<Designation>", ReplaceWith: =Cells (j, 10).Value, Replace: =wdReplaceAll
           .Content.Find.Execute "<sal>", ReplaceWith: =Cells (j, 11).Value, Replace: =wdReplaceAll
           .Save As "C:\Users\Karthikeyan.K\Desktop\Sample\" & Cells (j, 1).Value & ".pdf", 17
           j = j + 1
       End With
       Next i
       Set objword = Nothing
       Set document1 = Nothing

8. Add the following code between Sub RoundedRectangle2_Click() End sub.
   

   Dim OutApp as Object
   Dim OutMail As Object
   j = 8
   Irow = Sheet1.Cells (Rows. Count, "A").End (xlUp).Row - 7
   Set OutApp = Create Object ("Outlook. Application")
   Set OutMail = OutApp.CreateItem (0)
   On Error Resume Next
   With OutApp
       .Visible = False
   End With
   With Application
       .Screen Updating = False
       .Enable Events = False
   End With
   For i = 1 To irow
       With OutMail
           .To = Cells (j, 5).Value
           .CC = "[email protected]"
           .BCC = ""
           .Subject = "Offer Letter"
           .Body = "Hello World!"
           .Attachments. Add ("C:\Users\Karthikeyan.K\Desktop\Sample\" & Cells (j, 1).Value & ".pdf")
           .Send
           j = j + 1
       End With
       i = i + 1
       Next i
       On Error Go To 0
       Set OutMail = Nothing
       Set OutApp = Nothing
       With Application
           .Screen Updating = True
           .Enable Events = True
       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.
   
   
   
   
3. Click the Print All button to check the directory.
   
   
   
   The following figure shows the 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.)