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/index.php?t=envbadac
but querying an open workbook can cause problems:
Previously posted by Jamie Collins:
http://groups.google.com/groups?hl=en&lr=&[email protected]
the URL should all be on one line.
--
From: (e-mail address removed) (Jamie Collins)
Newsgroups: microsoft.public.excel.programming
Subject: Re: memory issue using ADO to query Excel
Date: 16 Jun 2004 03:52:58 -0700
Organization:
http://groups.google.com
Lines: 93
Message-ID: <
[email protected]>
References: <
[email protected]>
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: (e-mail address removed)
NNTP-Posting-Date: Wed, 16 Jun 2004 10:53:00 +0000 (UTC)
Dennis said:
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.aspx?scid=kb;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 said:
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
