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