Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 941
  • Last Modified:

export multiple queries to 1 excel spreadsheet

I have 5 queries which i need to export into 1 excel spreadsheet. each query will have its own tab in excel.

class anyone?

don't know where to get started. queries are are already built.

all help is appreciated.
0
lallentx
Asked:
lallentx
  • 2
  • 2
1 Solution
 
pique_techCommented:
Repeated use of DoCmd.TransferSpreadsheet to the same Excel destination file will add each query on a tab with the same name as the query.

i.e.,
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Product", "c:\test.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProductCategory", "c:\test.xls", True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "ProductGroup", "c:\test.xls", True

produced a spreadsheet at c:\ called test.xls with three tabs, one called Product, one called ProductCategory, and the last called ProductGroup

You should delete the Excel file before repeating or you'll (potentially) get unexpected behavior.

Hope this helps.
0
 
pique_techCommented:
(That assumes that you're using MS Access...if not, then I'm pretty sure my method won't work.)
0
 
ndegioiaCommented:
Form what I can understand Execl can read sql staments.  If you are using a sql or access database you can write a stored proceedure to execute multiple queries.  Here is the sytax

create proceedure Myproceedure
as
Select yourColumn
Form YourTable
Where YourConditions
go
Select yourColumn
Form YourTable
Where YourConditions
go

...and so on


Then all you have to do in Execl of any other client side app is run

Exec MyProceedure
go


Note you must be connected to sql or save the stored prceedure as a Macro.

Hope this helps;)
0
 
lallentxAuthor Commented:
pique tech, I will try your approach first, and Yes! it is MS Access what I'm using.
I'll keep y'all posted.
0
 
lallentxAuthor Commented:
Pike tech,
you are a genius in my book!

it worked!

thanks so so much.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now