auto plotting?

[jlnewbie]

I have a query that calculates values for plotting in both Access and Excel, however I've always copied these value to excel or "analyzed them with Excel" and plotted them. I've also finally figured out to have Access plot the values that I give it.
My questions is how can I have a form or report in Access automatically take my values and plot them, either from the query or from a table? Is this possible?

Thank you



JLopez
Lopez31@ATC-Enviro.com
Environmental Services @ Your Service

 

[kjv1611]

Can you provide a little more detail as to what your "plotting" is? Perhaps an example of what goes on in this process, currently manually?

 

[jlnewbie]

Presently I calculate two values, PercentWater vs DryDensity in Access and tranfers my values to excel to plot. The plot
yields a polymnomial curve that is given to the client.
ex.
PercentWater DryDensity
4.80 122.36
6.69 126.94
8.80 128.89
10.70 124.15

I'm hoping to have Access take these values and plot it for me based on each in individual curve. Maybe I don't know Access plotting enough?

Thank you again



JLopez
Lopez31@ATC-Enviro.com
Environmental Services @ Your Service

 

[kjv1611]

Can you just create a query to do what you are wanting to do, and then export that to Excel?

 

[jlnewbie]

yes this is what I do now but I was hoping that I can automate the exporting. Presently I manually export my values and plot them in an existing excel report. I somehow think that I'm missing a step in my exporting. Is it possible to link the query with the existing excel report without me forcing the data into it?



JLopez
Lopez31@ATC-Enviro.com
Environmental Services @ Your Service

 

[kjv1611]

Yes, I think I know what you would be best doing. For this, I am assuming that the layout of the spreadsheet is always the same.

You need to open Excel, and with this workbook or a new workbook that you will use for this purpose in the future, link to the database query. That will automatically refresh the data every time you open the workbook.

Instructions to follow...

 

[kjv1611]

In Excel (I'm using 2003, and I believe 2000 and 2002 both are the same for this - not sure about 97), go to:

Data Menu ->
Import External Data ->
Import Data

This will bring up an an explorer box asking you for what file to import from. Browse to the Access database, and then it will ask you to "select table", unless there is only one table. There, you need to select the query or whatever that you are normally using to populate Excel.

Once finished, the current data will be populated, but to make it a little more automated, you're not yet finished.

Next, right-click on the first cell of the section containing data, and choose "Data Range Properties".

This will bring up the box labeled "External Data Range Properties".

In this box, make sure that one of two checkboxes are checked, depending upon your needs:
Refresh every __ minutes
Refresh data on file open


I would suggest the second option (on file open), unless you have a reason for it to refresh on a particular interval. I would also suggest not checking the box "remove external data from worksheet before saving" underneath the second option, if you plan on saving the most current data each time.

Press "OK".

Close the file.

Reopen the same file (Excel Workbook). This time, when you open your workbook, you are presented with a message box with 2 options. The message box should say "Query Refresh". Your optinos are:
"Enable automatic refresh" or "Disable automatic refresh". I would just choose "Enable...", and from then on, it will refresh each time you open it.


Hopefully this is the sort of thing you're looking for. Of course, there are ways to automate things as well in Access, using VBA and/or Macros - whichever you're more comfortable with, but I think this would be the quickest/easiest method. VBA would arguably be the cleanest method, and you could then just create a new file (Excel workbook) each time if you wanted.