Help with Syntax Using ADO to Query Excel Worksheet

[jakebailey]

Hi Everyone!
I'm at the end of a fairly large project with Excel and having troubles moving data from one worksheet to another.
Using Excel 2007
Here's the scenario:

I have worksheet called 'Data' that holds thousands of records with more than 30+ columns. I'm trying to extract only records that have certain criteria and then copy them to either a new worksheet or workbook. In addition to that I need to create a calculated field based on two other fields and have that copy with it. Here's an example of my Query:

[VBA]strQuery = "SELECT * FROM [Sheet1$] WHERE Rep = 'ABC' ;[/VBA]

This works just find but it brings in all 30+ columns where I only want a fraction of that.
I've tried returning just the date in column "Customer Name" just as a test and it didn't work. I tried with or without quotes and no luck.

[VBA]strQuery = "SELECT Customer Name FROM [Sheet1$] WHERE Rep = 'ABC' ;"[/VBA]

Everything I have found online about ADO and creating a calculated field revolves around Access which I'm not using in this project.

Any help would be greatly appreciated.

Thanks,
Jake

[slamet Harto]

untested

strQuery = "SELECT tabelname.fieldname FROM tabelname WHERE tabelname.fieldname = 'the criteria'

[Bob Phillips]

Why are you using ADO< why not just filter the data and then copy the visible data?

[jakebailey]

Thanks for the quick reply.
You know I'm not sure why I'm using ADO to be honest. In the past when I needed to clean up data exported from my system I would put each table into it's own worksheet and then use Microsoft Query to create the joins and extract the data to a new workbook so it can be used for Pivot Tables and charts. From what I've read I should no longer be using Microsoft Query and instead use ADO.... That's the only reason why I'm looking at doing this. I'm completely open to other ways of doing this but I probably will need a little direction.

XLD - Can you give me a little more detail or point me in the right direction on what I should research?

Thanks,
-J

[Marcster]

Untested but try:


[vba]

strQuery = "SELECT F3 FROM [Sheet1$] WHERE F1 = 'ABC' ;

[/vba]
Where column C (F3) is Customer Name and Column A (F1) is Rep.

Your ADO connection can name your fields F1, F2 etc for Column A, B etc.