jakebailey
07-22-2010, 09:11 AM
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:
strQuery = "SELECT * FROM [Sheet1$] WHERE Rep = 'ABC' ;
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.
strQuery = "SELECT Customer Name FROM [Sheet1$] WHERE Rep = 'ABC' ;"
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
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:
strQuery = "SELECT * FROM [Sheet1$] WHERE Rep = 'ABC' ;
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.
strQuery = "SELECT Customer Name FROM [Sheet1$] WHERE Rep = 'ABC' ;"
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