SQL with Excel

[Kenneth]

Is it possible to get a result set from data stored in an excel file with
SQL statement?
e.g.
Select Sheet1.Column_A, Sheet2.Column_B from Sheet1, Sheet2 where
Sheet1.ColumnC = Sheet2.Column_D
and Sheet1.Column_E > 10

Thanks for providing any hint or tools.

 

[Jack Zhong]

Kenneth has brought this to us :

Is it possible to get a result set from data stored in an excel file with SQL
statement?
e.g.
Select Sheet1.Column_A, Sheet2.Column_B from Sheet1, Sheet2 where
Sheet1.ColumnC = Sheet2.Column_D
and Sheet1.Column_E > 10

Thanks for providing any hint or tools.

You can regard Excel sheet as a database table, and then you could get
it with SQL.

 

[Bob Phillips]

Use ADO as one way. For example

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1

Public Sub GetData()
Dim oConn As ADODB.Connection 'Object
Dim oRS As ADODB.Recordset 'Object
Dim sFilename As String
Dim sConnect As String
Dim sSQL As String

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

Set oRS = New ADODB.Recordset 'CreateObject("ADODB.Recordset")

sSQL = "SELECT * FROM [Sales$A1:E89]"
Set oRS = New ADODB.Recordset
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
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

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)