Excel ODBC Driver, Get Column Names?

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!
 
B

Bob Phillips

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)
 
R

Robert Paresi

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...
 
B

Bob Phillips

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...
 
R

Robert Paresi

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...
 
B

Bob Phillips

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...
 
Top