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

xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe= _
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.SetSourceD ata 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(xlCat egory, xlPrimary).HasTitle
= False
xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlVal ue, 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.

(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

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

xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe= _
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.SetSourceD ata 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(xlCat egory, xlPrimary).HasTitle
= False
xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlVal ue, 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.

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

ActiveChart.Location Whe=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.SetSourceD ata 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 Whe=xlLocationAsObject,
Name:=strSheetName

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


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

xlapp.Workbooks(strXlsFile).ActiveChart.Location Whe= _
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.SetSourceD ata 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(xlCat egory,
xlPrimary).HasTitle
= False
xlapp.Workbooks(strXlsFile).ActiveChart.Axes(xlVal ue,
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.

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.SetSourceD ata 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 Whe=xlLocationAsObject,
Name:=strSheetName

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

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

In article ,
says...
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.SetSourceD ata 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 Whe=xlLocationAsObject,
Name:=strSheetName

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

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.

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.SetSourceD ata 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 _
Whe=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.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy


"deko" wrote in message
om...
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.