-----Original Message-----
Sure Ian.
The code resides in Access. Here is another example, run from a command in
Access, button which builds a recordset from a crosstab query, then opens a
spreadsheet in Excel and dumps the results into cells which are part of an
Excel named range. It is this range upon which the chart is founded:
Private Sub cmdSubmitData_Click()
Dim appXL As Excel.Application
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim wkb As Excel.Workbook
Dim wks As Excel.Worksheet
Dim strPath As String
On Error GoTo Error_Handler
' Open the current database and audiogram query
Set db = CurrentDb
Set rst = db.OpenRecordset("Select * from qryData2Excel Where ID =" &
Me.txtID, dbOpenSnapshot)
Set appXL = New Excel.Application
Set wkb = appXL.Workbooks.Open("C:\MyExcel.xls")
Set wks = wkb.Worksheets(1)
appXL.Visible = True
With wks
'Create the Column Headings
.Cells(1, 1).Value = "ID"
.Cells(1, 2).Value = "Name"
.Cells(1, 3).Value = "StaffName"
.Cells(1, 4).Value = "ColA"
.Cells(1, 5).Value = "ColB"
.Cells(1, 6).Value = "ColC"
.Cells(1, 7).Value = "ColD"
.Cells(1, 8).Value = "ColE"
.Cells(1, 9).Value = "ColF"
.Cells(1, 10).Value = "ColG"
.Cells(1, 11).Value = "ExamDate"
'Fill Values
.Cells(2, 1).Value = rst!ID
.Cells(2, 2).Value = rst!FullName
.Cells(2, 3).Value = rst!StaffName
.Cells(2, 4).Value = rst![Field1]
.Cells(2, 5).Value = rst![Field2]
.Cells(2, 6).Value = rst![Field3]
.Cells(2, 7).Value = rst![Field4]
.Cells(2, 8).Value = rst![Field5]
.Cells(2, 9).Value = rst![Field6]
.Cells(2, 10).Value = rst![Field7]
.Cells(2, 11).Value = rst![DateField]
End With
DoEvents
Exit_Here:
Set wkb = Nothing
Set appXL = Nothing
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Here
End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
Arvin
Thanks for the link. My programming skills are not as good
as they could be and so I'm not sure if the code you
describe sits in Access or Excel. Also can you give me
some pointers on how to take stored data in Access and
then move it into Excel and then graph it. I realsie this
may be too much to detail in this forum but a few pointers
would be really helpful.
Cheers,
Ian.
to
display it in an graph
and
.