Problem populating Excel Chart with Access VBA

[Andy Williams]

I am using Office 2000 and I have a form that contains a Microsoft Excel 2000
Chart. When the form is opened this chart is refreshed by a piece of VBA Code
which retrieves data from a database and puts it into the Sheet associated
with the Excel Chart and then formats the chart.

If I open the Form whilst Excel is closed everything works fine but if I
already have Excel Open then I get a File Not Found error in Excel which
causes the Form to hang until it is acknowledged.

How do I avoid this?

 

[Dale Fye]

Andy,

What does your code look like that opens Excel? Are you using GetObject or
CreateObject?

You should start out with GetObject and trap for the error that occurs if
Excel is already open.

If GetObject works, that means Excel is already Open, and you should check
to see whether the workbook you want to open is already open in that
instance, before trying to open it again. If it is not open, then open it.
If GetObject generates an error, then use CreateObject to Open an instance
of Excel and load the workbook you want to use.

If GetObject works, you might want to set a variable (bWasExcelOpen) to
True, so that when your code is done, you can leave Excel in the same state
it was in when you ran the code.

HTH
Dale

 

[Andy Williams]

Dale

Thanks for replying.

The Access Form contains an Unbound Microsoft Excel 2000 object which I've
called chtTimeLine.

When the form opens it calls the AddChart Subroutine the start of which I've
added below. I did look at using GetObject but couldn't work out where I
should put it.

Andy Williams

Private Sub AddChart()

Dim oSheet As Excel.Worksheet
Dim oChart As Excel.Chart
Dim rst As Recordset
Dim strSQL As String
Dim introw As Integer
Set oChart = chtTimeLine.Object.Charts(1)
Set oSheet = chtTimeLine.Object.Worksheets(1)
Dim Coll As Variant

On Error GoTo err_AddChart

chtTimeLine.Visible = False
oSheet.Cells.Clear
strSQL = "EXEC spKPIRepeatRefChart " & Me.txtRepeatRefUID

Set rst = New ADODB.Recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open strSQL
introw = 2

oSheet.Cells(1, 1).Value = "Date"
oSheet.Cells(1, 2).Value = "SP's"
Do While Not rst.EOF
oSheet.Cells(introw, 1).Value = rst.Fields(0)
oSheet.Cells(introw, 2).Value = rst.Fields(1)
rst.MoveNext
introw = introw + 1
Loop

.....More Code to format the Chart

 

[Andy Williams]

Ok, had another look at GetObject and I think I've sorted it out now. Thanks
for your help.

 

[Dale Fye]

Andy,

Another way to approach this is to use Excel's Data Query functionality.

1. If you go into Excel, select Data -> Import External Data -> New
Database Query

2. Select MS Access Database, then select the database. The query wizard
will display all of the tables and queries available in that database.
Select your query, or the specific columns you want from a query.
Eventually, the wizard will get you to a page with radio buttons, select
"Return data to Microsoft Office Excel".

3. It will then ask you where you want to put the data. Select the
worksheet and cell where you want the data to start. There is also a
properties button that will allow you to include or exclude field names and
other info.

Once you have this query build, then when you open the speadsheet via
automation, instead of having to write the data to the individual cells, all
you have to do is refresh the Excel Queries, which is significantly quicker
than the method you are using (especially if you have large quantities of
data).

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.