Excel SQL Query

[abi_learn_vba]

Hi,

I have two issues.

First

I have below data (sample data) in sheet1 in my excel. I wanted to extact the rows which is <> 'Y' in column F3. But my my query does not return anything, but when i use = 'Y' it extract the two rows correctly.

Date

F1	F2	F3
A	A	Y
B	B
C	C	Y

<TBODY>
</TBODY>

Code

Sub CHK()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim conStr As String
Dim sql As String
conStr = "provider=microsoft.jet.oledb.4.0;data source= " & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";extended properties=Excel 8.0;"
Set cn = New ADODB.Connection
cn.Open conStr
sql = "Select * from [Sheet1$] Where F3 <> 'Y'"
Set rs = New ADODB.Recordset
Set rs = cn.Execute(sql)
Range("H1").CopyFromRecordset rs
End Sub

Second:

With similar data when there are F3 values with both captial 'Y' and small 'y'.

F1	F2	F3
A	A	Y
B	B	y
C	C	Y

<TBODY>
</TBODY>

With below code it would extract only two rows A and C, looks like the search value is case sensitive, is there a workaround for this. Like 'abi' and 'Abi' are same , 'y' and 'Y' are same.

Sub CHK()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim conStr As String
Dim sql As String
conStr = "provider=microsoft.jet.oledb.4.0;data source= " & ThisWorkbook.Path & "\" & ThisWorkbook.Name & ";extended properties=Excel 8.0;"
Set cn = New ADODB.Connection
cn.Open conStr
sql = "Select * from [Sheet1$] Where F3 = 'Y' "
Set rs = New ADODB.Recordset
Set rs = cn.Execute(sql)
Range("H1").CopyFromRecordset rs
End Sub

This is a sample data and i am actually using huge rows of data.

Thanks & Regards
-Abi

 

[Jon von der Heyden]

Hi Abi

Why are you using ADO for this? What Excel version are you running & catering for?

I recommend using AutoFilter method;

For example;

Dim rngTable as Excel.Range

With Sheet1
    Set rngTable = Intersect(.UsedRange, .Range("A:C"))
    .AutoFilterMode = False
End With

With rngTable
    Call .AutoFilter(Field:=3, Criteria1:="<>Y")
    Call .SpecialCells(12).Copy(Destination:=Sheet2.Range("H1"))
    Application.CutCopyMode = False
    Cal .AutoFilter
End With

 

[abi_learn_vba]

Hi Jon,

Thanks for your reply. I used ADO feeling that it would much faster than using Autofilter, may be i am wrong.

Also i had problems using the autofilter in my earlier projects so thought of using ADO.

I am using excel 2010.

Thanks & Regards
-Abi

 

[Jon von der Heyden]

OK 2010 is OK for ADO (no memory leak querying same workbook); but personally I would still go with AutoFilter. If anything I would think it be faster because there's less to it. I could be wrong but either way it should be fairly instant. Have you tried it?

 

[abi_learn_vba]

Yes, i tried Autofilter. It was quick, compared to ADO it might be a milliseconds slow, but i am ok with it.

Thanks
-Abi

 

[Kyle123]

OK 2010 is OK for ADO (no memory leak querying same workbook

You sure about that Jon? I still get memory leaks, even using ACE drivers. I reckon you could get round it with DAO though

 

[Jon von der Heyden]

You sure about that Jon? I still get memory leaks, even using ACE drivers. I reckon you could get round it with DAO though

I'm not sure, no. I did a few bog standard tests and never noticed any memory leaks. But as far as this is concerned I would either

1. Avoid ADO altogether (such as the suggested AutoFilter method - which seems to have done the job - bonus!)

2. Export data to external workbook/csv file and hook up with that file using ADO - if ADO was deemed necessary.

But I can well imagine that these memory leaks may still occur.