jwcolby
jwcolby at colbyconsulting.com
Thu Sep 16 06:51:52 CDT 2010
Think about putting the sub-reports on their own page. John W. Colby www.ColbyConsulting.com On 9/16/2010 1:40 AM, Rocky Smolin wrote: > Sounds like a plan. The sub-reports of course, are variable length if they > have any data at all. > > R > > > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan > Sent: Wednesday, September 15, 2010 9:59 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Creating a Spreadsheet from a Report > > There's really not that much code if you use a suitably formatted template > rather than trying to format ranges after you load the data. > > Create Names for cells at the appropriate places where row formatting > changes and GOTO the name begore inserting the data. > > For your detail rows, just have one formatted row with a named cell in the > first column, go to the name on the first record and then insert a new row > and populate it for each record as you step through the recordset. > > > -- > Stuart > > On 15 Sep 2010 at 21:37, Rocky Smolin wrote: > >> I think you're right. But it's a lot of nit-picky code to make a >> complex report look right in Excel. Just looking for a way to avoid >> work. >> >> R >> >> >> -----Original Message----- >> From: accessd-bounces at databaseadvisors.com >> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby >> Sent: Wednesday, September 15, 2010 6:12 PM To: Access Developers >> discussion and problem solving Subject: Re: [AccessD] Creating a >> Spreadsheet from a Report >> >> Excel automation is probably the way to go. You can export named >> ranges, run code out in the spreadsheet to perform formatting etc. >> >> John W. Colby >> www.ColbyConsulting.com >> >> On 9/15/2010 8:03 PM, Rocky Smolin wrote: >>> Thanks. Gotta think about this approach. >>> >>> Rocky >>> >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl >>> Collins Sent: Wednesday, September 15, 2010 4:55 PM To: Access >>> Developers discussion and problem solving Subject: Re: [AccessD] >>> Creating a Spreadsheet from a Report >>> >>> >>> ____________________________________________________________________ >>> __ ______ ___________ >>> >>> Note: This e-mail is subject to the disclaimer contained at the >>> bottom of this message. >>> ____________________________________________________________________ >>> __ ______ ___________ >>> >>> >>> >>> >>> Rocky, >>> >>> Easiest way is to use a template which has the report already laid >>> out on it. Then you need to have some hidden helper sheets >>> >>> One (or more sheets) you dump the access / database data into either >>> via a view / query / table. This will give you the data you need to >>> make the report. Often this can only be a few lines depending on >>> the complexity of the outcomes. >>> >>> Then you write code in the template that pull the data from the >>> hidden helper sheets and populates the report. >>> >>> Another more useful way is this. >>> >>> Create an Excel workbook with hooks into your database. This way >>> the user can update the report 'live' and any database changes are >>> immediately pulled into the XL report. You can even have the user >>> changes in Excel feed back into the database if you want, although I >>> rarely do this. Generally I have Excel as a 'read only' report. >>> >>> You can then write code in Excel to create a 'stand alone' report >>> for emailing etc. You want the users to do this, rather than >>> emailing the live version, which will fail once it cannot talk to >>> the database anymore. It also stops folks for stuffing up the data. >>> >>> If you need examples, email me offist and I will send you a couple >>> of workbooks I have that do exactly this. >>> >>> Regards >>> Darryl. >>> >>> >>> -----Original Message----- >>> From: accessd-bounces at databaseadvisors.com >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky >>> Smolin Sent: Thursday, 16 September 2010 7:10 AM To: 'Access >>> Developers discussion and problem solving' Subject: [AccessD] >>> Creating a Spreadsheet from a Report >>> >>> Dear List: >>> >>> I have a client who wants one of the reports in my app to be >>> exported as a spreadsheet. But nota flat file. She wants the >>> spreadsheet to look like the report - header line, details with >>> their own fields headings, plus two other sub reports. >>> >>> I've done this with other reports on a cell by cell basis where the >>> Transfer method won't work. Big PITA. Is there an easier way to do >>> this? >>> >>> >>> >>> MTIA >>> >>> Rocky >>> >>> >>> >>> ____________________________________________________________________ >>> __ ______ ___________ >>> >>> The information transmitted in this message and its attachments (if >>> any) is intended only for the person or entity to which it is >>> addressed. The message may contain confidential and/or privileged >>> material. Any review, retransmission, dissemination or other use of, >>> or taking of any action in reliance upon this information, by >>> persons or entities other than the intended recipient is prohibited. >>> >>> If you have received this in error, please contact the sender and >>> delete this e-mail and associated material from any computer. >>> >>> The intended recipient of this e-mail may only use, reproduce, >>> disclose or distribute the information contained in this e-mail and >>> any attached files, with the permission of the sender. >>> >>> This message has been scanned for viruses. >>> ____________________________________________________________________ >>> __ ______ ___________ >>> >>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >>> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> > >