Testing if a Chart worksheet already exists in Excel 1

[davejackson]

Hi,

I am exporting a table from access to excel and then running a macro over the data to create a chart (I'm doing it in excel as it's a bubble chart and i've no idea how to code the macro over and access table).

I would like to do one of two things,

Either, check if the chart worksheet already exists and if it does, delete it (I don't know how to test if the object exists as (Dir(Chart) <> &quot;&quot doesn't seem to work,

Or ideally, create a new excel workbook and run the excel macro from access (which would mean I wouldn't have to store the macro in excel)

Any help would be brilliant as I'm having a horrible time of this already.

Thanks in advance,

David

 

[JVFriederick]

Is it necessary to create the chart with a macro?

I would suggest to create the chart in Excel using &quot;reasonable&quot; data, get the chart formatted as you like, and concetrate your Access to Excel macro efforts on placing the data in the appropriate place.

 

[Danyul]

David,

The code below can be run as a macro in Excel to determine if a chart exists. If it does it is then deleted (without the requirement for confirmation).

This will only work if your chart is saved in a seperate sheet and you know its name.

*****************************************************
Sub macro()
Dim reply As String

Application.DisplayAlerts = False

If ChartExists(&quot;Chart2&quot Then ActiveWorkbook.Sheets(&quot;Chart2&quot.Delete

Application.DisplayAlerts = False

End Sub

Private Function ChartExists(cname) As Boolean

Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(cname)
If Err = 0 Then ChartExists = True _
Else ChartExists = False

End Function

*********************************************************

For the code between 'sub macro()' and 'End Sub' substitute the name of your chart for Chart2 (but ensure its enclosed in &quot;&quot. This code calls the private function bit and passes it the name of your chart.

The Private function bit determines whether the chart named &quot;Chart2&quot; exists and passes back the answer. (ChartExists will be either true or false).

The 'if ChartExists do something' line is a shortened form of 'if ChartExists=True then do something'

I'm not sure how familiar you are with VBA so if this is a little muddy let me know!

Regards

Danyul

 

[davejackson]

JVFriederick - Unfortunately I have many charts being created off a lot of different data and I want the user to select which chart they want to see.

Danyul - That works brilliantly, I'll have to get into this monitoring for errors thing. You don't know if it is possible to store and run the macro from access do you? It would be nice as it wouldn't force the user to have a copy of the excel and access objects.

Thanks very much to you both,

Dave

 

[Danyul]

Dave,

It can certainly be done, we just need to iron out a few specifics.

The following code placed in an Access module will open a copy of excel and allow you to work with it.


'**********************************************************
Option Compare Database
Option Explicit
Public appExcel As Excel.Application
Public wkbkExcel As Excel.Workbook

'********************************************************
Sub main()

'the next 2 lines open a copy of Excel and make it visible

Set appExcel = CreateObject(&quot;Excel.Application&quot
appExcel.Visible = True

'then open at your workbook this uses the setwkbkExcel
'module that follows

setwkbkExcel (&quot;your path and filename here&quot

'now you need to modify the code from my last post slightly
'so I've included the modifications below

macro

End Sub

'**********************************************************
Sub macro()
Dim reply As String

appExcel.DisplayAlerts = False

If ChartExists(&quot;Chart2&quot Then wkbkExcel.Sheets(&quot;Chart2&quot.Delete

appExcel.DisplayAlerts = False

wkbkExcel.SaveAs &quot;path and new filename&quot;
wkbkExcel.Close

End Sub

'*******************************************************
Private Function ChartExists(cname) As Boolean

Dim x As Object
On Error Resume Next
Set x = wkbkExcel.Sheets(cname)
If Err = 0 Then ChartExists = True _
Else ChartExists = False

End Function

'*********************************************************
Sub setwkbkExcel(TargetFile)

On Error GoTo errwkbkExcel

Set wkbkExcel = appExcel.Workbooks.Open(TargetFile)
Exit Sub

errwkbkExcel:

MsgBox (&quot;The file &quot; & TargetFile & &quot; can not be found&quot, vbInformation, &quot;Error&quot;
End

End Sub

'**********************************************************
So this opens Excel, then opens your workbook, then checks for the &quot;Chart sheet&quot;,Deletes it if its there, then re-saves the work book.

In addition to this the only thing you *MUST* do for it to work is to create a reference to the Excel Object Model in Access. To do this: While in an Access Module goto >Tools>References> check the box next to 'Microsoft Excel 8.0 Library'

Hope this helps...

Danyul

 

[davejackson]

Danyul,

Thanks very much for your help again. I felt like I was getting the hang of this now. The only problem is that when I am running the macro in access to create my charts in excel, the line;

appExcel.ActiveChart.SeriesCollection(Count).XValues = &quot;=tblOutputofMetricData!R&quot; & Count & &quot;C34&quot;

receives the error: &quot;Application-defined or object-defined error.&quot; (as do all my other SeriesCollection lines)
Is this because I have just lifted the code straight from the excel? Is it phrased differently because i'm calling it from access? When I looked at the object browser I realised that XValues and my other SeriesCollection commands do not seem to be supported. Am I making any sense? If I am unable to format the graph from here I will have to return to putting the macro in an existing spreadsheet which would be a shame.

Thanks again for your earlier help

Dave

 

[Danyul]

Dave,

Now we get to some of the specifics we have to iron out!

There are a couple of problems with the above line of code.

1) The 'count' property is read only so you cant set it with an = sign.

What is the data held in the access tblOutputofMetricData ?

2) You can't reference an access table in quite the same way as an excel spreadsheet (ie on a row & column basis)

You need to use a query to pick out the table value that you want.

If you can give me a run down of how the data is arranged in access we can work out how to reference it and graph it.

Danyul

 

[davejackson]

Danyul,

The 'Count' above is a variable of mine as I am reading down the excel table until I run out of rows and each time specifying three fields for the graph. If there is another 'count' property I probably just picked a bad name.
The number of rows (and hence 'bubbles' on my graph) can change depending on how many check boxes the user selects from a continuous form.

The data held in tblOutputofMetricData is a table of calculated data and text fields. Based on this data I want to create a number of different graphs of different types depending on what the user selects. The different graphs use various combinations of different fields. Therefore I thought it might be easiest to output all the data to a spreadsheet and run macros that checked for how many rows of data there are before building the graphs. The downside of this is that I seem to need a permanent excel object to store the macros which is less desirable.

Even with a query I am finding it hard to create the graph. The data looks something like this:
Code Volume Value Staff
AA 200 2000 7
BB 300 2500 8
CC 250 3000 6
DD 400 5000 8

I would like the a bubble for each code, staff on the x-axis, volume on the y-axis and value represented on size of bubble.

Any help would be great Danyul as if I could do everything in access that would be brilliant but not to worry if this is getting too detailed as I can do what I want by having a seperate excel object.

Thanks again for your help.

Dave