Excel ODBC Driver, Get Column Names?

  • Thread starter Thread starter Robert Paresi
  • Start date Start date
R

Robert Paresi

Hello,

Can someone please tell me how to retreive the column NAMES from an Excel
spreadsheet. I can get the data, but I need to know the column names?

Thank you!
 
Robert,

Here is an example that reads a sheet and displays the column names

Sub ExcelSheet()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, 0, 1, 1
' Check to make sure we received data.

'>>>>>>>>>>>> THIS BIT
For i = 0 To oRS.Fields.Count - 1
MsgBox oRS.Fields(i).Name
Next i

If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub







--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thank you ... but unfortunetly, I am not doing it in ASP, I am doing it
with the ODBC driver using Clarion code.

The record set that I am using is named as "COLUMN1, COLUMN2, etc." so I
can't use the internal functions to get the column name as it would just
return "COLUMN1".

Is there a Call Procedure I can do on the SQL Statement to get this
information - through an internal procedure or system table?

-Robert


Bob Phillips said:
Robert,

Here is an example that reads a sheet and displays the column names

Sub ExcelSheet()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, 0, 1, 1
' Check to make sure we received data.

'>>>>>>>>>>>> THIS BIT
For i = 0 To oRS.Fields.Count - 1
MsgBox oRS.Fields(i).Name
Next i

If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub







--

HTH

RP
(remove nothere from the email address if mailing direct)


Robert Paresi said:
Hello,

Can someone please tell me how to retreive the column NAMES from an Excel
spreadsheet. I can get the data, but I need to know the column names?

Thank you!
Click to expand...
Click to expand...
 
Sorry, I have no knowledge of Clarion, so I cannot help.

BTW, this is just VBA not ASP.

Bob

Robert Paresi said:
Thank you ... but unfortunetly, I am not doing it in ASP, I am doing it
with the ODBC driver using Clarion code.

The record set that I am using is named as "COLUMN1, COLUMN2, etc." so I
can't use the internal functions to get the column name as it would just
return "COLUMN1".

Is there a Call Procedure I can do on the SQL Statement to get this
information - through an internal procedure or system table?

-Robert


Bob Phillips said:
Robert,

Here is an example that reads a sheet and displays the column names

Sub ExcelSheet()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, 0, 1, 1
' Check to make sure we received data.

'>>>>>>>>>>>> THIS BIT
For i = 0 To oRS.Fields.Count - 1
MsgBox oRS.Fields(i).Name
Next i

If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub







--

HTH

RP
(remove nothere from the email address if mailing direct)


Robert Paresi said:
Hello,

Can someone please tell me how to retreive the column NAMES from an Excel
spreadsheet. I can get the data, but I need to know the column names?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
Hi!

Clarion really isn't too much of the issue, as anything that is an SQL
statement (ODBC compatible), is good enough for me to do in Clarion.

Simply:

ODBCDRIVER{PROP:SQL} = 'SELECT COLUMNNAMES FROM [SHEET1$]'

or any valid SQL statement I can excecute in Clarion that easily. I just
don't know the proper statement that use. There must be a way, as I can do
it with MSSQL and Sybase. :-)

Anyone?

-Robert


Bob Phillips said:
Sorry, I have no knowledge of Clarion, so I cannot help.

BTW, this is just VBA not ASP.

Bob

Robert Paresi said:
Thank you ... but unfortunetly, I am not doing it in ASP, I am doing it
with the ODBC driver using Clarion code.

The record set that I am using is named as "COLUMN1, COLUMN2, etc." so I
can't use the internal functions to get the column name as it would just
return "COLUMN1".

Is there a Call Procedure I can do on the SQL Statement to get this
information - through an internal procedure or system table?

-Robert


Bob Phillips said:
Robert,

Here is an example that reads a sheet and displays the column names

Sub ExcelSheet()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, 0, 1, 1
' Check to make sure we received data.

'>>>>>>>>>>>> THIS BIT
For i = 0 To oRS.Fields.Count - 1
MsgBox oRS.Fields(i).Name
Next i

If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub







--

HTH

RP
(remove nothere from the email address if mailing direct)


Hello,

Can someone please tell me how to retreive the column NAMES from an Excel
spreadsheet. I can get the data, but I need to know the column names?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
I don't think you will be able to use SQL against a spreadsheet to get that
info, the data provider just doesn't support it as far as I can see.

--

HTH

RP
(remove nothere from the email address if mailing direct)


Robert Paresi said:
Hi!

Clarion really isn't too much of the issue, as anything that is an SQL
statement (ODBC compatible), is good enough for me to do in Clarion.

Simply:

ODBCDRIVER{PROP:SQL} = 'SELECT COLUMNNAMES FROM [SHEET1$]'

or any valid SQL statement I can excecute in Clarion that easily. I just
don't know the proper statement that use. There must be a way, as I can do
it with MSSQL and Sybase. :-)

Anyone?

-Robert


Bob Phillips said:
Sorry, I have no knowledge of Clarion, so I cannot help.

BTW, this is just VBA not ASP.

Bob

Robert Paresi said:
Thank you ... but unfortunetly, I am not doing it in ASP, I am doing it
with the ODBC driver using Clarion code.

The record set that I am using is named as "COLUMN1, COLUMN2, etc." so I
can't use the internal functions to get the column name as it would just
return "COLUMN1".

Is there a Call Procedure I can do on the SQL Statement to get this
information - through an internal procedure or system table?

-Robert


Robert,

Here is an example that reads a sheet and displays the column names

Sub ExcelSheet()
Dim oConn As Object
Dim oRS As Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String
Dim i As Long

sFilename = "c:\Mytest\Volker1.xls"
sConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFilename & ";" & _
"Extended Properties=Excel 8.0;"

sSQL = "SELECT * FROM [Sheet1$]"

Set oRS = CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, 0, 1, 1
' Check to make sure we received data.

'>>>>>>>>>>>> THIS BIT
For i = 0 To oRS.Fields.Count - 1
MsgBox oRS.Fields(i).Name
Next i

If Not oRS.EOF Then
Sheet1.Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records returned.", vbCritical
End If

' Clean up our Recordset object.
oRS.Close
Set oRS = Nothing

End Sub







--

HTH

RP
(remove nothere from the email address if mailing direct)


Hello,

Can someone please tell me how to retreive the column NAMES from an Excel
spreadsheet. I can get the data, but I need to know the column names?

Thank you!
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Column widths in Excel 2013 16
The data in a linked Excel spreadsheet column is truncated to 255 characters in an Access database 0
VBA - retrieve data on last create named range from previous inserted named range 0
why won't text-to-columns work here? 6
Range names shows up in cells 1
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Inserting cells (shift cells right) using macro 0
Get Columns 3

Back
Top