Getting Excel Column Names using ADODB - Codoid
Select Page
Codoid Blog

Getting Excel Column Names using ADODB

Irrespective of the business that your client is in, marketing is an integral part of their operations. In fact, in this day and age of social.

Getting Excel columns names using ADODB is simpler than Excel COM interface. The below code shows how to retrieve column names from XLS.

Code

strExcelPath="C:Book1.xls"
strTableName="Sheet1$"'Note: $ symbol should be included with Table Name.
Set oCon=CreateObject("ADODB.Connection")

With oCon
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ="&strExcelPath&"; ReadOnly=False;"
.Open
End With

Set oRs1=oCon.OpenSchema(4,Array(Empty, Empty,strTableName,Empty))

Do Until oRS1.EOF
Msgbox oRs1.Fields.Item("COLUMN_NAME").Value
oRS1.MoveNext
Loop

oRs1.Close
oCon.Close

Set oRs1=Nothing
Set oCon=Nothing
  

Written By

Submit a Comment

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


Getting Excel columns names using ADODB is simpler than Excel COM interface. The below code shows how to retrieve column names from XLS.

Code

strExcelPath="C:Book1.xls"
strTableName="Sheet1$"'Note: $ symbol should be included with Table Name.
Set oCon=CreateObject("ADODB.Connection")

With oCon
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" & _
"DBQ="&strExcelPath&"; ReadOnly=False;"
.Open
End With

Set oRs1=oCon.OpenSchema(4,Array(Empty, Empty,strTableName,Empty))

Do Until oRS1.EOF
Msgbox oRs1.Fields.Item("COLUMN_NAME").Value
oRS1.MoveNext
Loop

oRs1.Close
oCon.Close

Set oRs1=Nothing
Set oCon=Nothing