Chart location and source...

[deko]

Is this the proper order for creating a shart, or am I missing some thing
else?

xlapp.Workbooks(strXlsFile).Charts.Add.Location where:=xlLocationAsObject,
Name:=strSheetName
xlapp.Workbooks(strXlsFile).Charts.Add.ChartType = xlLineMarkers
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(1, 3),
PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False
xlapp.Workbooks(strXlsFile).ActiveChart.ApplyDataLabels
Type:=xlDataLabelsShowValue

I am rather new to the world of Excel charting... any help is appreciated!

 

[Tushar Mehta]

Are you having problems? Or is this an academic question?

If the former, switch the sequence of the .ChartType and the
..SetSourceData statements.

A good -- though not perfect -- way of getting the necessary code is to
use XL's macro recorder. Turn it on (Tools | Macro > Record new
macro...), create the chart by hand, turn off the recorder, and switch
to the VBE.

Also, you may want to look at the VB(A) With statement as well as the
possibility of setting a variable of type excel.workbook to refer to
workbooks(strxlsfile).

--
Regards,

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

 

[deko]

Are you having problems? Or is this an academic question?

Yes, I am definitely having problems. In my haste to post I did not state
the problem - sorry.

If the former, switch the sequence of the .ChartType and the
.SetSourceData statements.

So the way to go is:

Add
Location
Source
Type
Other stuff

?

Here's the latest:

xlapp.Workbooks(strXlsFile).Charts.Add.Name = strSheetName & " Chart"
xlapp.Workbooks(strXlsFile).ActiveChart.SetSourceData Source:= _
xlapp.Workbooks(strXlsFile).Worksheets(j + 1).Cells(1, 3),
PlotBy:=xlColumns
xlapp.Workbooks(strXlsFile).ActiveChart.ChartType = xlLineMarkers
xlapp.Workbooks(strXlsFile).ActiveChart.HasLegend = False
xlapp.Workbooks(strXlsFile).ActiveChart.ApplyDataLabels
Type:=xlDataLabelsShowValue

I think I have problems with SetSourceData...

All this is contained in a loop, which provides the j value. I'm not sure
what to put in Cells() - but it should be the entire worksheet. There is no
data in the worksheet that is not part of the chart.

As for Location, this code creates a separate sheet for the chart. How
would I specify a worksheet (the active worksheet) as a location?

Thanks in advance.

 

[Tom Ogilvy]

the easiest approach is to go to excel, turn on the macro recorder
(tools=>Macro=>Record a new macro)

then create the chart manually.

Turn off the recorder and modify the code. It uses a lot of select and
selection which are not needed, but it should act as a general guide of the
order.

 

[Tom Ogilvy]

A good -- though not perfect -- way of getting the necessary code is to

use XL's macro recorder. Turn it on (Tools | Macro > Record new
macro...), create the chart by hand, turn off the recorder, and switch
to the VBE.

 

[Tushar Mehta]

I think I have problems with SetSourceData...

All this is contained in a loop, which provides the j value. I'm not sure
what to put in Cells() - but it should be the entire worksheet. There is no
data in the worksheet that is not part of the chart.

You really should check out the macro recorder. Of course, it will
only give you the final result for the chart. You may find you have to
adapt the code to use .Cells(1,3).CurrentRegion -- which will work as
long as your data are in a single contiguous area.

--
Regards,

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