Requirement:-To find the Address of given Column in Excel. 

In the below image we need to find the Address of “Application” column in the excel.Which is “C”.

Excel View
Excel Data


Code:-

sSheetName = "uftHelp"
sColumnName = "Application"
sPath = "C:TempTest.xlsx"
fnExcelColAddress sSheetName,sColumnName,sPath
'*******************************************Function***********************************
'Function Name:- fnExcelColAddress
'Function Description:- Fetch the Excel Column Address based on User defined Column Name
'Input Parameters:- SheetName,ColumnName,Excel Path
'Output Parameters:- None
'**************************************************************************************
Public Function fnExcelColAddress(sSheetName,sColumnName,sPath)
Dim sAddress
'Creating the Excel Object
Set objExcel = CreateObject("Excel.Application")
'Creating the Workbooks object
Set objWB = objExcel.workbooks.open(sPath)
'Creating the sheet object
Set objsheet = objwb.worksheets(sSheetName)
'Iterating the sheet to find the match
For iColumn = 1 to objsheet.usedrange.columns.count
If objsheet.Cells(1, iColumn).Value = Cstr(sColumnName) Then
sAddress = objsheet.Range("A1").Columns(iColumn).Address
sAddress = Replace(sAddress, "$1", "")
sAddress = Replace(sAddress, "$", "")
fnExcelColAddress= True
Exit For
End If
Next
If Not fnExcelColAddress Then
Msgbox "OOPS!! No Match found for the column Name = '"&sColumnName&"'",,"Address"
else
Msgbox "Column Name = '"&sColumnName&"' has Address = '"&sAddress&"'",,"Address"
End If
'closing the workbook
objWB.Save
objWB.close
Set objWB = Nothing
'Quit the Excel and destroying the Excel object
objExcel.Quit
Set objExcel=nothing
End Function

Excel object Model
Adding Hyperlink in Excel
Randomize Excel Data

Leave a Reply

Your email address will not be published. Required fields are marked *