chart properties

[adaniele]

guys,
i create a form that create an xls with the form's values.

DoCmd.OutputTo acOutputForm, "ooooooo", acFormatXLS, "d:\ooooooo.xls", False

then i try to modify it properties and to create a chart.

Dim xlApp As Object
Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open("d:\ooooooo.xls").Sheets(1)

With xlApp
    .Application.Sheets("ooooooo").Select
    .Application.Range("A1").Select
    .Application.Selection.Font.Bold = True
    .Application.Range("B1").Select
    .Application.ActiveCell.FormulaR1C1 = "solo"
    .Application.Charts.Add
    .Application.ActiveChart.ChartType = xlPie
    .Application.ActiveChart.SetSourceData Source:=Sheets("0000000").Range("A2:B2"), PlotBy:=xlRows
    .Application.ActiveChart.Location Where:=xlLocationAsObject, name:="ooooooo"
    .Application.ActiveChart.ApplyDataLabels Type:=xlDataLabelsShowLabelAndPercent, LegendKey:=False, HasLeaderLines:=True
    .Application.Activeworkbook.Save
    .Application.Activeworkbook.Close
    .Application.Quit
End With

Set xlApp = Nothing
Set xlSheet = Nothing

vStatusBar = SysCmd(acSysCmdClearStatus)

this code, change the title of one value and create the cart in a new worksheets called CHART1.

issues:
1- I DONT want to create the chart in a new worksheet, i want it in the "ooooooo" sheet.

2- when i try to change the chart to be a pie, an error appears
"variable not defined"

any suggestion? thx, max.

 

[namliam]

xlPie is an excel variable, unless you link the Microsoft Excel reference you cannot use those... Find its value in the debug window of excel
?xlPie
5
And simply replace xlPie by that value (5)

The making of the graph in the same sheet is probably the same issue... as your using an xl variable there as well
?xlLocationAsObject
2
Replace it and it should work.... (or link the reference)

 

[adaniele]

xlPie is an excel variable, unless you link the Microsoft Excel reference you cannot use those... Find its value in the debug window of excel
?xlPie
5
And simply replace xlPie by that value (5)

The making of the graph in the same sheet is probably the same issue... as your using an xl variable there as well
?xlLocationAsObject
2
Replace it and it should work.... (or link the reference)

namlian; your advice was great and it works. however, 'cos i dont know how to read the debbuger info i cant continue changing the chart properties.
do u know where i can find a guide ?
thx max.

 

[namliam]

No guide,

Just go into excel, and type ?xlvalue in the debug/immediat window and it will return the value you need to put in Access.... Thats all there is to it ...

Edit Post# 2100