UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
3 Pages V  1 2 3 >  (Go to first unread post)
   Reply to this topicStart new topic
> Report Grouping To Separate Excel Worksheets From Access, Any Version    
 
   
bakersburg9
post Aug 6 2019, 09:29 AM
Post#1



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


I'm producing a report that is grouped by project manager...would it be possible to send the report to Excel, and have each report (again, grouped by PM) to be saved in separate worksheets, with each project manager's name as the name of the worksheet? Or basically the same, but in separate workbooks?
Go to the top of the page
 
theDBguy
post Aug 6 2019, 09:43 AM
Post#2


Access Wiki and Forums Moderator
Posts: 76,304
Joined: 19-June 07
From: SunnySandyEggo


Hi. Interesting question. That would be nice. However, the only way I could think of right now is by exporting each report one group at a time. Also, depending on your report design, you may have to use Excel automation, or a simple export might work.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bakersburg9
post Aug 6 2019, 09:52 AM
Post#3



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


QUOTE
exporting each report one group at a time
thanks, DbGuy - do you think this could be done in a macro ? I don't mind running macros for 160 Project Managers...
Go to the top of the page
 
theDBguy
post Aug 6 2019, 10:04 AM
Post#4


Access Wiki and Forums Moderator
Posts: 76,304
Joined: 19-June 07
From: SunnySandyEggo


Well, when I said export each group one at a time, I was thinking of looping through the groups/managers. So, I am not sure but it may be possible to do a loop in a macro - I just haven't tried it yet.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
strive4peace
post Aug 6 2019, 10:15 AM
Post#5


strive4peace
Posts: 20,459
Joined: 10-January 04



Hi Steve,

do you just want the data in Excel, or do you want formatting too? Just data is, of course, easier! What is the SQL statement for the query your report is based on?

Is your preference different workbooks? (as opposed to different worksheets)

I've written something like this before, I'll try to find it ... but might be quicker to write again!


This post has been edited by strive4peace: Aug 6 2019, 10:25 AM

--------------------
have an awesome day,
crystal
Go to the top of the page
 
projecttoday
post Aug 6 2019, 10:15 AM
Post#6


UtterAccess VIP
Posts: 10,958
Joined: 10-February 04
From: South Charleston, WV


The Dbguy is right. I believe you can export a report to Excel but it's not going to produce multiple sheets like that. You will have to develop code.

--------------------
Robert Crouser
Go to the top of the page
 
bakersburg9
post Aug 6 2019, 10:34 AM
Post#7



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


Crystal,
Thanks so much for your help! I also want to incorporate the Project manager's name in a label, but that's just a "nice-to-have"

CODE
SELECT CostZeroNoInv.PDatInception, CostZeroNoInv.Cat, CostZeroNoInv.JobNum, CostZeroNoInv.TransactionType, CostZeroNoInv.AccountingDate, CostZeroNoInv.Vendor, CostZeroNoInv.Invoice, IIf(IsNull([VendorName]),[Description],[VendorName]) AS Descript, CostZeroNoInv.Amount
FROM CostZeroNoInv;


The label at the top of the report says: "Job Cost and Billing Detail - By Project Director" and the name of the field is "PDatInception" - obviously, I could just put a text box sourced by that field, but would be nice to concatenate it in the label ...

I also need to know how to insert a page break...

Steve
This post has been edited by bakersburg9: Aug 6 2019, 11:04 AM
Attached File(s)
Attached File  PageBreak.png ( 29.12K )Number of downloads: 8
 
Go to the top of the page
 
bakersburg9
post Aug 6 2019, 12:22 PM
Post#8



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


Found this little gem - creates separate workbooks for each tab

CODE
Sub SplitBook()

mypath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs Filename:=mypath & "\" & sht.Name & ".xls"
ActiveWorkbook.Close savechanges:=False
Next sht

End Sub
Go to the top of the page
 
theDBguy
post Aug 6 2019, 12:28 PM
Post#9


Access Wiki and Forums Moderator
Posts: 76,304
Joined: 19-June 07
From: SunnySandyEggo


Hi Steve. Glad to hear you're making good progress. Good luck!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
bakersburg9
post Aug 6 2019, 12:33 PM
Post#10



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


QUOTE
making good progress...
DbGuy -> thanks ! now I just need to come up with a way to export from Access to Excel and create the separate tabs/worksheets
Thanks!

Steve
Go to the top of the page
 
GroverParkGeorge
post Aug 6 2019, 12:38 PM
Post#11


UA Admin
Posts: 35,668
Joined: 20-June 02
From: Newcastle, WA


FWIW. I did something like this for a client a few years ago. It required looping a recordset and creating new worksheets in the target workbook for each "group".

Actually I used a template so that the "presentation" worksheet was preformatted as required, with links in cells in that presentation worksheet to the data pounded into the various worksheets. We copied the preformatted presentation worksheet each time a new group was encountered, along with the data worksheet. That allowed for multiple groups, but only the actual groups needed were ever added to any given workbook.

Even with all of that fussy stuff it goes pretty quickly.

I no longer support that client so I don't have the files available, but I wanted to assure you it can be done.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
bakersburg9
post Aug 6 2019, 01:17 PM
Post#12



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


Thanks, George ! cool.gif

