manipulating Query data 'on the fly'Question
 

Hi all.

I understand how to import external source data, and I am even getting
pretty good at using MSQuery to create QueryTables.

My question involves how to manipulate the query data in VBA prior to
outputing the query result onto a spreadsheet.

For a generic example,

Say I have a workbook with 3 worksheets.
I want to query a date/time range on an external database. I want to output
all of the dates that fall before today's date on sheet 2 range A6. I want
everything that falls on todays date to be output to sheet 2 range B13. And
all of the dates that fall after todays date on sheet 3 range G4.

The way I work around this problem now is to output the entire query to a
seperate 'data' page, then manipulate the data in vba. This really isn't
ideal. I would like to be able to somehow query the database and place the
result set into variables or arrays, so that I can manipulate the data prior
to output on the worksheets.

Any help appreciated.

manipulating Query data 'on the fly'Question
 

On 19 Jan., 11:23, Xaos wrote:
Hi all.

I understand how to import external source data, and I am even getting
pretty good at using MSQuery to create QueryTables.

My question involves how to manipulate the query data in VBA prior to
outputing the query result onto a spreadsheet.

For a generic example,

Say I have a workbook with 3 worksheets.
I want to query a date/time range on an external database. *I want to output
all of the dates that fall before today's date on sheet 2 range A6. *I want
everything that falls on todays date to be output to sheet 2 range B13. *And
all of the dates that fall after todays date on sheet 3 range G4.

The way I work around this problem now is to output the entire query to a
seperate 'data' page, then manipulate the data in vba. *This really isn't
ideal. *I would like to be able to somehow query the database and place the
result set into variables or arrays, so that I can manipulate the data prior
to output on the worksheets.

Any help appreciated.

Hi

Show us the code you are using now.

Regards,

Per

manipulating Query data 'on the fly'Question
 

"Per Jessen" wrote:

On 19 Jan., 11:23, Xaos wrote:
Hi all.

I understand how to import external source data, and I am even getting
pretty good at using MSQuery to create QueryTables.

My question involves how to manipulate the query data in VBA prior to
outputing the query result onto a spreadsheet.

For a generic example,

Say I have a workbook with 3 worksheets.
I want to query a date/time range on an external database. I want to output
all of the dates that fall before today's date on sheet 2 range A6. I want
everything that falls on todays date to be output to sheet 2 range B13. And
all of the dates that fall after todays date on sheet 3 range G4.

The way I work around this problem now is to output the entire query to a
seperate 'data' page, then manipulate the data in vba. This really isn't
ideal. I would like to be able to somehow query the database and place the
result set into variables or arrays, so that I can manipulate the data prior
to output on the worksheets.

Any help appreciated.

Hi

Show us the code you are using now.

Regards,

Per



Well, None of my current code applies to my question.

Let me try to explain in a different way

Say I create a query table using something like:

With ActiveSheet.QueryTables.Add( _
Connection:=rstRecordset, _
Destination:=Range("A1"))


Notice the destination parameter.

Now through VBA help file it reads:

"Destination Required Range. The cell in the upper-left corner of the
query table destination range (the range where the resulting query table will
be placed). The destination range must be on the worksheet that contains the
QueryTables object specified by expression ."

What I need to do is find an alternate method so that my 'destination' is
not a range on a worksheet, but rather an array variable or even a long
string variable that allows me to manipulate the query results pre-output.