Bolte IT Consulting

Get header row in an Ecxel worksheet

Below method gets any row from a provided Microsoft Excel worksheet as a String array. This can be quite handy, if you for example want to get all field names from a worksheet.

Source Code

The code references a Microsoft Excel API. It automates Excel in a background process and therefore requires Excel to be running as well as the parent workbook of a handed worksheet to be opened before calling this function.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
' @Author - Alexander Bolte
' @ChangeDate - 2014-12-11
' @Description - Returns the header row of a provided Excel.Worksheet object
' as a zero based String() Array.
' @Param worksheet - an Excel.Worksheet object of an Excel.Workbook, 
' which has been opened before calling this function. 
' @Param row - a Long indicating the row, which field headers
' should be read from.
' @Returns a zero based String() Array containing the field header names.
' @Remarks - The method starts always in column one in a handed worksheet.
Function getExcelHeader(workSheet, row) ' As String()
  Dim c ' As Integer
  Dim header ' As String()
  Dim importRng ' As Range
 
  Set importRng = workSheet.UsedRange
  ReDim header(importRng.Columns.Count - 1)
  For c = 0 to UBound(header)
      header(c) = cStr(workSheet.Cells(row,c + 1).Value)
  Next ' column in worksheet.
  Set importRng = Nothing
 
  getExcelHeader = header
End Function
Hits: 2905