How to get Column Address from column Name in Excel ? – User friendly Tech help
Requirement:-To find the Address of given Column in Excel.
n
nIn the below image we need to find the Address of “Application” column in the excel.Which is “C”.
n
n
 |
| Excel Data |
n
nCode:-
n
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
n
Excel object Model
nAdding Hyperlink in Excel
nRandomize Excel Data
Was this article helpful?
YesNo