Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
To create a macro to fetch data from excel sheet (2003) using a qu
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
To create a macro to fetch data from excel sheet (2003) using a qu
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
To create a macro to fetch data from excel sheet (2003) using
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
To create a macro to fetch data from excel sheet (2003) using
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fetch a value from a w/sheet to userform. | Excel Programming | |||
fetch values from w/sheet | Excel Discussion (Misc queries) | |||
Fetch Access data from Excel | Excel Programming | |||
fetch data from excel page | Excel Programming | |||
Use of Indirect function to fetch data from another Excel file | Excel Worksheet Functions |