By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,628 Members | 1,175 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,628 IT Pros & Developers. It's quick & easy.

Edit and output a crosstab query to a file

P: n/a
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

Nov 16 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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

Nov 16 '06 #2

P: n/a
On 16 Nov 2006 14:11:25 -0800, pi********@hotmail.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
Nov 17 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.