WORKING WITH QTP

TestEveryThinG

Get Column Address Excel VB / Find Column

Posted by rajivkumarnandvani on April 25, 2009

Some Time we have to Check that particular column Exist or Not abd required the column address
rem this function return the column address of your Excel sheet
rem input parameter xlFilePath := xls file path || FindColumn =column value which need to be check

dim sXLpath , FindColumn ,getColumnAddress
sXLpath =”C:\RajivKumarNandvani.xls” rem define xls file path
FindColumn =”Rajiv” rem check rajiv in column exist or not
getColumnAddress = FindColumnAddress(sXLpath ,FindColumn )
msgbox getColumnAddress
Public function FindColumnAddress(Byval xlFilePath ,byval FindColumn )

Set ObjAppExcel = CreateObject(“Excel.Application”)
rem Disable alerts
ObjAppExcel.DisplayAlerts = False
rem Add a workbook to the Excel App
ObjAppExcel.Workbooks.open(xlFilePath)
‘Get the object of the first sheet in the workbook
Set objectSheet = ObjAppExcel.Sheets(“Sheet1”)
rem count used Column in sheet
nColumnCount =objectSheet.UsedRange.Columns.Count
rem get the last column address
c =replace(objectSheet.Cells(1,nColumnCount).address,”$”,””)
rem define the range from A1 to last column address and filnd the value in range
set objValueFind = objectSheet.Range(“A1:”&c).Find(FindColumn)
If not objValueFind is nothing Then
FindColumnAddress =replace(objValueFind.address,”$”,””)
FindColumnAddress =replace(FindColumnAddress,”1″,””)
Exit function

End If
rem if not found then return the Empty
FindColumnAddress =”NOT FOUND”

Set objValueFind =nothing
Set objectSheet =nothing
Set ObjAppExcel =nothing
End Function

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: