[AccessD] Access data to Excel Chart

Ok, then you need solution three. I do similar things where I create
multiple budget books at a time selected from a menu. How much of the code
do you have written? Is the real question how best to structure the process
or is it how to write the code to create multiple workbooks from different
recordsets or is it you have an Excel instance stuck in memory that is
giving your code problems?
Jim Hale

-----Original Message-----
From: Lonnie Johnson [mailto:prodevmg at yahoo.com]
Sent: Wednesday, November 09, 2005 10:29 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access data to Excel Chart


Thanks,
 
The actual application is a process where the user can select one or more
clients from a listbox. An excel workbook would then be created for EACH
client with four different graphs in each workbook.

"Hale, Jim" <Jim.Hale at fleetpride.com> wrote:
Lonnie,
The fastest, easiest way to do this is from within Excel. When you create
the pivot table when it asks for the data source select "external data
source." You can then navigate to your mdb and select the query you want to
run. When it comes time to update the data simply hit refresh from the data
menu in Excel. This solution means the user runs it from Excel and doesn't
need to know anything about Access. If it is used on multiple machines the
mdb has to be available to them and the connection to the mdb should use the
network path. Also, if your query uses parameters or function calls it will
not work. The way around this is to use sub queries to provide the criteria.

The second easiest way is to create your pivot table and chart and then use
the "analyze it with excel" selection on the toolbar to dump the results to
Excel. You then cut and paste into your spreadsheet and refresh the pivot
table from the data menu. This is fast but manual.

The third way is to fully automate the process. To do this create the pivot
table and chart and store it as an Excel template without data. Then open
the Excel sheet using automation and paste the query results into the
spreadsheet. This is the best solution for the long term but of course
requires development time. Assuming you have already opened an Excel
instance AppXcel the following function can be used to paste recordsets into
the named worksheet: (this is from a class I created to move data to Excel)

Jim Hale

Public Function PasteRecSetExcel(strSheetName As String, _
rstData As Recordset, Optional blPaste As Boolean = False, Optional
strWSRange As String) As Boolean


***********************************************************************
The information transmitted is intended solely for the individual or
entity to which it is addressed and may contain confidential and/or
privileged material. Any review, retransmission, dissemination or
other use of or taking action in reliance upon this information by
persons or entities other than the intended recipient is prohibited.
If you have received this email in error please contact the sender and
delete the material from any computer. As a recipient of this email,
you are responsible for screening its contents and the contents of any
attachments for the presence of viruses. No liability is accepted for
any damages caused by any virus transmitted by this email.