Automation on Excel sheet (Macros)

Introduction

Those who know VB script well, then without having any automation tool they can do automation using excel sheet itself. Well, it is another platform where you can create your own or customized GUI screen and develop automation code according to your requirements.

What you should know to start automation in Automation on Excel sheet (Macros)? 

Steps involved while you wanted to do basic automation using Automation on Excel sheet (Macro)

1. Open excel sheet from MS Office
2. Select VIEW menu
3. Then, click Marco (see at right side)
4. And, click Record Macro
5. Then, Record Macro pop up window appear
6. Then, Record Macro pop up window appears
7. Here, provide Macro Name, Shortcut Key (only single character allowed) and provide the location you wanted the Macro to store
8. Lastly, click on OK button
9. Then perform any operation on excel sheet (Example enter some values in 3 rows and 3 columns)
10. Again, select VIEW menu and next click on Macros then click on Stop Recording option
11. It's time to clear the content you have recorded just now and then
12. Now, select VIEW menu and next click on Macros then click on View Macros option to view the macro you recorded just now and click on Run button
13. Hope you have viewed the actions that you have performed on the excel sheet.

NOTE: To see the code how excel sheet has been written automatically. Press ALT+F11 then go the Module (Project Explorer) where your excel sheet is present and click on that model to view the code as show in the below picture.

Suggestion

Once you understand how the excel automatically capturing the automation code, then try with the all excel operations you are familiar with and understand the code that excel writes automatically on the "Microsoft Visual Basic for Applications". If the excel automation process is clear and build confidence in you then you can scale yourself as intermediate in creation of macros using excel sheet.

Steps involved while you wanted to do Intermediate automation using Automation on Excel sheet (Macro)

1. Open excel sheet from MS Office
2. Select VIEW menu
3. Use keyboard, select ALT + F11 to open  the "Microsoft Visual Basic for Applications - Book1", see the below snap for the details.
4. Select the module (Project Explorer) of the sheet you are working on
5. Now, select Insert Menu and then click on User Form, then you can view the screenshot as shown below
6. Once, user form is shown, as per your requirement select the GUI objects from the Toolbox by drag & drop that selected object with the Mouse.
7. Now, double click on the object to view the function prototype for that object to start your coding. See the template in the below picture
8. Write the code in it and click on the RUN macro icon (shown in the above snapshot)
9. Now, you will experience as per the code. It will throw an error if there are any mistakes in the code you have written.

See the Post for more information on the below topic.

VB script to Interact with Application Program Interfaces (API's) - CreateObject

• Interact with any browser
• Interact with document
• Interact with excel itself (as we demonstrated above)
• Interact with Quick Test Professional (QTP/UFT) 
• Interact with Quality Center (Refer Open Test Architecture -Working with ALLM)