Arrays elements is not returns the values to Calling function

Hi, Below function is for: Search for the First Column name in the excel sheet and select the entire row,read all the column names of a row and returns the list columns of array elements. But its retruning last column name as last elements and all others are blank. PLEASE ADVICE ME ON THIS.

Public Function Read_Column_Name(TarExcelPath As String, SheetName As String, ColumnName As String) As String()
Dim Add As String
Dim text As String
Dim lngIndex As Long
Dim CapturedText() As String
lngIndex = 1

Application.EnableEvents = False


'To open the Target excel report data file
Set TarobjExcel = CreateObject("Excel.Application")
TarobjExcel.DisplayAlerts = 0
TarobjExcel.Workbooks.Open TarExcelPath, False, True

Set TarcurrentWorkSheet = TarobjExcel.ActiveWorkbook.Worksheets(SheetName)


Set cell1 = TarobjExcel.ActiveWorkbook.Worksheets(SheetName).Cells.Find(What:=ColumnName, LookIn:=xlValues, LookAt:=xlPart, _
                    MatchCase:=False, SearchOrder:=xlByColumns)

Add = cell1.Address
Add = Replace(Add, "$", "")
Add = Extract_Number_from_Text(Add)
'MsgBox Add
'Number of Columns in Target Sheet
TusedColumnsCount = TarcurrentWorkSheet.UsedRange.Columns.Count
MsgBox TusedColumnsCount
For j = 1 To TusedColumnsCount
text = TarcurrentWorkSheet.Cells(Add, j).Value
If text <> "" Then
ReDim CapturedText(1 To TusedColumnsCount)
ReDim Preserve CapturedText(1 To TusedColumnsCount)
CapturedText(lngIndex) = text
'ReDim Preserve CapturedText(1 To TusedColumnsCount)
lngIndex = lngIndex + 1

Else
     Exit For
End If
    Next j

ReDim Preserve CapturedText(1 To TusedColumnsCount)
Read_Column_Name = CapturedText

Read_Column_Name_End:
Exit Function

End Function

Hi amarendra19

Maybe like

Option Explicit

Public Function Read_Column_Name(TarExcelPath As String, SheetName As String, ColumnName As String) As String
Dim XAdd As String
Dim cell1 As Object
Dim lngIndex As Long
Dim TarobjExcel As Object
Dim TarcurrentWorkSheet As Object
Dim TusedColumnsCount As Long
Dim j As Long
Dim Temp As String

Set TarobjExcel = CreateObject("Excel.Application")
TarobjExcel.DisplayAlerts = 0
TarobjExcel.Workbooks.Open TarExcelPath, False, True
Set TarcurrentWorkSheet = TarobjExcel.ActiveWorkbook.Worksheets(SheetName)
Set cell1 = TarobjExcel.ActiveWorkbook.Worksheets(SheetName).Cells.Find(What:=ColumnName, LookIn:=xlValues, LookAt:=xlPart, _
                    MatchCase:=False, SearchOrder:=xlByColumns)
XAdd = cell1.Column
TusedColumnsCount = TarcurrentWorkSheet.UsedRange.Rows.Count
For j = 1 To TusedColumnsCount
If Not IsEmpty(TarcurrentWorkSheet.Cells(XAdd, j)) Then
Temp = Temp & ", " & TarcurrentWorkSheet.Cells(XAdd, j).Value
End If
    Next j
Read_Column_Name = Temp
End Function