A
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
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