Some Useful Excel VBA Macro codes

Clear Content from specific Sheet

Sheets("Sheet1").Range("A2:Y2000").ClearContents

Copy paste from a sheet in one workbook to another

Set a = Workbooks.Open("C:\source.xlsx") source book
Set Z = ThisWorkbook 'open workbook on which VBA coding is done - destination

 vals1 = a.Sheets("Sheet1").Range("A2:X2000").Value
 Z.Sheets("Sheet1").Range("A2:X2000").Value = vals1
 

Fill specific text in blank cells of a particular column in a sheet

Dim LastRow As Long

 LastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
 'In the above code, I've selected column A. In general, select the column that is Primary key.
'Rows.Count returns the total # of rows in the Sheet which varies from version of excel to another version
'xlUp is directtion in which you want to go. Imitates Ctrl + ArrowKey action.
'Thus the last row# is found.
For i = 2 To lr1
  If Sheets("Sheet1").Range("G" & i).Value = "" Then
   Sheets("Sheet1").Range("G" & i).Value = "TextToBeReplaced"
  End If
 Next i

To do the reverse, i.e make a cell blank if it contains specific value then use the below logic and code.

Here the code will check if a Cell in a particular column has character String (string of aplphabets). If yes, it will make it blank.

Note that I’m not using double quotes (“”) to make a cell blank. Reason: I found that when i do that, For some strange reason,the formula that refer to this cell will get messed up!  i was not able to figure out why. Even clearContents didnt work.

Solution: I copied a blank cell. That did the trick. Formula didnt get impacted.

Dim LastRow As Long

 LastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
 'In the above code, I've selected column A. In general, select the column that is Primary key.
'Rows.Count returns the total # of rows in the Sheet which varies from version of excel to another version
'xlUp is directtion in which you want to go. Imitates Ctrl + ArrowKey action.
'Thus the last row# is found.
For i = 2 To lr1
  If Sheets("Sheet1").Range("G" & i).Value Like "*[a-zA-Z]*" Then
   Sheets("Sheet1").Range("G" & i).Value =Sheets("Sheet1").Range("G2500").Value
'i used G2500 above as i know that it will be always blank.
End If
Next i

 

To check if a cell is blank.

Here the below code will check following conditions

1. if the cell is totally blank (not even a single space character)
2. if the cell is having one or more space character

Sub TestCellA1()

'Test if the value is cell A1 is blank/empty
If (IsEmpty(Range("A1").Value) = True) Or (Range("A1").Value Like " *") Then
MsgBox "Cell A1 is empty"
End If

End Sub

 

Steps to copy past Graph from one workbook to another workbook as picture:

1. Delete the current picture in destination workbook
2. Activate the sheet in destination where you want to paste. Otherwise the picture gets pasted in currently active sheet
3. Use “CopyPicture” function to copy the graph as picture. Otherwise, the graph gets copied as-is and gets pasted to destination

Note: In your VBA code, if you are performing a series of tasks, keep the task of pasting the picture as last one. Because you use the Activate function, the code below this picture-copy-paste-codeBlock will try to act on that activated sheet.

'Remove Existing picture
 d.Sheets("Sheet1").Pictures.Delete

 'copy the graph from wb1 to wb2
 Z.Sheets("Sheet1").Shapes("Picture 1").CopyPicture  'Name of picture as displayed in excel
d.Sheets("Sheet1").Activate
d.Sheets("Sheet1").Paste Range("L2")

 

Lets say you used some formula for computation in your excel. You want to copy the range of cells to another workbook. After you do so, you see that some of the cells may contain errors like “#NA!” or “Div!0” etc. You dont want that to  show in the destination workbook? Use this VBA code

Dim row1, row2, row3, column As Integer
 row1 = 4
 column = 3 ' column from which i want to run thru

 For column = 3 To 21
 If IsError(d.Sheets("Sheet1").Cells(row1, column).Value) Then
 d.Sheets("Sheet1").Cells(row1, column).Value = ""
 End If
 Next column

 

OUTLOOK:

If you want to send same email very frequently with some attachment you can use the below macro code in your outlook session

 

Sub DSR()

Dim oMsg As Outlook.MailItem
Set oMsg = Application.CreateItemFromTemplate("C:\StatusReport.oft")
'above code will open the outlook message that you have saved as template
reportDate = Date
emailSubject = "Daily Status Report  " & reportDate

oMsg.Attachments.Add "C:\attachment.xlsx"
oMsg.Recipients.Add "somebody@gmail.com "
oMsg.Subject = emailSubject
oMsg.Display

