Reports output in Excel

[fat controller]

How much formatting can be done to a report from Access into Excel? I am trying to (or will be in the next day or so) to create a report to export data and I would like it presented in a specific format. This is hopefully to replace a spreadsheet where someone currently has to collate and re-type a load of info that has already been typed into various other spreadsheets.

Access is going to happily cope with all of those users entering their data to a table, and I would like to be able to output that data to something similar to the end result now?

Is there a way to have an Excel template file and simply direct Access what to put in which cell?

 

[GinaWhipp]

You can either Format an Excel spreadsheet and have Access drop to that Template (it's quicker) or Format Excel every time you do the export. Have a look here...
http://btabdevelopment.com/code-snippets/page/2/

Quite a few of those links show how to Format Excel from Access.

 

[fat controller]

A template would be the ideal solution, as I already have a spreadsheet that is formatted. The slight fly in the ointment is that the current template is across five different sheets (four where the data is entered, and the fifth which gives the totals.

I may get away with only doing the totals sheet, in which case I Access could take care of that for me - but I am not sure if that would be suitable without asking what the requirement is. Am I right in thinking that Access can only export to one sheet?

I am certainly going to be back for help with this one, as I am a wee bit daunted at the prospect...... (although determined to see it work)

Would this be the sort of thing I should be looking at?

 

[GinaWhipp]

Great! I use Templates... much easier. You can export to multiple Worksheets in the same Workbook, however, depending on what you are sending to each Worksheet you may need separate Functions.

That link you posted, that is exactly what you should be looking at. Of course, you're going to modify for your environment but no worries, we're to help if you get stuck!

 

[fat controller]

That is brilliant, thank you - I will get a copy of the current spreadsheet and empty it ready to use as a template. Is is pretty much a case of telling it which fields to export to which sheet & cell?

The other option I could do is have a 'plain' sheet for import where Access shoves all its data in one row, and have the other sheets linked to the relevant cells in the row? Not my preferred option though.

EDIT - one other thing - I might need to have a report from another table/query add to the same spreadsheet at the same time, is that possible?

Something along the lines of the top 25 rows are for one group of staff, the 10 rows below that are for another group of staff - information is slightly different for each group but the time spans are the same. I'm happy to try and expand/explain more if it doesn't make sense

 

[GinaWhipp]

Is it pretty much a case of telling it which fields to export to which sheet & cell?

Yes, it is...

Yes, you could use that second option, I've never tried it but it could work just as well.

 

[fat controller]

Just a quick thought - am I right in thinking that as the dates will be the same (PeriodStartDate), I can simply join the results from both tables at query level and then export the query to the template?

 

[fat controller]

OK, I will hopefully be getting to this over the weekend - what bit of the code is it that does the following:

- Opens the template
- Selects the correct worksheet
- Export data from relevant field to relevant cell (and repeat)

And is there a way to force the user to save the excel doc to prevent the template being overwritten?

I assume that the template file will have to be lodged somewhere (in along side the back end file?)

 

[GinaWhipp]

Are you asking because you don't understand the code? I'm just trying to understand how I should frame my answer so as not to cause you any confusion.

There are a few lines you can add to Save the Excel Workbook leaving the Template intact.

The Template should be on a Network Drive so everyone has access to it. However, the individual newly created Workbooks can be saved on the User's Local Drive.

 

[fat controller]

Yeah, although gaining confidence with VBA, I am still very much a learner; I am keen to learn the code as I use it, not least as I have to look after this going forward - that, and I can see little point in simply copying and pasting code in the hope it works, because I won't actually know how it worked if (and when) I need to replicate it.

 

[GinaWhipp]

Busy day...

Okay post the code you are using so I can comment the sections with what it does.

 

[fat controller]

As yet, I haven't started with any code - just trying to make sense of the code that was in the link first. Been working outdoors so not been near a PC to even try anything - going to give it a shot tomorrow.

 

[GinaWhipp]

Hmm, then you want me to pick one and amend it. Well, going to need to know a few things so I can get it close to what you want...

1. Name of Template
2. Name you would like to *Save As*
3. Path of Template
4. Path for *Save As* Template
5. Cell references... which cells, not them all but a few so you can get the idea
6. Name of Query (or Table) being used

 

[fat controller]

Something to get me going to understand it would be fantastic - Once I see bits of it in action, I can generally work out the detail from there.

To answer your questions:

1. 4WeeklyMasterTemplate.xls
2. Save as "FourWeeksEnding" & Date where date is calculated from a date on the form that runs the report, but I will be able to work that last bit out easily enough
3. E:\Payroll Test\Template
4. E:\Payroll Test

I come unstuck here as I haven't yet worked out the query, so I will come back with these once I have a working query?

The problem I have is that I would like the upper half of a the spreadsheet to be populated with the data from one table, and the bottom half from another. I have two queries at the moment each doing one table, but I am unsure how to get them combined into one?

I will ask in the queries area.

Thanks again

 

[GinaWhipp]

I can use two queries or a table and a query, just gets a little trickier and I will use two (or more) Functions to make that work. I just need the names.

 

[fat controller]

You are a true diamond

First Query is called - 4WeeklyPayrollControllersProcess
Second Query is called - 4WeeklyGSAProcess


[QueryField] Excel Sheet and Cell

[4WeeksCommencing] Week 1 K2
[EmployeeNumber] Week 1 A8
[ControllerName] Week 1 B8
[W1SatEnhTotal] Week 1 C8
[W1M-FEnhTotal] Week 1 D8
[W2SatEnhTotal] Week 2 C8
[W3SatEnhTotal] Week 2 D8

These are a few from the first query, with subsequent records to be put into the row underneath (so A9, C9 & D9 etc)

From the second query

[EmployeeNumber] Week 1 A33
[ControllerName] Week 1 B33
[W1SatOTHrsGSA] Week 1 C33
[W2SatOTHrsGSA] Week 2 C33

Same applies as above, subsequent records to drop down one row.

Hope this makes sense, and thanks again for your help

 

[GinaWhipp]

It's enough to get me started... but not right now. It's Sunday taking a bit of a break but I should have something by tomorrow evening (EST).

 

[fat controller]

Please do not put yourself out - I am in no position to be placing time demands on you; I am extremely grateful for the fact that you are taking the time to help me, so it gets done when you have an opportunity

Enjoy some rest (which I must confess I am doing now too, so will raise a glass in your honour)

Cheers

 

[fat controller]

Just wanted to add that I am likely to be at the PC over this weekend if any more info is needed; if you don't have time, no matter

 

[GinaWhipp]

First let apologize... First the Holidays and then I got sick and all that in between my *day job*. So sorry, I didn't reply in a timely fashion... That said, do you still need this?