Exporting queries into multiple Excel worksheets (1 Viewer)

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
Hi guys,

Work have asked for a lot of information to be run from Access and exported into Excel. The info they require will need to be exported into 4 Excel worksheets in the same workbook. Is it possible to tell Access that when they click on the report button on the form, it will automatically run the various queries and then put them into separate worksheets in the same book? I think this is perhaps too complex for Access to do?

Thanks for any suggestions!

Jim
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
Not too complicated and you have a choice...

How experienced are you with VBA?

Are there templates set up or is just going to a new workbook every time? If you are exporting to a Template might be better, then you can set up the Headers anot have to code for that also and I like Templates, easier.

OR

You can just set a TransferSpreadsheet and run it 4 times behind your button, no code involved there.
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
HI Gina,

Its a set of queries I've set up that bring through a load of info on customers, sales and products. The board requested they be in separate worksheets for ease of reading.

My VBA skills are pretty basic. I can do things like command Access when a button is pressed on a form, to export/open in Excel. Beyond that I'd struggle. Basically I would want when the button is pressed to:

Run the 3 queries & insert them into the same Excel spreadsheet. As different board members will be using this, they don't have time (reads can't be bothered) to just paste them in to one spreadsheet manually.

I can set a Excel template up (e.g. report titles, fonts etc) before hand if that would be easier?

I'm under a bit of pressure at work so I really need to impress with this.

Thanks for your reply.

J
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
I can't gaurantee speed but I'll be as fast as I can... I really do have a day job! :D

Yes, please set up the Templates, I caouldn't do it anyway, no idea what they need to look like. Just because I have the extra work when naming the worksheets (tabs) PLEASE no spaces, i.e...

Company Information :(
CompanyInformation :)

So, obviously I want the happy face. I am also going to need...

1. The starting Row for each worksheet
2. If you want filtering set up
3. Do you want Excel open or closed once the export is completed?
4. Do you want a Date to show (that will require some info from you but for now, yes or no will do)
5. Are any of the columns going to require totals at the bottom
6. UNC Path to where the Template(s) are
7. Name of the Template
8. Path to where you want the file(s) to be and names you want them to be

And finally... a VERY big box of chocolates! :D (Kidding, my doctor would have a fit!)
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
Ha! I didn't mean you need to work quickly, just my company expect me to work magic on things that take time. Last week I was asked to convert 300+ Access reports into SQL, with them expecting it done in 2 days. Considering they're paid so much, they aren't half dumb!

Ok,
- template is called "Perfomance2013".
- worksheets are called 'NewCustomers13', 'NewSales13', 'AllProducts13'
- filters/sorts are done in the queries already so not needed additionally in Excel
- I want excel to open once the export is done
- Date is not important but if it isn't too hard to do it would be useful
- No totals are needed, totals are part of the queries already
- template path is z:\CompanyHO\StandardForms\MIO\
- to save to individual users desktops on their C:\ (not sure of exact name!)

Worsheet1 (NewCustomers13)
CLIENTID: 12345
Contact: Joe Bloggs
Company: Zebra Inc.
Street1: 123 Alpha Street
Town: London
County: Greater London
Marketing Source: Website
Pay by: Cash
Enquiry Date: 01/01/2013
First Sale Date: 03/01/2013

Worksheet2 (NewSales13)
CLIENTID:12345
Company: Zebra Inc.
Sales Since: 03/01/2013
Last Sale: 11/12/2013
Total 2013 Sales: 8
Total 2013 Rev: £1,500
Purchase Method: Telephone

Worksheet3 (AllProducts13)
Product ID: AV1234
Product Name: CodeTenA
Product Colour: Green
Product Weight: 10kg
With Parts: Yes
Delivery Type: Courier only
Stocked since: 01/01/2012
Planned expiry: 01/06/2015
Product Cost 2013: £1.49
Sales Price 2013: £3.66
Total Unit Sales 2013: 140
Total Product Cost 2013: £1,500
Total Sales Revenue 2013: £3,500
Variance 2013: -£500

I think that answer all the questions? There might me one or 2 fields to add to some of the queries but the basics are there. The data is made up, if you hadn't figured that out already! :)

If you can do this, name a charity, i'll give £5 to it. Better than chocolates!
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
Not to bad, thanks for the answers, if I have more questions I'll post. I do see one, issue I prefer UNC paths because not everyone will name the drive Z:\. But I still enough to get started. Though it's dinner time here and I'm hungry... so later tonight I will post the code with instructions, if done oR with more questions. Not really working tomorrow... it is Independence Day and well, I'll try to peek in but no promises!

