Edit and output a crosstab query to a file

[Mike]

Hi everyone,

I found the Access reports too limited to do what i wanted so i created
a module to export a crosstab query to an excel file, and then i modify
it as i want. My problem is that i created a unique query for every
"sector", saved them, and select the right one according to the user's
choice.

What i'd like to do is to create the crosstab query directly in VBA,
change the WHERE part for the sector i want to show and output it to
the excel file with or without saving the query (if not i'd like to
erase if afterwards, or replace it everytime).

I dont think the runSQL command would work since it only runs action
queries, and i dont think that it is possible to make a crosstab table
neither. Could anyone help me?

Thanx alot,

Mike

[pietlinden]

Mike wrote:

Hi everyone,

I found the Access reports too limited to do what i wanted so i created
a module to export a crosstab query to an excel file, and then i modify
it as i want. My problem is that i created a unique query for every
"sector", saved them, and select the right one according to the user's
choice.

What i'd like to do is to create the crosstab query directly in VBA,
change the WHERE part for the sector i want to show and output it to
the excel file with or without saving the query (if not i'd like to
erase if afterwards, or replace it everytime).

I dont think the runSQL command would work since it only runs action
queries, and i dont think that it is possible to make a crosstab table
neither. Could anyone help me?

Thanx alot,

Mike

why not create the query in your code, open a recordset based on it,
and then send the whole thing to Excel with this:
http://www.mvps.org/access/modules/mdl0035.htm

[Wayne Gillespie]

On 16 Nov 2006 14:11:25 -0800, pi********@hotm ail.com wrote:

>
Mike wrote:

>Hi everyone,

I found the Access reports too limited to do what i wanted so i created
a module to export a crosstab query to an excel file, and then i modify
it as i want. My problem is that i created a unique query for every
"sector", saved them, and select the right one according to the user's
choice.

What i'd like to do is to create the crosstab query directly in VBA,
change the WHERE part for the sector i want to show and output it to
the excel file with or without saving the query (if not i'd like to
erase if afterwards, or replace it everytime).

I dont think the runSQL command would work since it only runs action
queries, and i dont think that it is possible to make a crosstab table
neither. Could anyone help me?

Thanx alot,

Mike

why not create the query in your code, open a recordset based on it,
and then send the whole thing to Excel with this:
http://www.mvps.org/access/modules/mdl0035.htm

This method will no longer work in A2002 (if patched after 18 Oct 2005) and
A2003. MS have removed this functionality from Access due to some sort of legal
dispute. (Can't recall the details)

(from KB904018)
INTRODUCTION
Microsoft has released an update for Microsoft Access 2002. This update removes
the functionality in Access 2002 that lets users add new data to or edit
existing data in a linked Microsoft Excel worksheet.

There is workaround code at http://support.microsoft.com/kb/904953/en-us
(KB904953)

Wayne Gillespie
Gosford NSW Australia