How to embed chart in active worksheet with VBA?

[deko]

As far as I can tell, this *should* embed my chart in the Active worksheet:

xlapp.Workbooks(strXlsFile).ActiveChart.Location Where:= _
xlLocationAsObject, Name:=strSheetName

(where strSheetName is the name of the target worksheet)

But when I put this code in a loop, the chart is created in it's *own
sheet*:

xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row),
PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlCategory, xlPrimary).HasTitle
= False
xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlValue, xlPrimary).HasTitle =
False
xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False
xlapp.Workbooks(strXlsFile).ActiveChart.HasTitle = False

Am I missing something obvious, or do I need to do something different when
using automation?

Thanks in advance.

 

[Tushar Mehta]

(a) What code does XL generate when you use its macro recorder?

(b) If you already have a worksheet named strSheetName, how do you
expect
xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName
to work?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[deko]

(a) What code does XL generate when you use its macro recorder?

ActiveChart.Location Where:=xlLocationAsObject, Name:="2000_pr4a"

Of course, 2000_pr4a = strSheetName

(b) If you already have a worksheet named strSheetName, how do you
expect
xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName
to work?

Perhaps that's my problem. I've tried using this:

xlapp.Workbooks(strXlsFile).Charts.Add
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("C1").End(xlDown).Row),
PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.Location Where:=xlLocationAsObject,
Name:=strSheetName

But the charts still appear in their own sheet - Chart1, Chart2, Chart3,
etc.

 

[deko]

I've been experimenting with the macro recorder and that is a big help, to
be sure.
I just tried this again, and this time no chart appears in the workbook.

