Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use a query rathen than advanced filter
Hello Excel Guru,
I am not very skilled with VBA in Excel. I have a hige sheet and I would like to filter it. At moment I am using the Advanced filter but it is not very "confortable": one of filtering criteria is matching some strings in column "description". So, if I have to find only one string (e.g I am looking for "connection" string )I set the "criteria range" as Description *connection* If I want to filter cells that have at some time the strings "connection" and "battery" I have to add a column in the criteria range: Description Description *connection* *battery* so I have to change the criteria range all the time. Some matter with other columns where I should find if the operator is "kriss" or "andy". I was wondering if there is a chance to use a query which I can build programmatically. I read something regarding the chance to load the "ODBC" plug-in which should allow me to connect to a database. Does it work for filtering excel sheet itself ? Thanks, Massimo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use a query rathen than advanced filter
You can query an excel sheet as a database. You would need an ODBC driver
that supports this. You can also do it using ADO: http://www.erlandsendata.no/english/...php?t=envbadac but querying an open workbook can cause problems: Previously posted by Jamie Collins: http://groups.google.com/groups?hl=e...ing.google.com the URL should all be on one line. -- From: (Jamie Collins) Newsgroups: microsoft.public.excel.programming Subject: memory issue using ADO to query Excel Date: 16 Jun 2004 03:52:58 -0700 Organization: http://groups.google.com Lines: 93 Message-ID: References: NNTP-Posting-Host: 81.171.142.210 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1087383180 1480 127.0.0.1 (16 Jun 2004 10:53:00 GMT) X-Complaints-To: NNTP-Posting-Date: Wed, 16 Jun 2004 10:53:00 +0000 (UTC) "Dennis" wrote ... I set up a SQL text box to run queries on a 38k rows by 100 columns using ADO. I've got to working fine. However, after about 10 query requests, I receive insufficient memory errors and I'm forced to shut down Excel. I clear my recordset variable after each query. I was wondering if there is some cache that should be cleared. All of my queries are SELECT queries. Are you querying an open workbook? Microsoft Knowledge Base Article - 319998 http://support.microsoft.com/default...;en-us;Q319998 BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO If this applies, save the worksheet to a temporary workbook, close it and query the closed workbook. Here's some example code: Option Explicit Sub Test() Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim Con As Object Dim rs As Object Dim strCon As String Dim strPath As String Dim strSql1 As String ' Amend the following constants to suit Const FILENAME_XL_TEMP As String = "" & _ "delete_me.xls" Const TABLE_NAME_CURRENT As String = "" & _ "MySheet" ' Do NOT amend the following constants Const CONN_STRING_1 As String = "" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=<PATH<FILENAME;" & _ "Extended Properties='Excel 8.0;HDR=YES'" ' Build connection strings strPath = ThisWorkbook.Path & _ Application.PathSeparator strCon = CONN_STRING_1 strCon = Replace(strCon, _ "<PATH", strPath) strCon = Replace(strCon, _ "<FILENAME", FILENAME_XL_TEMP) ' Build sql statement strSql1 = "" strSql1 = strSql1 & "SELECT Col1 FROM " strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]" ' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3" ' Delete old instance of temp workbook On Error Resume Next Kill strPath & FILENAME_XL_TEMP On Error GoTo 0 ' Save copy of worksheet to temp workbook Set wb = Excel.Application.Workbooks.Add() With wb ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _ Copy .Worksheets(1) .SaveAs strPath & FILENAME_XL_TEMP .Close End With ' Open connection to temp workbook Set Con = CreateObject("ADODB.Connection") With Con .ConnectionString = strCon .CursorLocation = 3 .Open Set rs = .Execute(strSql1) End With ' <<do something with recordset rs.Close Con.Close End Sub Jamie. -- Regards, Tom Ogilvy "MassimoM" wrote in message om... Hello Excel Guru, I am not very skilled with VBA in Excel. I have a hige sheet and I would like to filter it. At moment I am using the Advanced filter but it is not very "confortable": one of filtering criteria is matching some strings in column "description". So, if I have to find only one string (e.g I am looking for "connection" string )I set the "criteria range" as Description *connection* If I want to filter cells that have at some time the strings "connection" and "battery" I have to add a column in the criteria range: Description Description *connection* *battery* so I have to change the criteria range all the time. Some matter with other columns where I should find if the operator is "kriss" or "andy". I was wondering if there is a chance to use a query which I can build programmatically. I read something regarding the chance to load the "ODBC" plug-in which should allow me to connect to a database. Does it work for filtering excel sheet itself ? Thanks, Massimo |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to use a query rathen than advanced filter
Hello Tom,
sorry if I haven't answered soon, It was national holiday here in Italy. Thank a lot for your useful suggestion, I missed it in my search. While searching I found this article which might be useful to somebody else: http://groups.google.com/groups?hl=e...p05%26rnum%3D1 MassimoM "Tom Ogilvy" wrote in message ... You can query an excel sheet as a database. You would need an ODBC driver that supports this. You can also do it using ADO: http://www.erlandsendata.no/english/...php?t=envbadac but querying an open workbook can cause problems: Previously posted by Jamie Collins: http://groups.google.com/groups?hl=e...ing.google.com the URL should all be on one line. -- From: (Jamie Collins) Newsgroups: microsoft.public.excel.programming Subject: memory issue using ADO to query Excel Date: 16 Jun 2004 03:52:58 -0700 Organization: http://groups.google.com Lines: 93 Message-ID: References: NNTP-Posting-Host: 81.171.142.210 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1087383180 1480 127.0.0.1 (16 Jun 2004 10:53:00 GMT) X-Complaints-To: NNTP-Posting-Date: Wed, 16 Jun 2004 10:53:00 +0000 (UTC) "Dennis" wrote ... I set up a SQL text box to run queries on a 38k rows by 100 columns using ADO. I've got to working fine. However, after about 10 query requests, I receive insufficient memory errors and I'm forced to shut down Excel. I clear my recordset variable after each query. I was wondering if there is some cache that should be cleared. All of my queries are SELECT queries. Are you querying an open workbook? Microsoft Knowledge Base Article - 319998 http://support.microsoft.com/default...;en-us;Q319998 BUG: Memory Leak Occurs When You Query an Open Excel Worksheet Using ADO If this applies, save the worksheet to a temporary workbook, close it and query the closed workbook. Here's some example code: Option Explicit Sub Test() Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim Con As Object Dim rs As Object Dim strCon As String Dim strPath As String Dim strSql1 As String ' Amend the following constants to suit Const FILENAME_XL_TEMP As String = "" & _ "delete_me.xls" Const TABLE_NAME_CURRENT As String = "" & _ "MySheet" ' Do NOT amend the following constants Const CONN_STRING_1 As String = "" & _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=<PATH<FILENAME;" & _ "Extended Properties='Excel 8.0;HDR=YES'" ' Build connection strings strPath = ThisWorkbook.Path & _ Application.PathSeparator strCon = CONN_STRING_1 strCon = Replace(strCon, _ "<PATH", strPath) strCon = Replace(strCon, _ "<FILENAME", FILENAME_XL_TEMP) ' Build sql statement strSql1 = "" strSql1 = strSql1 & "SELECT Col1 FROM " strSql1 = strSql1 & " [" & TABLE_NAME_CURRENT & "$]" ' strSql1 = strSql1 & " WHERE Co2=1 OR Col2=3" ' Delete old instance of temp workbook On Error Resume Next Kill strPath & FILENAME_XL_TEMP On Error GoTo 0 ' Save copy of worksheet to temp workbook Set wb = Excel.Application.Workbooks.Add() With wb ThisWorkbook.Worksheets(TABLE_NAME_CURRENT). _ Copy .Worksheets(1) .SaveAs strPath & FILENAME_XL_TEMP .Close End With ' Open connection to temp workbook Set Con = CreateObject("ADODB.Connection") With Con .ConnectionString = strCon .CursorLocation = 3 .Open Set rs = .Execute(strSql1) End With ' <<do something with recordset rs.Close Con.Close End Sub Jamie. -- Regards, Tom Ogilvy "MassimoM" wrote in message om... Hello Excel Guru, I am not very skilled with VBA in Excel. I have a hige sheet and I would like to filter it. At moment I am using the Advanced filter but it is not very "confortable": one of filtering criteria is matching some strings in column "description". So, if I have to find only one string (e.g I am looking for "connection" string )I set the "criteria range" as Description *connection* If I want to filter cells that have at some time the strings "connection" and "battery" I have to add a column in the criteria range: Description Description *connection* *battery* so I have to change the criteria range all the time. Some matter with other columns where I should find if the operator is "kriss" or "andy". I was wondering if there is a chance to use a query which I can build programmatically. I read something regarding the chance to load the "ODBC" plug-in which should allow me to connect to a database. Does it work for filtering excel sheet itself ? Thanks, Massimo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advanced Filter Query--Not | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I use advanced filter to filter for blank cells? | Excel Discussion (Misc queries) | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |