[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
>