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

  • Thread starter Thread starter Hatman50
  • Start date Start date
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.
 
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.
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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.
 
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 -
Click to expand...

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?
 
I suspect that it can! The latest version of Access (2010) has added much
stronger macro functions. It's just that I haven't used macros recently,
since discovering the greater flexibility of VBA.

If this were mine, I'd probably look into whether the version of Access you
are using allows you to set up specific and detailed export specifications,
then if you can "call" them from a macro. That way, you could detail out
the query ("from") and the sheet ("to") as part of your export spec.

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.

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 -
Click to expand...

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?
 
Back
Top