Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart location method fails using sheet name
mystery solved!
Dim objSheet As Object Dim objChart As Object For p = LBound(sn) To UBound(sn) 'array of sheet names Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(p)) Set objChart = objSheet.ChartObjects.Add(300, 20, 500, 300).Chart objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _ Worksheets(sn(p)).Range("A1:C" & xlapp.Workbooks _ (strXlsFile).Worksheets(sn(p)).Range("C1").End(xlD own).Row), _ PlotBy:=xlColumns objChart.ChartType = xlLineMarkersStacked objChart.Axes(xlCategory, xlPrimary).HasTitle = False objChart.Axes(xlValue, xlPrimary).HasTitle = False objChart.HasLegend = False objChart.ChartTitle.Text = sn(p) objChart.HasTitle = True Next p The ChartObjects collection holds embedded Chart objects, which are accessed through the ChartObject object; the Chart property of the ChartObject object is used to access the actual chart. When using automation, the Add method of the ChartObjects object will add an embedded chart, rather than the Add method of the Charts object. There's an example he http://msdn.microsoft.com/library/de...latecharts.asp but it's not very well documented. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart location method fails using sheet name
mystery solved!
Dim objSheet As Object Dim objChart As Object For p = LBound(sn) To UBound(sn) 'array of sheet names Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(p)) Set objChart = objSheet.ChartObjects.Add(300, 20, 500, 300).Chart objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _ Worksheets(sn(p)).Range("A1:C" & xlapp.Workbooks _ (strXlsFile).Worksheets(sn(p)).Range("C1").End(xlD own).Row), _ PlotBy:=xlColumns objChart.ChartType = xlLineMarkersStacked objChart.Axes(xlCategory, xlPrimary).HasTitle = False objChart.Axes(xlValue, xlPrimary).HasTitle = False objChart.HasLegend = False objChart.ChartTitle.Text = sn(p) objChart.HasTitle = True Next p The ChartObjects collection holds embedded Chart objects, which are accessed through the ChartObject object; the Chart property of the ChartObject object is used to access the actual chart. When using automation, the Add method of the ChartObjects object will add an embedded chart, rather than the Add method of the Charts object. There's an example he http://msdn.microsoft.com/library/de...latecharts.asp but it's not very well documented. One issue I've run into now, after implementing this solution, is that Excel does not quit after the function is complete - even after: Set xlapp = Nothing xlapp.Quit Thinking that the problem was the old "global reference to the automated application" issue (helpfully described by Mr. Mehta at http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/) I tried this: For p = LBound(sn) To UBound(sn) With xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Char tObjects.Add _ (Left:=300, Top:=20, Width:=500, Height:=300).Chart .ChartType = xlLineMarkersStacked .SetSourceData Source:=xlapp.Workbooks(strXlsFile).Worksheets _ (sn(p)).Range("A1:C" & xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Rang e _ ("C1").End(xlDown).Row), PlotBy:=xlColumns .Axes(xlValue, xlPrimary).HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .HasLegend = False .ChartTitle.Text = sn(p) .ChartTitle.Font.Bold = True .HasTitle = True End With Next p But the problem remains. Apparently Access needs a diaper on this one... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Chart location method fails using sheet name
One issue I've run into now, after implementing this solution, is that
Excel does not quit after the function is complete - even after: Set xlapp = Nothing xlapp.Quit Looking at the first example, before releasing xlApp first release the others in reverse order: Set objChart = nothing Set objSheet = nothing xlapp.Quit Set xlapp = Nothing Not sure about the second example. Regards, Peter T "deko" wrote in message ... mystery solved! Dim objSheet As Object Dim objChart As Object For p = LBound(sn) To UBound(sn) 'array of sheet names Set objSheet = xlapp.Workbooks(strXlsFile).Worksheets(sn(p)) Set objChart = objSheet.ChartObjects.Add(300, 20, 500, 300).Chart objChart.SetSourceData Source:=xlapp.Workbooks(strXlsFile). _ Worksheets(sn(p)).Range("A1:C" & xlapp.Workbooks _ (strXlsFile).Worksheets(sn(p)).Range("C1").End(xlD own).Row), _ PlotBy:=xlColumns objChart.ChartType = xlLineMarkersStacked objChart.Axes(xlCategory, xlPrimary).HasTitle = False objChart.Axes(xlValue, xlPrimary).HasTitle = False objChart.HasLegend = False objChart.ChartTitle.Text = sn(p) objChart.HasTitle = True Next p The ChartObjects collection holds embedded Chart objects, which are accessed through the ChartObject object; the Chart property of the ChartObject object is used to access the actual chart. When using automation, the Add method of the ChartObjects object will add an embedded chart, rather than the Add method of the Charts object. There's an example he http://msdn.microsoft.com/library/de...us/odc_xl2003_ ta/html/odc_XL_manipulatecharts.asp but it's not very well documented. One issue I've run into now, after implementing this solution, is that Excel does not quit after the function is complete - even after: Set xlapp = Nothing xlapp.Quit Thinking that the problem was the old "global reference to the automated application" issue (helpfully described by Mr. Mehta at http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/) I tried this: For p = LBound(sn) To UBound(sn) With xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Char tObjects.Add _ (Left:=300, Top:=20, Width:=500, Height:=300).Chart .ChartType = xlLineMarkersStacked .SetSourceData Source:=xlapp.Workbooks(strXlsFile).Worksheets _ (sn(p)).Range("A1:C" & xlapp.Workbooks(strXlsFile).Worksheets(sn(p)).Rang e _ ("C1").End(xlDown).Row), PlotBy:=xlColumns .Axes(xlValue, xlPrimary).HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .HasLegend = False .ChartTitle.Text = sn(p) .ChartTitle.Font.Bold = True .HasTitle = True End With Next p But the problem remains. Apparently Access needs a diaper on this one... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hyperlink or Other Method To Return To Previous Location (Sheet) Possible? | New Users to Excel | |||
Workbooks.open method fails | Excel Programming | |||
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 | Excel Programming | |||
Select method of Range fails | Excel Programming | |||
Copy method fails in IIS | Excel Programming |