How to query Excel Data Using SQL?

How to query Excel Dats Using SQL query?

1. Set a reference to the appropriate library: Extra -> References -> MS ActiveX Data Objects 2.x library

2. Create a Connection string that contains a reference to the Workbook absolute path e.g.

Dim strPath as String
srPath = C:\Wkb.xls
Dim szConnect As String
szConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPath & ";" & _
"Extended Properties=""Excel 8.0;HDR=YES"";"

3. Build the SQL query e.g.
Dim szSQL As String
szSQL = "SELECT DISTINCT " & strName & " FROM [ORG$]"
where strName a variable that contains the name of the column to be extracted from the worksheet and ORG is the name of the worksheet in the workbook. For some funny reason you have to add a $ sign to that name in the SQL string

4. Extract the data into a recordset e.g.
Dim rsData As ADODB.Recordset
Set rsData = New ADODB.Recordset
rsData.Open szSQL, szConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

5. Transfer that data (if any) into a spreadsheet range e.g.
Dim strRange as String
srRange = "$A$1"
Select Case Not rsData.EOF
Case True
ActiveSheet.Range(strRngDest).CopyFromRecordset rsData
Case False
GoTo PROC_ERROR
End Select

More information: How to use ADO with Excel Data from Visual Basic or VBA

Take a look here:

http://www.xl-expert.com/html_pages/...ity_Excel.html

for a demo

"Niranjan" wrote:

> How to query Excel Dats Using SQL query?

Patrick,

is there a specific reason why you use the MSDASQL provider?

it seems a (little bit ) slower then going straight to Jet using a
connection string like:

m_con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel
8.0;Data Source=" & m_sDatabase


Also When using ADO on excel files you should always check that the
queried workbook is not open as (known bug, leaks memory).
See http://support.microsoft.com/kb/319998/EN-US


I've adapted your demo as follows and included some handy functions

Option Explicit

Dim m_sDatabase As String
Dim m_con As ADODB.Connection
Dim m_rst As ADODB.Recordset

Private Sub Class_Initialize()
Set m_con = New ADODB.Connection
Set m_rst = New ADODB.Recordset
End Sub
Private Sub Class_Terminate()
If m_con.State Then m_con.Close
If m_rst.State Then m_rst.Close
Set m_rst = Nothing
Set m_con = Nothing
End Sub

Public Sub Initialise(sDatabase As String)
Dim wkb As Workbook, sErr$

On Error Resume Next
Set wkb = Workbooks(Dir(sDatabase))
On Error GoTo oops
m_sDatabase = sDatabase

If Dir(sDatabase) = "" Then
Err.Raise 1
ElseIf Not wkb Is Nothing Then
Err.Raise 2
Else
m_con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Extended Properties=Excel 8.0;Data Source=" & m_sDatabase
End If
Exit Sub
oops:
Select Case Err.number
Case 1: sErr = "Unable to locate Database"
Case 2: sErr = "Querying an open workbook is not allowed"
Case Else: sErr = "Unable to connect to database"
End Select
Err.Raise 1, "Database Loader", sErr & vbCrLf & sDatabase
End Sub

Public Function GetData(sRange As String, Optional sWHERE As String)
With m_rst
If .State Then .Close
.Open "SELECT * from [" & sRange & "] " & _
IIf(sWHERE <> "", "WHERE " & sWHERE, ""), m_con, adOpenStatic
GetData = GetRowsTransposed
.Close
End With
End Function

Public Function GetHeaders(sRange As String)
Dim vGH, i&
If m_rst.State Then m_rst.Close
With m_rst
If .State Then .Close
.Open "SELECT TOP 1 * from [" & sRange & "] ", m_con, adOpenStatic
If Not .EOF Then
ReDim vGH(1 To 1, 1 To .Fields.Count)
For i = 1 To .Fields.Count
vGH(1, i) = .Fields(i - 1).Name
Next
End If
GetHeaders = vGH
.Close
End With
End Function

Private Function GetRowsTransposed()
'Transposes the 0based GetRows Array to a 1based Variant.
Dim vGR, vGA, r&, f&
If Not m_rst.EOF Then
vGR = m_rst.GetRows
ReDim vGA(1 To UBound(vGR, 2) + 1, 1 To UBound(vGR, 1) + 1)
For r = 0 To UBound(vGR, 2)
For f = 0 To UBound(vGR, 1)
If Not IsNull(vGR(f, r)) Then vGA(r + 1, f + 1) = vGR(f, r)
Next
Next
GetRowsTransposed = vGA
End If
End Function


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Patrick Molloy wrote :

> Take a look here:
>
> http://www.xl-expert.com/html_pages/...ity_Excel.html
>
> for a demo
>
> "Niranjan" wrote:
>
> > How to query Excel Dats Using SQL query?

keepITcool wrote:
> Patrick,
>
> is there a specific reason why you use the MSDASQL provider?
>
> it seems a (little bit ) slower then going straight to Jet using a
> connection string like:

Yes, MSDASQL is even on the list of depreciated components:

http://msdn.microsoft.com/library/de...components.asp

I've tried pointing this out to Patrick a few times myself but it just
falls on deaf ears

Jamie.

--

ok already

I updated my web page. I didn't before because it was (a) 4 years old and
(b) never viewed.

I appreciate KeepItCool's class , however, I have deliberately kept the
example simple so that less experienced VBA coders can get a handle on the
core stuff.


"Jamie Collins" wrote:

> keepITcool wrote:
> > Patrick,
> >
> > is there a specific reason why you use the MSDASQL provider?
> >
> > it seems a (little bit ) slower then going straight to Jet using a
> > connection string like:
>
> Yes, MSDASQL is even on the list of depreciated components:
>
> http://msdn.microsoft.com/library/de...components.asp
>
> I've tried pointing this out to Patrick a few times myself but it just
> falls on deaf ears
>
> Jamie.
>
> --
>
>