To create a macro to fetch data from excel sheet (2003) using a qu

[Atul D. Pohankar]

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

 

[Jeff]

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]

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

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

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

 

[Jeff]

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.

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

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

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