execute sql query on excel columns using vba

Question

I'm doing excel data verification and would like to view any discrepancies within each column. And I think executing

"select distinct column1 from table_name"

is efficient. Is there a way to do that in vba with/without connecting to any database? Any advice would be appreciated.

[This answer](http://stackoverflow.com/a/8766541/445425) may be of some use — chris neilsen, May 17 '14 at 01:35

score 1 · Answer 1 · answered May 16 '14 at 16:18

1

This code should do the SELECT DISTINCT for one column:

ActiveSheet.Range("A2:A65536").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ActiveSheet.Range("B2"), Unique:=True

The code will paste the Distinct values from activesheet column A to column B. Hope this helps

answered May 16 '14 at 16:18

neophyte

Thanks a lot. It helps. But I don't want to change the raw file anyway :) – bananaLeaf May 16 '14 at 18:04
If you need something more specific let me know.. I answered according to question ;) – neophyte May 19 '14 at 07:06
Okay. I want the queried results as a recordset or something and handle the results in vba. So is creating an object connection the only option? – bananaLeaf May 20 '14 at 12:23
you could loop for every cell in the column.. if the count of cell value is 1 in the column add it to an array – neophyte May 20 '14 at 12:35

1 Answers1