Hi

I need help to create a macro in excel 2003. The macro should fetch data
from excel sheet using a query, as the data in sheet is more than 35000 lines
using for loop of while loop takes more time to search data.
I am using following code for fetching data but no success (Please help
me to run it)

Code:-
Sub Testquery2()
Dim strQuery As String
Dim cn As ADODB.Connection
Dim rsT As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0;Data"
.ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
"DBQ=" & Application.Path & "\Variance Calculation.xls;"
.Open
End With

strQuery = "SELECT * FROM [DB_OL & Act input $]"

Set rsT = New ADODB.Recordset
rsT.CursorLocation = adUseClient
rsT.Open strQuery, cn
adOpenStatic , adLockOptimistic, adCmdText

If (rsT.RecordCount <> 0) Then
MsgBox "Query Success"
End If

rsT.Close
cn.Close
End Sub

Please help me to get the way.

Thanks & Regards,
Atul D. Pohankar

Hi Atul, First things first have you created a reference to the Active x
Library 2.7 via Tools --> References--> Microsoft Active X Library 2.7?. If
Not do so. Second your connection string looks a little gnarly to me. Try
somthing like this to see if you get the results you need.

Public Sub WorksheetQuery()

Dim Recordset As ADODB.Recordset
Dim ConnectionString As String

ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\Variance Calculation.xls;" & _
"Extended Properties=Excel 8.0;"

Dim SQL As String

' Query based on the worksheet name.
SQL = "SELECT * FROM [Variance Calculation$]"

Set Recordset = New ADODB.Recordset

On Error GoTo Morgue

Call Recordset.Open(SQL, ConnectionString, _
CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
CommandTypeEnum.adCmdText)

If Not Recordset.EOF Then Call MsgBox("Success!", vbInformation,
ThisWorkbook.Name)

'your Code here

Morgue:
If (Err.Number <> 0) Then
Debug.Print Err.Description
End If

If (Recordset.State = ObjectStateEnum.adStateOpen) Then
Recordset.Close
End If

Set Recordset = Nothing

End Sub




"Atul D. Pohankar" wrote:

> Hi
>
> I need help to create a macro in excel 2003. The macro should fetch data
> from excel sheet using a query, as the data in sheet is more than 35000 lines
> using for loop of while loop takes more time to search data.
> I am using following code for fetching data but no success (Please help
> me to run it)
>
> Code:-
> Sub Testquery2()
> Dim strQuery As String
> Dim cn As ADODB.Connection
> Dim rsT As ADODB.Recordset
>
> Set cn = New ADODB.Connection
> With cn
> .Provider = "Microsoft.Jet.OLEDB.4.0;Data"
> .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
> "DBQ=" & Application.Path & "\Variance Calculation.xls;"
> .Open
> End With
>
> strQuery = "SELECT * FROM [DB_OL & Act input $]"
>
> Set rsT = New ADODB.Recordset
> rsT.CursorLocation = adUseClient
> rsT.Open strQuery, cn
> adOpenStatic , adLockOptimistic, adCmdText
>
> If (rsT.RecordCount <> 0) Then
> MsgBox "Query Success"
> End If
>
> rsT.Close
> cn.Close
> End Sub
>
> Please help me to get the way.
>
> Thanks & Regards,
> Atul D. Pohankar

Hi Jeff,

Tahankyou, for the help. i need information on how to use where clause
with variables in the query

SQL = "SELECT * FROM [Variance Calculation$]"

Please help me.

Thanks & Regards,
Atul D. Pohankar

"Jeff" wrote:

> Hi Atul, First things first have you created a reference to the Active x
> Library 2.7 via Tools --> References--> Microsoft Active X Library 2.7?. If
> Not do so. Second your connection string looks a little gnarly to me. Try
> somthing like this to see if you get the results you need.
>
> Public Sub WorksheetQuery()
>
> Dim Recordset As ADODB.Recordset
> Dim ConnectionString As String
>
> ConnectionString = _
> "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> "Data Source=" & ThisWorkbook.Path & "\Variance Calculation.xls;" & _
> "Extended Properties=Excel 8.0;"
>
> Dim SQL As String
>
> ' Query based on the worksheet name.
> SQL = "SELECT * FROM [Variance Calculation$]"
>
> Set Recordset = New ADODB.Recordset
>
> On Error GoTo Morgue
>
> Call Recordset.Open(SQL, ConnectionString, _
> CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
> CommandTypeEnum.adCmdText)
>
> If Not Recordset.EOF Then Call MsgBox("Success!", vbInformation,
> ThisWorkbook.Name)
>
> 'your Code here
>
> Morgue:
> If (Err.Number <> 0) Then
> Debug.Print Err.Description
> End If
>
> If (Recordset.State = ObjectStateEnum.adStateOpen) Then
> Recordset.Close
> End If
>
> Set Recordset = Nothing
>
> End Sub
>
>
>
>
> "Atul D. Pohankar" wrote:
>
> > Hi
> >
> > I need help to create a macro in excel 2003. The macro should fetch data
> > from excel sheet using a query, as the data in sheet is more than 35000 lines
> > using for loop of while loop takes more time to search data.
> > I am using following code for fetching data but no success (Please help
> > me to run it)
> >
> > Code:-
> > Sub Testquery2()
> > Dim strQuery As String
> > Dim cn As ADODB.Connection
> > Dim rsT As ADODB.Recordset
> >
> > Set cn = New ADODB.Connection
> > With cn
> > .Provider = "Microsoft.Jet.OLEDB.4.0;Data"
> > .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
> > "DBQ=" & Application.Path & "\Variance Calculation.xls;"
> > .Open
> > End With
> >
> > strQuery = "SELECT * FROM [DB_OL & Act input $]"
> >
> > Set rsT = New ADODB.Recordset
> > rsT.CursorLocation = adUseClient
> > rsT.Open strQuery, cn
> > adOpenStatic , adLockOptimistic, adCmdText
> >
> > If (rsT.RecordCount <> 0) Then
> > MsgBox "Query Success"
> > End If
> >
> > rsT.Close
> > cn.Close
> > End Sub
> >
> > Please help me to get the way.
> >
> > Thanks & Regards,
> > Atul D. Pohankar

