LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 53
Default Chart location method fails using sheet name

When using automation from Access to create an embedded chart, the chart
appears on the first worksheet in the workbook rather than the sheet
specified with the Location method:

Dim xlapp As Excel.Application
Dim cht As Excel.Chart
Set xlapp = CreateObject("Excel.Application")
Set cht = xlapp.Workbooks(strXlsFile).Charts.Add
Set cht = cht.Location(Whe=xlLocationAsObject, Name:="Sheet3")

The chart appears embedded in Sheet1, not Sheet3.

I understand the Location method creates a new chart object (destroying any
reference to the original chart object), so the return value of the Location
method needs to be assigned to the cht object variable (which can then be
used for setting SourceData and other properties). Also, the Charts.Add
method by default creates the chart on a new sheet - so apparently the
Location method is successfully defining the chart as an embedded chart, but
why is the Name parameter not working?

I tried following a suggestion made earlier using the below code, but got
the same results:

Set cht = xlapp.Workbooks(strXlsFile).Charts.Add
For i = cht.SeriesCollection.Count To 1 Step -1
cht.SeriesCollection(i).Delete
Next i
cht.SetSourceData
Source:=xlapp.Workbooks(strXlsFile).Worksheets("Sh eet3").Range("A1:C" & _

xlapp.Workbooks(strXlsFile).Worksheets("Sheet3").R ange("C1").End(xlDown).Row
), _
PlotBy:=xlColumns
cht.ChartType = xlLineMarkersStacked
Set cht = cht.Location(Whe=xlLocationAsObject, Name:="Sheet3")

I'm wondering if the Location method parameter "Whe=xlLocationAsObject"
points to a worksheet object that is undefined since I am running this from
an Access module. But how to define it?

Has anyone run into this before?

Thanks in advance.


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Hyperlink or Other Method To Return To Previous Location (Sheet) Possible? Mhz New Users to Excel 4 August 1st 06 11:39 AM
Workbooks.open method fails JAT Excel Programming 3 January 24th 05 08:19 PM
Excel 2003 Workbooks.Open with CorruptLoad=xlRepairFile fails on Excel 5.0/95 file due to Chart, with Error 1004 Method 'Open' of object 'Workbooks' failed Frank Jones Excel Programming 2 June 15th 04 03:21 AM
Select method of Range fails J West Excel Programming 1 June 7th 04 02:41 PM
Copy method fails in IIS Domien Excel Programming 0 February 19th 04 02:46 PM


All times are GMT +1. The time now is 08:16 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"