Creating a temporary queryable dataset in Excel via VBA

[Jean9]

I'm wondering if anyone has any code to create a dataset from a selected range on a worksheet that can then be queried against and the resulting rows be written to a worksheet within the same workbook. I need the dataset to remain until a series of queries have been executed and then it can be disposed of. Please include any references that need to be added. The code will need to work in versions of excel 2003 and above.

Thanks in advance for any help provided

 

[SkipVought]

hi,

I sure can be done. You don't necessarily need VBA to query within a workbook but use your macro recorder if you need to generate code. I routinely do multiple queries to get data into a workbook and then queries within the workbook.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[UnsolvedCoding]

Can you give an example of what you want to do?

 

[Jean9]

@Skip
I'm unsure how to run actual complex queries against a selected range. Do you have an example?
@UnsolvedCoding
I would like to select all the rows and columns of a particular spreadsheet to include headers (if needed to identify column names), put all data into a temporary dataset, run a query on the dataset to select a set of fields in rows that meet a particular criteria and then write the results to another worksheet. For example: Select A8 through AZ44 (the 44 being determined by finding the number of the last row with data in it), A8 contains the column names. Write the results to a dataset. Run a select against the dataset like "Select Account, Distribution_Amt, Fund, Dist from rs where Business_Unit like 'TS*' and Account not in ('12345', 23456') and Dist_Amt <> 0 and Freq = 0" where rs is the dataset and the fields are the column headers. Either in one statement put the results of the query into another worksheet starting at A9 or cycle through the rs and put the rows into another worksheet starting at A9 of that worksheet. Then, run a different query against the same rs and put those results in yet another worksheet starting at whatever column/row particular to that worksheet. Then dispose of the rs.

I just kind of need a template of code to follow. I can plug and play with my particulars. I don't have enough experience with excel to know how to do something that in access is a simple matter.

Thanks all.

 

[SkipVought]

rather than talk around what you want, please post SAMPLE DATA and what you expect for your result and any intermediate state that is necessary to clarify your issue.

There are no 'templates' or 'code' that just happens to be hanging around that meets your criteria.

Anyone can throw code around, but that's as meaningless as an infinite number of monkeys pounding an infinite number of typewriters for an infinite amount of time to get a solution. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}