xlapp.Workbooks(strXlsFile).Charts.Add
xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _
xlapp.Workbooks(strXlsFile).Worksheets(j +
1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.Location Where:=xlLocationAsObject,
Name:=strSheetName

As far as I can tell, this is correct code. Please correct me if I'm wrong.

 

[Tushar Mehta]

Check the response to your other discussion on the same subject titled
'Chart location and source...' Among other tips, it contains
information about which two lines generated by the macro recorder have
to be swapped to make the code work.

As I continue to mention, the macro recorder is a good starting point.
However, it is not perfect.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[deko]

Check the response to your other discussion on the same subject titled

'Chart location and source...' Among other tips, it contains
information about which two lines generated by the macro recorder have
to be swapped to make the code work.

As I continue to mention, the macro recorder is a good starting point.
However, it is not perfect.

Apparently you don't know what you're talking about.

In the previous post the order was Add.Location, type, source. Switching
type and source would be Add.Location, source, type. The macro recorded
yields type, source, location.

If your previous bad advice had worked, I would not sill be posting about
this probelm.

 

[Tom Ogilvy]

I think you misjudge Tushar. Perhaps there is a misunderstanding in what he
has said added to some frustration on your part. But Tushar does know a lot
more about charting than I do - he is one of the mainstays over in
Excel.charting.

this worked for me:
Sub ABCD()
Dim j As Long, strXlsFile As String
Dim strSheetName As String, xlapp As Application
strXlsFile = "Book3"
strSheetName = "Sheet2"
j = 1
Set xlapp = Application
xlapp.Workbooks(strXlsFile).Charts.Add
xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _
xlapp.Workbooks(strXlsFile).Worksheets(j + _
1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.Location _
Where:=xlLocationAsObject, Name:=strSheetName

End Sub

You don't get a chart because your range doesn't point to any data - using
that j+1 to indicate the data sheet would be the source of the problem as my
best guess - it is pointing to the wrong sheet probably.

for example, when you get the blank chart, right click on your blank chart
and select source data, then click in the source box and then navigate to
the proper sheet and highlight your data area and hit enter and the chart
will appear.

Anyway, when the reference pointed to the data, the above code worked fine.

 

[deko]

this worked for me:

Sub ABCD()
Dim j As Long, strXlsFile As String
Dim strSheetName As String, xlapp As Application
strXlsFile = "Book3"
strSheetName = "Sheet2"
j = 1
Set xlapp = Application
xlapp.Workbooks(strXlsFile).Charts.Add
xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _
xlapp.Workbooks(strXlsFile).Worksheets(j + _
1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.Location _
Where:=xlLocationAsObject, Name:=strSheetName

End Sub

Hi and thanks for the reply. I tried the exact same thing but no luck. I'm
wondering if I need to exit the outer For Loop before making changes to the
chart. Perhaps I need to specify a cell in the worksheet location, in
addition the the worksheet name?

You don't get a chart because your range doesn't point to any data - using
that j+1 to indicate the data sheet would be the source of the problem as my
best guess - it is pointing to the wrong sheet probably.

I'll try a test with hardcoded parameters and see what happens.

for example, when you get the blank chart, right click on your blank chart
and select source data, then click in the source box and then navigate to
the proper sheet and highlight your data area and hit enter and the chart
will appear.

But I don't get a blank chart - I don't get any chart at all. The funny
thing is the code (the snippet above) does not throw any exceptions - it
seems to run fine. But when I open the workbook - no charts. Is there a
"visible" switch or something? I've repeated the chart creation process
with the macro recorder on and reviewed the code - this is what I get:

Sub Macro1()
Columns("A:C").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkersStacked
ActiveChart.SetSourceData Source:=Sheets("2000_pr1a").Range("A1:C45"), _

PlotBy:=xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="2000_pr1a"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
ActiveChart.HasLegend = False
ActiveChart.HasDataTable = False
End Sub

The order of events is Add, Type, Range, Location, Other Stuff - the same as
in the code snippet above. Nevertheless, as I mentioned, the result is no
chart at all.

Anyway, I'll keep playing around with it. Thanks again for the help.

 

[Tushar Mehta]

Hi Tom,

Thanks for the support.

The key to creating a chart is knowing that XL will make a 'best
guess' attempt at what the chart should contain based on the contents
of the current region. If it contains data XL will create a chart
based on its interpretation of that data. If it doesn't, XL will
create a chart with one series containing one data point that has a
value of 1.

What that means is that the Charts.Add() statement creates not an empty
chart but one with one or more series. Now, if one were to change the
chart type to something incompatible with the current data selection,
XL will raise an error.

So, to play safe what one needs to do is (1) delete all existing
series, (2) set the source range, and then (3) set the type.

Alternatively, create a chartobject directly in the worksheet. I don't
like this approach because one has to specify the dimensions of the
chartobject. Creating a chart and moving it to a worksheet causes XL
to set the chartobject's dimensions based on...experientially, I've
concluded it centers the chart and makes it 1/2 the height and 1/2 the
width of the visible worksheet.

Option Explicit

Sub ABCD()
Dim j As Long, strXlsFile As String
Dim strSheetName As String, xlapp As Application
Dim x As Chart, i As Integer
strXlsFile = "Book1"
strSheetName = "Sheet2"
j = 1
Set xlapp = Application
With xlapp.Workbooks(strXlsFile)
Set x = .Charts.Add()
For i = x.SeriesCollection.Count To 1 Step -1
x.SeriesCollection(i).Delete
Next i
x.SetSourceData _
Source:= _
.Worksheets(j + 1).Range("A1:C" & _
.Worksheets(j + 1).Range("C1").End(xlDown).Row), _
PlotBy:=xlColumns
x.ChartType = xlBubble3DEffect
Set x = x.Location(Where:=xlLocationAsObject, Name:=strSheetName)
End With
End Sub

In the above code, if the chart is created with an empty currentregion
(or a currentregion data structure that is incompatible with a bubble
chart), and if the charttype is set before the setsourcedata statement,
XL will raise an error.

Also note that set x=x.location() bit. When the chart location
changes, the object to which x was pointing disappeared. So, x must be
're-established.'

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[deko]

this worked for me:

Did you run this from an Access 2000 module?

Sub ABCD()
Dim j As Long, strXlsFile As String
Dim strSheetName As String, xlapp As Application
strXlsFile = "Book3"
strSheetName = "Sheet2"
j = 1
Set xlapp = Application
xlapp.Workbooks(strXlsFile).Charts.Add
xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlColumnClustered
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Range("A1:C" & _
xlapp.Workbooks(strXlsFile).Worksheets(j + _
1).Range("C1").End(xlDown).Row), PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.Location _
Where:=xlLocationAsObject, Name:=strSheetName

End Sub

Anyway, when the reference pointed to the data, the above code worked

fine.

hmm. still not working for me...