Use MACRO to export queries to specific SHEETs in ONE Excel file?


H

Hatman50

I'm sorry if this question seems familiar, but I've not found a thread
with a solution.
I want to export numerous querries to ONE Excel file having each
querry put in/on it's own SHEET.
Can this be done using an Access Macro?
If not, can VBA do it?

Thank you for any advice.
 
J

Jeff Boyce

How are you deciding which query goes on which sheet? You'll need to let
Access know ...

If you wish to have this process completely automatic, you'll probably find
you have more control by using a VBA procedure.

If you can specify the names of each of the spreadsheets (and each of the
queries), you could do it via macros. ... but if you're using an earlier
version of Access, you won't have a way to handle any errors in the macro.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Ad

Advertisements

H

Hatman50

How are you deciding which query goes on which sheet?  You'll need to let
Access know ...

If you wish to have this process completely automatic, you'll probably find
you have more control by using a VBA procedure.

If you can specify the names of each of the spreadsheets (and each of the
queries), you could do it via macros.  ... but if you're using an earlier
version of Access, you won't have a way to handle any errors in the macro..

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.







- Show quoted text -
We are using Access 2003. So the question is HOW do I do it via
Macros? How/Where in the macro do I refer to the SHEET name?
i.e.
In the Macro there is an Action called OutputTo. And at the bottom of
the screen are Action Arguments as follows.
Object Type = Query
Object Name= Name of Query
Object Format= Microsoft Excel(*.xls)
Output File= Path to Excel File
Auto Start= No
Template File= blank

Unfortunately The user DOES NOT want to use/learn VBA.
If it can be done via a Macro. Please advise How?
Regards.
 
J

Jeff Boyce

Hopefully one of the other newsgroup readers has specific experience with
this.

All my 'exports to Excel' have been via VBA, because of the control and
error handling.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

How are you deciding which query goes on which sheet? You'll need to let
Access know ...

If you wish to have this process completely automatic, you'll probably
find
you have more control by using a VBA procedure.

If you can specify the names of each of the spreadsheets (and each of the
queries), you could do it via macros. ... but if you're using an earlier
version of Access, you won't have a way to handle any errors in the macro.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.







- Show quoted text -
We are using Access 2003. So the question is HOW do I do it via
Macros? How/Where in the macro do I refer to the SHEET name?
i.e.
In the Macro there is an Action called OutputTo. And at the bottom of
the screen are Action Arguments as follows.
Object Type = Query
Object Name= Name of Query
Object Format= Microsoft Excel(*.xls)
Output File= Path to Excel File
Auto Start= No
Template File= blank

Unfortunately The user DOES NOT want to use/learn VBA.
If it can be done via a Macro. Please advise How?
Regards.
 
Ad

Advertisements

H

Hatman50

Hopefully one of the other newsgroup readers has specific experience with
this.

All my 'exports to Excel' have been via VBA, because of the control and
error handling.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.










We are using Access 2003. So the question is HOW do I do it via
Macros? How/Where in the macro do I refer to the SHEET name?
i.e.
In the Macro there is an Action called OutputTo. And at the bottom of
the screen are Action Arguments as follows.
Object Type = Query
Object Name= Name of Query
Object Format= Microsoft Excel(*.xls)
Output File= Path to Excel File
Auto Start= No
Template File= blank

Unfortunately The user DOES NOT want to use/learn VBA.
If it can be done via a Macro. Please advise How?
Regards.- Hide quoted text -

- Show quoted text -
Sorry Jeff, I must've misunderstood your previous post. I thought you
were suggesting that "It could be done via Macros" depending on the
Version of Access. That's why I was asking How?. My Bad!

Anybody Else have any success with this?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top