[AccessD] Excel Graphs from Access Data - Ideas, Please

Open Excel, click Data. Select Import External Data.  Select New Database
Query.  Select MS Access, then the table or query you want.  Go through the
wizard.  When you are done, the data is in excel.  Right click on the data,
and select 'Data Range Properties'.  This gives you all sorts of options,
including how the data is 'refreshed'.

Now, as far as how to work the parameters, well, that's up to you.  You
could put the parameters into a table, and have a form in Excel that allows
them to change the parameters (and then refresh the data).  It depends on
what the parameters are, or how they are going to change.  (ie, lets say you
some people want data for this month, some for next month.  Instead of
having them enter a month, just create two queries, one for this month, one
for next month.  Put the linked data either into different sheets, or
different workbooks.  It's going to depend on what parameters are really
necessary.  You could also have them run a make table query, and just link
the table.

Drew

-----Original Message-----
From: Tina Norris Fields [mailto:tinanfields at torchlake.com] 
Sent: Monday, September 25, 2006 10:23 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Excel Graphs from Access Data - Ideas, Please

Hi Drew,
Yes, maybe - if I do it that way, let me think how I'd go about it. 

I would create my Access queries with their date-range, wells, or 
wellfield parameters to get my data - create my Excel spreadsheets and 
graphs for the several different data combinations.  Then import into 
Excel from the updated Access queries as desired.  Yes?  The queries are 
all parameter queries, getting their parameters from combo boxes and 
calendar controls on a form - so, I'm thinking to use make-table queries 
to provide standard consistent data sources for the Excel spreadsheets.

Most of the users in this client office do know something about Excel, 
but virtually nothing about Access.  For that reason I am looking for 
ways to provide them with buttons and drop-down selection lists to 
generate the data set they want to see graphed, then a quick easy leap 
of some kind to the Excel spreadsheets and graphs of the selected data.  
On the Excel side I would like to provide a button to run the macro that 
generates the graph, or have the data come directly into named ranges 
that are already set up as the source for my graph.  I don't yet see how 
to import fresh data from the Access query-made-table into Excel without 
overwriting any named ranges I had established - and, thereby, losing 
the sources for my graph series.

I like the simplicity of your suggestion.  Would you share more of your 
thoughts, please?

Thank you,
Tina


DWUTKA at marlow.com wrote:
> Why automate at all?  Excel allows you to place external data on a
> spreadsheet, and you can have the sheet prompt to 'refresh' the data
> whenever excel is opened.  It's pretty straight forward, and wouldn't
> require using any code.  (Hey, wait, am I advocating a non-code approach?
> William may take away my 'Code Boy' title!)
>
> Drew
>
>
>   
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com