Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Please forgive my lack of brain functions today.
This may sound silly, but Ive been working with access for weeks and cant wrap my brain around this problem in excel. Its rather straight forward. I have a large table some 19000 records. Each record has 18 columns. I want to select all records with a value from column # 12 that is equal to variable €œA1€. Then take all matching records and copy to a new workbook and save. Then do again with different value. Dan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Hello Dan,
Speaking of Access, using ADO might make some sense with such a quandary. Here's an example that creates a new workbook in c:\temp\ named Book1.xls: Sub ExToEx() Dim cn As Object Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" cn.Execute "SELECT Col1, Col2 INTO [Excel 8.0;Database=c:\temp\book1.xls].[Sheet1]" _ & " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value cn.Close: Set cn = Nothing End Sub Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the variable is located in the 2nd Sheet, A1. The workbook that your running this from must be saved so that a proper connection can be established. More info can be found on such an approach he http://support.microsoft.com/kb/295646/EN-US/ Regards, Nate Oliver |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
I need to keep this in excel if I can.
Any ideas? Let me know. Dan "Nate Oliver" wrote: Hello Dan, Speaking of Access, using ADO might make some sense with such a quandary. Here's an example that creates a new workbook in c:\temp\ named Book1.xls: Sub ExToEx() Dim cn As Object Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" cn.Execute "SELECT Col1, Col2 INTO [Excel 8.0;Database=c:\temp\book1.xls].[Sheet1]" _ & " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value cn.Close: Set cn = Nothing End Sub Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the variable is located in the 2nd Sheet, A1. The workbook that your running this from must be saved so that a proper connection can be established. More info can be found on such an approach he http://support.microsoft.com/kb/295646/EN-US/ Regards, Nate Oliver |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
You can filter data to a new location with Data=Filter=Advanced filter.
I assume A1 was representative. If you need code, turn on the macro recorder while you do it manually - then turn off the recorder. This records a single command which should be easy to modify to take variable arguments and place into a loop. -- Regards, Tom Ogilvy "wtpcomplab" wrote in message ... I need to keep this in excel if I can. Any ideas? Let me know. Dan "Nate Oliver" wrote: Hello Dan, Speaking of Access, using ADO might make some sense with such a quandary. Here's an example that creates a new workbook in c:\temp\ named Book1.xls: Sub ExToEx() Dim cn As Object Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" cn.Execute "SELECT Col1, Col2 INTO [Excel 8.0;Database=c:\temp\book1.xls].[Sheet1]" _ & " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value cn.Close: Set cn = Nothing End Sub Where Col1 and Col2 are column headers located in row 1 of Sheet1, and the variable is located in the 2nd Sheet, A1. The workbook that your running this from must be saved so that a proper connection can be established. More info can be found on such an approach he http://support.microsoft.com/kb/295646/EN-US/ Regards, Nate Oliver |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Nate Oliver wrote: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" More info on why this is a bad idea can be found he BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) http://support.microsoft.com/default...;en-us;Q319998 Jamie. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
"wtpcomplab" wrote:
I need to keep this in excel if I can. Right, this is all in Excel. Excel can act as a Jet DB as well. I wrote the code in Excel for Excel and even tested it. One potential issue is that your data table would really need to look like a DB. Tom's suggestion is more straightforward although I'm not sure about looping. You could pass the copied, filtered data to a new workbook, save and close. Bon chance, Nate Oliver |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Interesting,
Note they're speaking to querying an open Excel file. One of the benefits of using ADO to query a spreadsheet is that the file can (and apparently should) be closed. Not to mention that ADO is extremely fast. Cheers, Nate "Jamie Collins" wrote: Nate Oliver wrote: cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" More info on why this is a bad idea can be found he BUG: Memory leak occurs when you query an open Excel worksheet by using ActiveX Data Objects (ADO) http://support.microsoft.com/default...;en-us;Q319998 Jamie. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Looping would loop through a list of unique items (also can be built with
advanced filter) and would put each of the items in this list as criteria in the criteria cell/range, adjust the destination (which would be a variable in the advanced filter command), then execute the filter. Perform any remaining administration. I have successfully used this many times as I am sure you have yourself. So apparently my suggestion was not clear or was vague as to what I meant. Thanks for pointing that out. -- Regards, Tom Ogilvy "Nate Oliver" wrote in message ... "wtpcomplab" wrote: I need to keep this in excel if I can. Right, this is all in Excel. Excel can act as a Jet DB as well. I wrote the code in Excel for Excel and even tested it. One potential issue is that your data table would really need to look like a DB. Tom's suggestion is more straightforward although I'm not sure about looping. You could pass the copied, filtered data to a new workbook, save and close. Bon chance, Nate Oliver |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Tom, I apologize, I missed the following in the original post:
Then do again with different value. Which is exactly where looping comes into play as you have mentioned. Thank you for following up. Regards, Nate Oliver |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Nate Oliver wrote:
Note they're speaking to querying an open Excel file. *You* were suggesting to the OP to query an open Excel file by using ThisWorkbook.FullName in the connection string. Jamie. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Hello Jamie,
Where's the ADO Recordset in my post? Quote by MS: "When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet that is open in Excel, a memory leak occurs in the Excel process. Repeated queries may eventually cause Excel to run out of memory and raise an error, or cause Excel to stop responding." Workaround by MS: "Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel data to a new worksheet. For additional information about using the SELECT INTO syntax to export data, click the following article number to view the article in the Microsoft Knowledge Base: 295646 How To Transfer Data from ADO Data Source to Excel with ADO" My Original Post: Sub ExToEx() Dim cn As Object Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" cn.Execute "SELECT Col1, Col2 INTO [Excel 8.0;Database=c:\temp\book1.xls].[Sheet1]" _ & " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value cn.Close: Set cn = Nothing End Sub Look familiar? Regards, Nate Oliver |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Nate Oliver wrote: Where's the ADO Recordset in my post? Quote by MS: "When you retrieve a Microsoft ActiveX Data Objects (ADO) Recordset from an Excel worksheet that is open in Excel, a memory leak occurs in the Excel process. Workaround by MS: "Use the SELECT INTO syntax of the Jet OLE DB Provider to export the Excel data to a new worksheet. Nate, It's not your fault but what can I do? I've tried giving Microsoft 'feedback' about this article but they have so far taken no notice; I guess ADO classic doesn't interest them. So my only recourse is to spread the word myself and jump on anyone in the ngs who repeats MS's bad advice. There may be no recordset in your post be there certainly is a memory leak. I've done extensive testing using the very same performance monitoring code found in the article in question and I think I know the truth. The key to the issue is not the recordset, not the connection, not the provider or driver. It's as simple as this: using ADO to execute Jet SQL involving an open workbook causes a memory leak. Let's be fair: did you test your assertion or did you just put your faith in Microsoft? I did a test just now. My connection was to a .mdb MS Access/Jet database i.e. not even an Excel connection. I executed the following SELECT..INTO..FROM which does not return a row set and I certainly did not use a recordset: SELECT * INTO [Excel 8.0;Database=C:\db1.xls;].Sheet1 FROM [Excel 8.0;Database=C:\db.xls;].[Sheet1$]; With the source workbook open I got a memory leak. When it was closed, I experienced no leak. I'm not sure how I can prove to you that the leak occurs other than to say, try it for yourself. If you don't have VB6 or other requirements of the code, just open Windows Task Manager, switch to the Performance tab and keep you eye on the Mem usage: if the value increases but does not return to something very close its original value then you're leaking. If any of the regulars who use ADO (you know who you are <g) and have VB6.0 are willing to test and post their experience/results here, I would be grateful. Here is the link to the article and code: http://support.microsoft.com/default...;en-us;Q319998 I really wish I was wrong on this one: it would be nice to be able to safely operate on an open workbook. There must be someone out there itching to publicly show me up ...? Jamie. -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Nate, It's not your fault but what can I do? I've tried giving
Microsoft 'feedback' about this article but they have so far taken no notice; I guess ADO classic doesn't interest them. So my only recourse is to spread the word myself and jump on anyone in the ngs who repeats MS's bad advice. How noble. Don't hold your breathe while you wait for them to fix that, I'm under the impression that they've moved on. I've spoken directly with Microsoft personnel regarding incorrect KBs, e.g., http://www.utteraccess.com/forums/sh...0493&fpart=2.2 And, nothing. There may be no recordset in your post be there certainly is a memory leak. Pity, I'm going to have to agree. It ruins something so simple and fast like the following: http://www.xtremevbtalk.com/showpost.php?p=864947 Let's be fair: did you test your assertion or did you just put your faith in Microsoft? Have to admit, I did take Microsoft's word for it on this one, it is their product. I'm not sure how I can prove to you that the leak occurs other than to say, try it for yourself. If you don't have VB6 or other requirements of the code, just open Windows Task Manager, switch to the Performance tab and keep you eye on the Mem usage: if the value increases but does not return to something very close its original value then you're leaking. Don't worry about it, you don't need Visual Studio to see it. What I did notice is that the leak doesn't compound as you recall the procedure, it appears to stabilize after the first leak. I really wish I was wrong on this one: it would be nice to be able to safely operate on an open workbook. There must be someone out there itching to publicly show me up ...? I wish you were wrong too. I guess if you're going to use ADO with Excel, you need to to either kill the App when you're done, if you can live with a temporary leak or separate the code and data, which sounds familiar. See you around. Regards, Nate Oliver |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
I'm not sure how I can prove to you that the leak occurs other than to
say, try it for yourself. If you don't have VB6 or other requirements of the code, just open Windows Task Manager, switch to the Performance tab and keep you eye on the Mem usage: if the value increases but does not return to something very close its original value then you're leaking. How unfortunate, perhaps I was too quick to agree with you, Jamie. Memory consumption doesn't have to be a leak. The leveling off of the memory consumption versus continual degredation leads me to believe that Microsoft hasn't changed their kb to your point of view because it would be incorrect. What we're probably looking at is a Jet cache in memory, which is refreshed on subsequent calls (hence the stability following call #1). See [Microsoft's] Russ Gray's post he http://groups-beta.google.com/group/...0021430755ed35 And the following: http://support.microsoft.com/default...;en-us;Q247140 Nate, It's not your fault but what can I do? I've tried giving Microsoft 'feedback' about this article but they have so far taken no notice; I guess ADO classic doesn't interest them. So my only recourse is to spread the word myself and jump on anyone in the ngs who repeats MS's bad advice. Well, someone recently told me that the proliferation of myths flies in the face of 'the Usenet way', so I might advise against posting as such. ;) Cheers, Nate Oliver |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Nate Oliver wrote:
The leveling off of the memory consumption versus continual degredation leads me to believe that Microsoft hasn't changed their kb to your point of view because it would be incorrect. What we're probably looking at is a Jet cache in memory, which is refreshed on subsequent calls (hence the stability following call #1). See [Microsoft's] Russ Gray's post he http://groups-beta.google.com/group/...0021430755ed35 And the following: http://support.microsoft.com/default...;en-us;Q247140 Nate, Where's that healthy (or otherwise) cynicism of yours from the other thread? You distrust their VB garbage collector based on no evidence (design faults in DAO, Excel and other components cannot be attributed to the garbage collector), and no testing or observations of your own. Yet in this thread you observe the memory leak for yourself but then you go running back to Microsoft to try to explain it away. Think about it, I mean, think about it *yourself*: if the Jet cache is the explanation, why does usage climb when the workbook is open but remain stable when the workbook is closed? You may be seeing some stabilization/recovery but do you get (close to) everything back when done? I changed my test (I'll post the exact code and output at the end) to do 50 iterations, the first 10 with the workbook open, the next 10 with it closed, the next 10 with it open etc. As before I was using a connection to an mdb jet database. I closed and released the ADO Connection on each iteration. As expected, with the workbook open I saw the usage steadily increase on each of the first 10 iterations. For iteration 11, the first with the workbook closed, there was a recovery but at a level much increased from the original amount used by Excel. For iterations 11 to 20 with the workbook closed the usage was *exactly* the same each time. For iteration 21, with the workbook open again, there was an immediate and significant rise in usage and it kept rising for the next nine iterations. This pattern continued to the end. I immediately ran the code again and the usage hadn't recovered much from the last iteration i.e. even closing and releasing the Excel application didn't get the memory back. This looks exactly like a memory leak to me i.e. I have closed and released everything and AFAIK flushed the Jet cache in the process but I haven't got the memory back. If not a memory leak then what is it? And how to I get this non-leaking yet absent memory back? someone recently told me that the proliferation of myths flies in the face of 'the Usenet way', so I might advise against posting as such. Actually, I told you can expect people to jump on your case if your sole piece of 'advice' is based on superstition rather than fact. I may have made a mistake somewhere but I'm making my assertions in good faith based on my observations. I am not trying to be contrary for the sake of it. I am open-minded to being wrong and, as I said before, I hope I am wrong. I've posted my code and my output (below). In what way do you think I am trying to invent or repeat mythology? Here is my output in full: Memory used by Excel: 2166784 <<< original Memory used by Excel: 9183232 <<< iteration 1: workbook open Memory used by Excel: 9490432 Memory used by Excel: 9814016 Memory used by Excel: 10125312 Memory used by Excel: 10440704 Memory used by Excel: 10760192 Memory used by Excel: 11083776 Memory used by Excel: 11407360 Memory used by Excel: 11710464 Memory used by Excel: 12013568 Memory used by Excel: 8048640 <<< iteration 11: workbook closed Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 8048640 Memory used by Excel: 12644352 Memory used by Excel: 12939264 Memory used by Excel: 13238272 Memory used by Excel: 13541376 Memory used by Excel: 13844480 Memory used by Excel: 14151680 Memory used by Excel: 14450688 Memory used by Excel: 14757888 Memory used by Excel: 15065088 Memory used by Excel: 15372288 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 11395072 Memory used by Excel: 15953920 Memory used by Excel: 16236544 Memory used by Excel: 16535552 Memory used by Excel: 16838656 Memory used by Excel: 17145856 Memory used by Excel: 17444864 Memory used by Excel: 17752064 Memory used by Excel: 18055168 Memory used by Excel: 18358272 Memory used by Excel: 18657280 I immediate ran the code a second time: Memory used by Excel: 14700544 Memory used by Excel: 19308544 Memory used by Excel: 19603456 Memory used by Excel: 19902464 Memory used by Excel: 20213760 Memory used by Excel: 20525056 Memory used by Excel: 20832256 Memory used by Excel: 21143552 Memory used by Excel: 21430272 Memory used by Excel: 21745664 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22061056 Memory used by Excel: 22413312 Memory used by Excel: 22745088 Memory used by Excel: 23068672 Memory used by Excel: 23384064 Memory used by Excel: 23736320 Memory used by Excel: 24092672 Memory used by Excel: 24449024 Memory used by Excel: 24805376 Memory used by Excel: 25169920 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25522176 Memory used by Excel: 25890816 Memory used by Excel: 26247168 Memory used by Excel: 26603520 Memory used by Excel: 26980352 Memory used by Excel: 27336704 Memory used by Excel: 27697152 Memory used by Excel: 28053504 Memory used by Excel: 28409856 Memory used by Excel: 28770304 Memory used by Excel: 29126656 Here is my code: Option Explicit Private Declare Function PdhVbOpenQuery Lib "pdh.dll" _ (ByRef QueryHandle As Long) As Long Private Declare Function PdhCloseQuery Lib "pdh.dll" _ (ByVal QueryHandle As Long) As Long Private Declare Function PdhVbAddCounter Lib "pdh.dll" _ (ByVal QueryHandle As Long, ByVal CounterPath As String, _ ByRef CounterHandle As Long) As Long Private Declare Function PdhRemoveCounter Lib "pdh.dll" _ (ByVal CounterHandle As Long) As Long Private Declare Function PdhCollectQueryData Lib "pdh.dll" _ (ByVal QueryHandle As Long) As Long Private Declare Function PdhVbGetDoubleCounterValue Lib "pdh.dll" _ (ByVal CounterHandle As Long, ByRef CounterStatus As Long) As Double Private Declare Function PdhVbIsGoodStatus Lib "pdh.dll" _ (ByVal StatusValue As Long) As Long Private Declare Function PdhVbGetOneCounterPath Lib "pdh.dll" _ (ByVal PathString As String, ByVal PathLength As Long, _ ByVal DetailLevel As Long, ByVal CaptionString As String) As Long Private Declare Function PdhVbCreateCounterPathList Lib "pdh.dll" _ (ByVal DetailLevel As Long, ByVal CaptionString As String) As Long Private Declare Function PdhVbGetCounterPathFromList Lib "pdh.dll" _ (ByVal Index As Long, ByVal Buffer As String, _ ByVal BufferLength As Long) As Long Private Declare Function PdhVbGetCounterPathElements Lib "pdh.dll" _ (ByVal PathString As String, ByVal MachineName As String, _ ByVal ObjectName As String, ByVal InstanceName As String, _ ByVal ParentInstance As String, ByVal CounterName As String, _ ByVal BufferSize As Long) As Long Private Sub Command1_Click() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim i As Integer Dim j As Integer Dim max As Integer Dim r As Long Dim hPDHQuery As Long 'Handle to performance monitor query Dim hPDHCounter As Long 'Handle to performance monitor counter Dim strCounterPath 'Path to performance monitor counter Dim lngCounterStatus As Long 'Status of counter when checked Dim dblPrivateBytes As Double 'Value of counter when checked ' Open Excel Dim xl As Object Set xl = CreateObject("Excel.Application") 'Initialize PDH query object. r = PdhVbOpenQuery(hPDHQuery) 'Initialize counter. strCounterPath = "\\JAMIEC2\Process(Excel)\Private Bytes" r = PdhVbAddCounter(hPDHQuery, strCounterPath, hPDHCounter) 'Gather data. r = PdhCollectQueryData(hPDHQuery) 'Get counter value and process data. dblPrivateBytes = PdhVbGetDoubleCounterValue(hPDHCounter, _ lngCounterStatus) If PdhVbIsGoodStatus(lngCounterStatus) Then Debug.Print "Memory used by Excel: " & CLng(dblPrivateBytes) Else Debug.Print "Invalid data." End If max = 10 * 5 ReDim alngPrivateBytes(max) Dim wb As Object Dim strTargetWB As String For i = 1 To 5 For j = 1 To 10 If j = 1 Then If i Mod 2 = 1 Then Set wb = xl.Workbooks.Open("C:\db.xls") Else wb.Close False End If End If Set cn = New ADODB.Connection cn.ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Tempo\New_Jet_DB.mdb" cn.CursorLocation = adUseClient cn.Open strTargetWB = "C:\db" & ((i - 1) * 10) + j & ".xls" On Error Resume Next Kill strTargetWB On Error GoTo 0 cn.Execute _ "SELECT * INTO" & _ "[Excel 8.0;Database=" & strTargetWB & ";]" & _ ".test FROM [Excel 8.0;Database=C:\db.xls;].[Sheet1$]" r = PdhCollectQueryData(hPDHQuery) 'Get counter value and process data. dblPrivateBytes = PdhVbGetDoubleCounterValue(hPDHCounter, _ lngCounterStatus) 'Process data. If lngCounterStatus = 0 Then Debug.Print "Memory used by Excel: " & CLng(dblPrivateBytes) alngPrivateBytes(((i - 1) * 10)) = CLng(dblPrivateBytes) Else Debug.Print "Invalid data." alngPrivateBytes(0) = 0 End If cn.Close Set cn = Nothing ' just for Nate <g Next Next ' Debug.Print "Total increase for " & max & " iterations: " & _ ' (CStr(alngPrivateBytes(max) - alngPrivateBytes(0))) ' Debug.Print "Average increase per iteration: " & _ ' (CStr((alngPrivateBytes(max) - alngPrivateBytes(0))) / max) 'Clean up. r = PdhRemoveCounter(hPDHCounter) r = PdhCloseQuery(hPDHQuery) ' Close Excel xl.Quit Set xl = Nothing End Sub Jamie. -- |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Hello Jamie,
Actually, I did do some testing. Usually do, but I'm typically not a memory leak hunter, so this variety of testing is somewhat foreign to me, i.e., I don't usually pull up perfmon.exe to check on process bytes. Why do I not try to break to ADO for you? Honestly, it's not how I prefer to spend my time. Can I? Not sure. Have some respectable programmers (in my estimation) recommend taking out your own trash versus relying on VB[A] to do it? Yes, for whatever reason, be it a DAO, Excel, ADO Error, whatever, good enough for me, it seems like reasonable defense. It's mentioned again he http://msdn.microsoft.com/library/en...l/pooling2.asp Not my findings, but I'll go with the heard on this one. Like declaring variables, it makes sense to me to explicitly destroy that which I create, so I do and move on with life. Do you have to declare variables in VB[A]? I'll leave you to your ongoing experimentation. Funny you should mention the discord between the two threads. In the other thread it seems you're arguing that ADO is stable and to be trusted, and here, well, we're seeing something that's not good, whether it's Excel, Jet or ADO. In any case, back with the quandary at hand, you might be right. I got playing with a loop last night myself, it looked something like the following: Sub ExToEx() Dim cn As Object, i As Long Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _ ThisWorkbook.FullName & ";Extended Properties=Excel 8.0;" For i = 1 To 1000000 Kill "c:\temp\book1.xls" cn.Execute "SELECT Col1, Col2 INTO " & _ "[Excel 8.0;Database=c:\temp\book1.xls].[Sheet1]" _ & " FROM [Sheet1$a:b] Where Col2 = " & Sheets(2).Range("a1").Value Next cn.Close: Set cn = Nothing End Sub And I played around with binding in the loop as well. I wasn't pleased with the results. When I did this by hand, yesterday it appeared, by my eye, to stabilize. When I continuously slammed Jet with the loop, memory was getting chewed up with each pass. When I closed the Workbook, memory appeared to crawl back, very slowly. And when I closed the Excel App, I got a giant dose of memory back, which sounds inconsistent with what you're saying, but, my observation based on limited testing. I have the latest SP for Jet, it appears that the cache might be the issue, it doesn't appear to be cleared out and hangs with the App. Is it a classic memory leak? Not sure, but it's not good and is a shame. Cheers, Nate Oliver |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
filter with vb code
Nate Oliver wrote: I wasn't pleased with the results. Is it a classic memory leak? Not sure, but it's not good and is a shame. Sincere thanks for this. In the other thread it seems you're arguing that ADO is stable and to be trusted, and here, well, we're seeing something that's not good, whether it's Excel, Jet or ADO. I'm trying to concentrate on specifics. I've seen bad things happen when using ADO + Jet + an open Excel workbook, so I view it as an unstable aspect of ADO. Likewise isolated and specific unstable aspects of DAO, Excel, etc. I've yet to see bad things happen when ADO objects are closed/released implicitly and/or in the 'wrong' order, so I consider it a stable aspect of ADO. I've not seen or heard of bad things happening when the VB garbage collector is trusted to do its work. Jamie. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter isn't working with a VBA Code | Excel Worksheet Functions | |||
vba code to filter | Excel Programming | |||
filter code help | Excel Programming | |||
Filter Code | Excel Programming | |||
vba filter code | Excel Programming |