[AccessD] Creating a Spreadsheet from a Report

Stuart McLachlan stuart at lexacorp.com.pg
Wed Sep 15 23:58:43 CDT 2010


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
> 





More information about the AccessD mailing list