No charity, find a child and buy them a book! Tell them it came from a crazy lady that thinks *Reading is Fundamental!*
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
Okay, a few more questions and a problem (maybe)...

Issue...
filters/sorts are done in the queries already so not needed additionally in Excel

The query cannot ask for criteria. If it does it will not run, so the criteria has to be set from the Form. If you have a query getting criteria from the Form, I need the SQL posted here.

Questions...
1. Query names only if no criteria, if criteria post the SQL here
2. What is the starting Row on the worksheets? A2?

Code is almost finished (and ready for testing) with the answers to the above questions.
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
Hi Gina!

Having discussed with my colleagues, no sorts are necessary so we don't need to worry about that.

My column headings will be in A1, B1 etc and data will start in A2, B2 etc. in all 3 worksheets (hope that makes sense!)

My 3 queries are called:

1 - "Qry_NewCustomers13"
2 - "Qry_NewSales13"
3 - "Qry_AllProducts13"

Hope that makes sense.

As for the book, i'll donate some of mine to the local kids library. I have outgrown Harry Potter etc :p
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
No, I dodn't need the Heading. I just need to know in your Templates, do you have a Header row? What Row do you want me to start the data on?

Hmm... will never outgrow Harry Potter!
Great idea... Library = MANY kids!
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
Oh, but you don't want the way access puts them in, very ugly and ot professional looking, so my advise is to put in a Header row and I'll start on Row 2. You want to WOW them right? :D
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
Hi! Yes, you are right, I should put headers. Can I used the field names for each column, as above?
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
You can use those names or anything you want in the Headers.

And let say sorry :( had so much fun and food that I did not finish yesterday. I will be getting to later today.
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
Thanks Gina! And don't say sorry, you're doing me a massive favour :) :)
 

GinaWhipp

AWF VIP
Local time
Today, 06:12
Joined
Jun 21, 2011
Messages
5,901
Okay, sorry for delay but here you go...

UNTESTED so please try on test database.

Code:
Function SendToExcel(strTQName As String, strSheetName As String)
'From [URL]http://www.btabdevelopment.com/ts/freetools[/URL] (modified)
' strTQName is the name of the table or query you want to send to Excel
' strSheetName is the name of the sheet you want to send it to
Dim rst1 As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim ApXL As Object
Dim xlWBk As Object
Dim xlWSh1 As Object
Dim xlWSh2 As Object
Dim xlWSh3 As Object
Dim fld As DAO.Field
Dim strPath As String
 
On Error GoTo Err_Handler
 
strPath = "Z:\CompanyHO\StandardForms\MIO\Perfomance2013.xlsx"
 
Set rst1 = CurrentDb.OpenRecordset(Qry_NewCustomers13)
Set rst2 = CurrentDb.OpenRecordset(Qry_NewSales13)
Set rst3 = CurrentDb.OpenRecordset(Qry_AllProducts13)
 
Set ApXL = CreateObject("Excel.Application")
Set xlWBk = ApXL.Workbooks.Open(strPath)
 
Set xlWSh1 = xlWBk.Worksheets(“NewCustomers13”)
Set xlWSh2 = xlWBk.Worksheets(“NewSales13”)
Set xlWSh3 = xlWBk.Worksheets(“AllProducts13”)
 
 
rst2.MoveFirst
xlWSh2.Range("A2").CopyFromRecordset rst2
 
rst3.MoveFirst
xlWSh3.Range("A2").CopyFromRecordset rst3
 
rst1.MoveFirst
xlWSh1.Range("A2").CopyFromRecordset rst1
' selects the first cell to unselect all cells
xlWSh1.Range("A1").SELECT
 
 
rst.Close
Set rst = Nothing
'Remove prompts to save the report
ApXL.DisplayAlerts = False
xlWBk.Save
ApXL.DisplayAlerts = True
ApXL.Visible = True
'ApXL.Quit
 
Exit Function
Err_Handler:
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
HI Gina! Thank you soooo much for this. I'm on leave at the moment, I'll give this a try when I get back to work next week. I've donated my entire set of Potter books and also a few of my Wilbur Smith novels to the local library, as promised :) :) Will drop you a reply next week. :)
 

jimtimber

Registered User.
Local time
Today, 10:12
Joined
Apr 25, 2014
Messages
118
Hey! Just to let you know this works beautifully. A bit slow on my Access but works. THANK YOU GINA :) x
 

Users who are viewing this thread

Top Bottom