Need help putting an Excel graph on a report

[pokdbz]

I am using a piece of code to generate an Excel sheet and graph. I have a
couple of questions. With the code below it actually opens Excel then you
have to manually save it. Is there a way to automate it so it does not open
Excel and automaticlly saves the chart with the updated information?

Second question. If you can do that how do you pull that updated chart into
a report in Access?

Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As Database
Dim rs As Recordset
Dim intLastCol As Integer
Const conMAX_ROWS = 20000
Const conSHT_NAME = "PHQChartQuery"
Const conWKB_NAME = "C:\TouchScreen\PHQChart.xls"
Set db = CurrentDb
Set objXL = New Excel.Application
Set rs = db.OpenRecordset("MakeTablePHQ", dbOpenSnapshot)

With objXL
.Visible = True
Set objWkb = .Workbooks.Open(conWKB_NAME)
On Error Resume Next
Set objSht = objWkb.Worksheets(conSHT_NAME)
If Not Err.Number = 0 Then
Set objSht = objWkb.Worksheets.Add
objSht.Name = conSHT_NAME
End If
Err.Clear
On Error GoTo 0
intLastCol = objSht.UsedRange.Columns.Count
With objSht
.Range(.Cells(1, 1), .Cells(conMAX_ROWS, _
intLastCol)).ClearContents
.Range(.Cells(1, 1), _
.Cells(1, rs.Fields.Count)).Font.Bold = True
.Range("A2").CopyFromRecordset rs
End With
End With
Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
Set rs = Nothing
Set db = Nothing

 

[Pieter Wijnen]

1) Use the .Save method of the Excel WorkBook
to do it silently Don't set the .Visible property to true
2) Create the chart in Access instead

HTH

Pieter

 

[pokdbz]

Thanks. The .Save method will that be in Excel or in Access?

I found that trying to create the chart in Access limiting so I went with
Excel. I could not setup the chart like I could using Excel. But is there a
way to just pull the chart from a saved file?

 

[Pieter Wijnen]

In Excel, As that's what you want to save <g>

Pieter