export, graph, import

[ben]

I have a query setup that pulls selected values from a
table. I want to be able to export the data from the
query to excel to graph it, then import it back into
Access to use on a report.

What I need to know is...
1.)Where does the code need to be inserted into Access
(code added below...)
2.)Do I need to have a preformatted setup in excel to
export to?
3.)If I get the data graphed correctly, what needs to be
done to get it back to Access(if the code doesn't do it
for me)
4.)Let's assume that the code below takes care of Q 1-3
above...which lines need to be adjusted to fit my
particular needs?

I am pretty much a newbie to access, but i have some code
that is supposed to do this...it is given below:

I realize this is a lot to answer, but any information
will help me a great deal.

Finally, here's the code:
'===========================
Option Compare Database
Option Explicit

Const xlLineMarkers As Long = 65
Const xlColumns As Long = 2
Const xlLocationAsObject As Long = 2

Sub ChartExcel()
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim strQry As String
Dim ctrl As Control
Dim rpt As Report
Dim obj As ObjectFrame

strPath = "c:\"
strFile = "ExcelChart.xls"
strPathFile = strPath & strFile
strQry = "qryBilledAmount"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, strQry, _
strPath & strFile, True

Dim oXL As Object
Const ERR_APP_NOTRUNNING As Long = 429
On Error Resume Next
' Attempt to reference running instance of Excel
Set oXL = GetObject(, "Excel.Application")
' If Excel isn't running, create a new instance
If Err = ERR_APP_NOTRUNNING Then
Set oXL = CreateObject("Excel.Application")
End If
On Error GoTo 0
oXL.Application.Visible = True
oXL.Application.Workbooks.Open strPathFile
Dim wb As Object
Set wb = oXL.Workbooks(strFile)

Dim ws As Object
Set ws = wb.ActiveSheet
Dim ch As Object
Set ch = wb.Charts.Add
ch.ChartType = xlLineMarkers
ch.SetSourceData Source:=wb.Sheets(strQry) _
.Range("A1").currentregion, PlotBy:=xlColumns
ch.Location Where:=xlLocationAsObject, _
Name:=strQry
oXL.selection.Copy

DoCmd.OpenReport "rptExcelChart", acViewDesign
Set rpt = Reports!rptExcelChart
For Each ctrl In rpt.Section(acDetail).Controls
If ctrl.ControlType = acObjectFrame Then
Application.DeleteReportControl rpt.Name, ctrl.Name
End If
Next
DoCmd.RunCommand acCmdPaste

wb.Close SaveChanges:=False
Set ws = Nothing
Set wb = Nothing
DoCmd.SetWarnings True
DoCmd.OpenReport "rptExcelChart", acViewPreview

End Sub
'===========================

 

[Guest]

Ben,

I'd first suggest using the Graph object in Access to do your graphing
instead of automating Excel and trying to paste the graph.
Read the following and study the samples listed.

Steve

Some instructions for a using MsGraph with Access.

The Chart Wizard in Access leaves much to be desired,
ie: you're not limited to 6 Series.

Take a blank Form (even if you're making a graph for a Report)
and insert an unbound object frame.
With the form in design mode, Insert/Object (on the menus), select Ms Graph
(*Not* the Chart Wizard)

Click on the detail of your Form (ie: click off the graph)

Open the properties of the Graph object:
Format Tab: Size Mode - Zoom
Column Heads - Yes
Data Tab: Row Source Type - Table/Query
Row Source - Enter the name of your table, CrossTab Query, Select Query or
SQL
Enabled - Yes
Locked - No

View the form then go right back to design view (this will load
the graph datasheet with your data vs the sample data).
Don't worry about what the graph looks like just yet.

Then right click the Graph and select Chart Object ->Open (not Edit)

Make sure the Data/Series is in Rows or Columns - whatever is
appropriate for your data.

Now you have something to work with. Select the graph type
and any formatting you wish.

The Row Source can be a table or query name or a sql statement which
makes it very nice for dynamic data.

Using automation you can dynamically change the graph
to suit your needs, see the help files below.

If you're *really* making a report, now copy the Graph object
from the form to the report.

Download the Graph Sample MDB from MsKb Q186855, Grphsm97.Exe
and convert it to a newer version of Access if req'd.

Study the Graph Help file Graph9.hlp (A97), Graph9.chm (A2k), Graph10.chm
(AXp)
Study the Graph object Methods and Properties in the Access Object Browser

Also get the automation help files appropriate for you
for various help in coding:

Q302460 Office XP Products
Q260410 Office 2000 Products
Q167223 Office 97 Products