End Sub

 

If you want to merge Sheet 2 of all the workbooks in a particular folder, into one destination workbook

</pre>
Sub GetSheets()
Dim Path As String, fileName As String
Dim Sht As Worksheet

'~~~Set the path variable
Set a = ThisWorkbook

'path variable
oPath = path of the folder along with trailing "\" but without excel file name

fileName = Dir(oPath & "*.xlsx")

newSheetNo = 2
Do While fileName <> ""
Workbooks.Open fileName:=oPath & fileName, ReadOnly:=True
With ActiveWorkbook
.Sheets(2).Copy After:=ThisWorkbook.Sheets(newSheetNo - 1)
ThisWorkbook.Sheets(newSheetNo).Name = .Name
End With
Workbooks(fileName).Close
fileName = Dir()
newSheetNo = newSheetNo + 1
Loop
End Sub
<pre>

 

 

 

To update a Text file.

Assumption: File path is given in excel and text to find and replace is also given in excel cells

Set FSO = CreateObject("Scripting.FileSystemObject")
Set TextFile = FSO.OpenTextFile(Filename)
Text = TextFile.ReadAll
TextFile.Close
Text = Replace(Text, FindValue, ReplaceValue)

'Below code will insert 4 line after 58th line in text file
myarr = Split(Text, vbNewLine)
myarr(57) = myarr(57) & vbNewLine & Sheets("Operation").Cells(9, 4) & vbNewLine & Sheets("Operation").Cells(10, 4) & vbNewLine & Sheets("Operation").Cells(11, 4) & vbNewLine & Sheets("Operation").Cells(12, 4)
Text = Join(myarr, vbNewLine)

'Below code will delete the extra blank line inserted at the end of text file
intLength = Len(Text)
strEnd = Right(Text, 2)

If strEnd = vbCrLf Then
Text = Left(Text, intLength - 1)
End If

Open Filename For Output As #1
Print #1, Text
Close #1

 

 

Selenium CrossBrowser Testing:One Test Method, DataProvider – Run the test of multiple browsers

This post sequel to my earlier Post.

What different i did this time is – Parameterization of DataProvider.

When i write this post, i didnt come acrosss any option provided by TestNG to parameterize the DataProvider method.

Now you may ask me the question – why parameterize Dataprovider?

When you run the same test case on multiple browsers, you would like to use the different input excels for each different browsers. In my last post, in order to pick and read the relevant file, i was first finding out the calling Test name. Based on the test name, i was passing the corresponding file to the excel reading function.

But i wanted to harness the power of XML and make my dataprovider more flexible. While the XML allows the user to configure and run the same Test case on various browsers based on browser name that is given to it, i was trying to build the same flexibility to my dataProvider also. The dataprovider should not except the file name to be hardcoded in the class. It should pick the file that is fed to it from XML.

Though i couldnt find the direct way of doing it, I implemented in slightly round-about way.

Instead of passing the parameter to the dataprovider method, I passed it to my @BeforeTest Method itself.In that method i captured the value of file name parameter in one of the class variable.

String testName,fileName;

@BeforeTest
@Parameters({"Browser", "filePath"})
public void intiateWebDriver(String Browser,String filePath){
fileName = filePath;

This class variable was then used in dataprovider method.

@DataProvider(name = "dp")
public String[][] readInputData() throws IOException{

String[][] TestData = null;
utilityClass u = new utilityClass();

TestData = u.excelDataReader(fileName);
return TestData;
}

XML file looks like this. Note that i’m passing multiple parameters for each Test.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE suite SYSTEM "http://testng.org/testng-1.0.dtd">
<suite name="Default suite" parallel = "tests" thread-count = "10">
	<listeners>
	<listener class-name="org.uncommons.reportng.HTMLReporter"/>
	<listener class-name="org.uncommons.reportng.JUnitXMLReporter"/>
</listeners>
<test name="TestChrome">
<parameter name="Browser" value="chrome" />
<parameter name="filePath" value="C://Selenium//TestData//AccountTransfer.xlsx" />
<classes>
<class name="sunView5xTest.AccountTransferMultiBrowserTest">
</class>
</classes>
</test>
<test name="TestIE">
<parameter name="Browser" value="IE" />
<parameter name ="filePath" value="C://Selenium//TestData//AccountTransferIE.xlsx" />
<classes>
<class name="sunView5xTest.AccountTransferMultiBrowserTest">
</class>
</classes>
</test>
</suite>