Spit-balling here, but what about this ? Say I had a huge excel file like this, with the Project Manager's name at the top, in cell A1 - and column A would just have the PM's name, so I could do something like:

CODE
Sub DeleteAllRowsBelow()
'
    ActiveCell.Select
    Selection.End(xlDown).Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.EntireRow.Delete
    Range("A1").Select

    Sheets("Report Name_ ""rJob_Cost_And_Bil").Name = "ErikAllen"
        
End Sub


I think something along those lines might work - like you have your workbook with ALL data - make a copy on a new sheet, etc, delete everything that's not the project manager whose name is in A1 - I don't know if I would at that point rename the worksheet to the value that's in cell A1, or just write code to copy the data and paste it to a new worksheet - this would rename all worksheets to the value in cell A1 - I [bold]THINK[/bold]

Something like that - but maybe there's a problem with my methodology




Go to the top of the page
 
projecttoday
post Aug 6 2019, 03:27 PM
Post#13


UtterAccess VIP
Posts: 10,958
Joined: 10-February 04
From: South Charleston, WV


If you do a pivot table you only need one sheet of data. When the user clicks on a supervisor's data in the pivot a sheet appears with only that supervisor's data on it.

--------------------
Robert Crouser
Go to the top of the page
 
bakersburg9
post Aug 6 2019, 03:51 PM
Post#14



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


QUOTE
click on supervisor's data in the pivot a sheet appears with only that supervisor's data on it.
I remember now that capability, but for some reason, I can't get it to work...
Go to the top of the page
 
WildBird
post Aug 6 2019, 06:38 PM
Post#15


UtterAccess VIP
Posts: 3,603
Joined: 19-August 03
From: Auckland, Little Australia


Ceratinly doable.

Pivots are a way as well as individual worksheets.

Some places I have worked would mean you can only see your own data, so each report would be customised to each user.

You could follow others advice, and have a 'template' - a normal xlsm file, and this has a main page with hyperlinks to everyones worksheet (this is optional) and each 1 worksheet is preformatted, and then you have looping code and it copies the worksheet each time and populates. Each worksheet, I would have a pivot table filtered to the PM on that sheet.

Busy and hungover at the moment, but definitely doable what you are after.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
projecttoday
post Aug 6 2019, 07:00 PM
Post#16


UtterAccess VIP
Posts: 10,958
Joined: 10-February 04
From: South Charleston, WV


If this is for the supervisors individually then I think a separate spreadsheet for each one is good.

Otherwise give that pivot table another try.

--------------------
Robert Crouser
Go to the top of the page
 
MadPiet
post Aug 6 2019, 11:40 PM
Post#17



Posts: 3,285
Joined: 27-February 09



To add the page breaks between Groups, click on the group you want to put the page breaks between, and then show Properties. Force New Page:Before Section.

Of course that's meaningless if you're just exporting the stuff to Excel...
If you're doing that, you probably want a DISTINCT query to get the unique list of group values you're putting in each worksheet, and then you could maybe use CopyFromRecordset to transfer the records to Excel. (You'd basically pass the individual group value as a parameter to the second query (that returns all the records) and then use CopyFromRecordset from that to drop your data into the Excel template sheet. (You may have to put that on a hidden sheet or something, then delete it once the report is complete.. otherwise if you save over it, you won't be able to use it as a template anymore.)
Go to the top of the page
 
strive4peace
post Aug 7 2019, 01:08 AM
Post#18


strive4peace
Posts: 20,459
Joined: 10-January 04



hi Steve,

I wrote a function writes data to Excel from a query and breaks each sheet at whatever field you want to group by -- but it got kind of late so I'll test it more tomorrow. You can make a bunch of sheets in one workbook, or a bunch of files with one sheet each. It has more flexibility too, like you can define what you want on the first row of each sheet for a title (in addition to column headings) -- ordinarily I wouldn't have put something like that in but since you wanted it, I did.

As for page breaks ... there are PrintTitleRows and PrintTitleColumns ... and that may be all you need to be done automatically.

I was hoping to post it tonight, but it needs more testing first.



--------------------
have an awesome day,
crystal
Go to the top of the page
 
bakersburg9
post Aug 7 2019, 09:21 AM
Post#19



Posts: 5,558
Joined: 2-November 04
From: Downey, CA


Crystal, Thanks so much!!!! You do that just for little 'ol me ? - I'm touched !

WildBird, Robert, Mad, all of you - thanks all for your very valuable input - yeah, maybe an Excel MASTER could make it work with a pivot, but for this, doing it in MS Access just seems so much better... some of the 'raw' data needs to have some stuff not included at the detail level, and that's just not "smooth" with pivots

Crystal, I can't wait to see what you've come up with - thanks again !! cool.gif

Steve
Go to the top of the page
 
projecttoday
post Aug 7 2019, 09:27 AM
Post#20


UtterAccess VIP
Posts: 10,958
Joined: 10-February 04
From: South Charleston, WV


I hope you are aware that what I had in mind was an Excel pivot coded in Access. You can make Access code so that it produces a completed pivot table when the user clicks a button.

--------------------
Robert Crouser
Go to the top of the page
 
3 Pages V  1 2 3 >


Custom Search


RSSSearch   Top   Lo-Fi    18th September 2019 - 06:27 AM