Working With VBA And SQL can get a little tricky. Depending on which data
type your querying will determine the operator need i.e if you where looking
for a date your expression would look like SELECT * FROM table WHERE somedate
= #mm/dd/yy# if it is text your after SELECT * FROM table WHERE sometext
='text'

Things begin to get tricky the more advanced your SQL statement becomes.
Using our SQL Variable and a WHERE clause should be simple enough.

SQL = "SELECT * FROM [Variance Calculation$] WHERE columnName =#"
DateVariable &"#"

or

SQL = "SELECT * FROM [Variance Calculation$] WHERE coulmnName= ' " &
textvariable &" ' " 'extra spaces add to show single qoute clearly

Just remember that the entire SQL Statement is a string in VBA and be
mindful of SQLs syntax and you'll be fine. HTH.

"Atul D. Pohankar" wrote:

> Hi Jeff,
>
> Tahankyou, for the help. i need information on how to use where clause
> with variables in the query
>
> SQL = "SELECT * FROM [Variance Calculation$]"
>
> Please help me.
>
> Thanks & Regards,
> Atul D. Pohankar
>
> "Jeff" wrote:
>
> > Hi Atul, First things first have you created a reference to the Active x
> > Library 2.7 via Tools --> References--> Microsoft Active X Library 2.7?. If
> > Not do so. Second your connection string looks a little gnarly to me. Try
> > somthing like this to see if you get the results you need.
> >
> > Public Sub WorksheetQuery()
> >
> > Dim Recordset As ADODB.Recordset
> > Dim ConnectionString As String
> >
> > ConnectionString = _
> > "Provider=Microsoft.Jet.OLEDB.4.0;" & _
> > "Data Source=" & ThisWorkbook.Path & "\Variance Calculation.xls;" & _
> > "Extended Properties=Excel 8.0;"
> >
> > Dim SQL As String
> >
> > ' Query based on the worksheet name.
> > SQL = "SELECT * FROM [Variance Calculation$]"
> >
> > Set Recordset = New ADODB.Recordset
> >
> > On Error GoTo Morgue
> >
> > Call Recordset.Open(SQL, ConnectionString, _
> > CursorTypeEnum.adOpenForwardOnly, LockTypeEnum.adLockReadOnly, _
> > CommandTypeEnum.adCmdText)
> >
> > If Not Recordset.EOF Then Call MsgBox("Success!", vbInformation,
> > ThisWorkbook.Name)
> >
> > 'your Code here
> >
> > Morgue:
> > If (Err.Number <> 0) Then
> > Debug.Print Err.Description
> > End If
> >
> > If (Recordset.State = ObjectStateEnum.adStateOpen) Then
> > Recordset.Close
> > End If
> >
> > Set Recordset = Nothing
> >
> > End Sub
> >
> >
> >
> >
> > "Atul D. Pohankar" wrote:
> >
> > > Hi
> > >
> > > I need help to create a macro in excel 2003. The macro should fetch data
> > > from excel sheet using a query, as the data in sheet is more than 35000 lines
> > > using for loop of while loop takes more time to search data.
> > > I am using following code for fetching data but no success (Please help
> > > me to run it)
> > >
> > > Code:-
> > > Sub Testquery2()
> > > Dim strQuery As String
> > > Dim cn As ADODB.Connection
> > > Dim rsT As ADODB.Recordset
> > >
> > > Set cn = New ADODB.Connection
> > > With cn
> > > .Provider = "Microsoft.Jet.OLEDB.4.0;Data"
> > > .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};" &
> > > "DBQ=" & Application.Path & "\Variance Calculation.xls;"
> > > .Open
> > > End With
> > >
> > > strQuery = "SELECT * FROM [DB_OL & Act input $]"
> > >
> > > Set rsT = New ADODB.Recordset
> > > rsT.CursorLocation = adUseClient
> > > rsT.Open strQuery, cn
> > > adOpenStatic , adLockOptimistic, adCmdText
> > >
> > > If (rsT.RecordCount <> 0) Then
> > > MsgBox "Query Success"
> > > End If
> > >
> > > rsT.Close
> > > cn.Close
> > > End Sub
> > >
> > > Please help me to get the way.
> > >
> > > Thanks & Regards,
> > > Atul D. Pohankar