Excel COM frustration

[Locodarwin]

Howdy!

I'm working with Excel COM objects. I can create charts without a hitch in most cases without having to create a separate VBA macro in Excel.

However, I've hit a snag that's killing my (at one time pretty awesome) project. Can anyone tell me why the first example below works, but the second one errors out with, "Requested action with object has failed?"

Local $FrequencyChart = $oExcel.Charts.Add

With $oExcel.Worksheets("Chart Data")
        $FrequencyChart.SeriesCollection.Add(.Range(.Cells($iRowOffset + 2, 2), .Cells($iRowOffset + 2, $iSetLength + 2)))
        $FrequencyChart.SeriesCollection.Add(.Range(.Cells($iRowOffset + 3, 2), .Cells($iRowOffset + 3, $iSetLength + 2)))
EndWithoÝ÷ Ù«­¢+Ù1½°ÀÌØíÉÅÕ¹å
¡ÉÐôÀÌØí½á°¹
¡ÉÑ̹()]¥Ñ ÀÌØí½á°¹]½É­Í¡ÑÌ ÅÕ½Ðí
¡ÉÐÑÅÕ½Ðì¤(ÀÌØíÉÅÕ¹å
¡ÉйMÉ¥Í
½±±Ñ¥½¸ Ĥ¹aY±ÕÌô¹I¹ ¹
±±Ì ÀÌØí¥I½Ý=ÍаȤ°¹
±±Ì ÀÌØí¥I½Ý=ÍаÀÌØí¥MÑ1¹Ñ ¬È¤¤(ÀÌØíÉÅÕ¹å
¡ÉйMÉ¥Í
½±±Ñ¥½¸ Ĥ¹Y±ÕÌô¹I¹ ¹
±±Ì ÀÌØí¥I½Ý=ÍЬİȤ°¹
±±Ì ÀÌØí¥I½Ý=ÍЬİÀÌØí¥MÑ1¹Ñ ¬È¤¤)¹]¥Ñ

I've made sure the ranges specified above are correct and have data, so that's a factor that can be eliminated from the problem. Also, the code works fine in VBA with only the removal of the "$" character from the variables and a space between "End" and "With."

So, what's going on, here? Am I referencing the object methods incorrectly somehow? The methods ".XValues" and ".Values" can work with an array as well as a range - am I stuck copying my range into an array, or will THAT even work? I've tried a few different ways of passing the data to the method, including these...

.XValues.Range(...)

.XValues(.Range(...))

...with no luck. The first way eliminates the error but fails to do anything. Help!

I'll paste the entire function this code is part of below.

-S

[Locodarwin]

Here's the whole function:

expandcollapse popup

Func CreatePeakGainChart($oExcel, $iStartFrequency, $iEndFrequency, $iSpecValue)
    
    Local $FrequencyChart = $oExcel.Charts.Add ;(Default, $oExcel.Sheets($oExcel.Sheets.Count), Default)
    
    Local $iRowOffset = ($iChartSize + 1) * ($iCurrentChart - 1) + 1
    
    If $iEndFrequency <= 3000 Then
        Local $sChannel = "802.11b"
    Else
        Local $sChannel = "802.11a"
    EndIf
    
    Local $iSetLength = ($iEndFrequency - $iStartFrequency) / $iFrequencyStep
    
    ; Chart parameters
    With $FrequencyChart
        .Name = "Peak Gain " & $sChannel
        .HasTitle = 1
        .ChartTitle.Text = "Peak Gain - " & $sChannel
        .ChartType = $xlLine
        .SetSourceData($oExcel.Worksheets("Chart Data").Range($oExcel.Worksheets("Chart Data").Cells($iRowOffset + 1, 2), $oExcel.Worksheets("Chart Data").Cells($iRowOffset + 1, $iSetLength + 2)))
    
        .PlotArea.Select
        $oExcel.Selection.ClearFormats
    EndWith
    
    With $FrequencyChart.Axes($xlValue)
        .MinimumScale = -20
        .MaximumScale = 10
        .MinorUnit = 1
        .MajorUnit = 2
        .CrossesAt = -20
        .ReversePlotOrder = 0
        .ScaleType = $xlLinear
        .DisplayUnit = $xlNone
        
        .HasMajorGridlines = True
        .MajorGridlines.Border.Color = (Dec("C0C0C0")) ; RGB(192, 192, 192)
        .MajorGridlines.Border.Weight = $xlThin
        .MajorGridlines.Border.LineStyle = $xlDot
    EndWith
    
    With $FrequencyChart.Axes($xlCategory)
        .TickLabelSpacing = 1
        .TickMarkSpacing = 10
        .TickLabels.Orientation = 90
        .HasMajorGridlines = True
        .MajorGridlines.Border.Color = (Dec("C0C0C0")) ;$oExcel.RGB(192, 192, 192)
        .MajorGridlines.Border.Weight = $xlThin
        .MajorGridlines.Border.LineStyle = $xlDot
    EndWith
    

    ; Chart data ------------------ where the problem starts
    
    With $oExcel.Worksheets("Chart Data")
        $FrequencyChart.SeriesCollection(1).XValues = .Range(.Cells($iRowOffset, 2), .Cells($iRowOffset, $iSetLength + 2))  ;  ----- this line returns the "Requested action with object failed" error.
        $FrequencyChart.SeriesCollection(1).Values = .Range(.Cells($iRowOffset + 1, 2), .Cells($iRowOffset + 1, $iSetLength + 2))
        $FrequencyChart.SeriesCollection.Add(.Range(.Cells($iRowOffset + 2, 2), .Cells($iRowOffset + 2, $iSetLength + 2)))
        $FrequencyChart.SeriesCollection.Add(.Range(.Cells($iRowOffset + 3, 2), .Cells($iRowOffset + 3, $iSetLength + 2)))
    EndWith
    
    With $FrequencyChart
        .SeriesCollection(1).Name = "Spec Line (" & $iSpecValue & " dBi)"
        .SeriesCollection(2).Name = "Horizontal Polarization"
        .SeriesCollection(3).Name = "Vertical Polarization"
    EndWith
    
    ; Chart formatting
    $FrequencyChart.SeriesCollection(1).Select
    With $oExcel.Selection.Border
        .Color = (Dec("0000FF"))
        .Weight = $xlThin
        .LineStyle = $xlContinuous
    EndWith
    
    $FrequencyChart.SeriesCollection(2).Select
    With $oExcel.Selection.Border
        .Color = (Dec("663399")) ; RGB(153, 51, 102)
        .Weight = $xlMedium
        .LineStyle = $xlContinuous
    EndWith
    
    $FrequencyChart.SeriesCollection(3).Select
    With $oExcel.Selection.Border
        .Color = (Dec("808000")) ; RGB(0, 128, 128)
        .Weight = $xlMedium
        .LineStyle = $xlContinuous
    EndWith
    
    With $FrequencyChart
        .Legend.Position = $xlLegendPositionLeft
        .Legend.Top = 2
        .Legend.Width = 150
        
        .PlotArea.Height = .ChartArea.Height * 0.85
        .PlotArea.Left = .ChartArea.Width / 2 - .PlotArea.Width / 2
        .PlotArea.Top = .ChartArea.Height / 2 - .PlotArea.Height / 2 + 24
        
        .ChartArea.Font.Size = 10
        .ChartTitle.Font.Size = 12
        .ChartTitle.Font.Bold = 1
    EndWith
    
    $FrequencyChart.Axes($xlCategory).TickLabelSpacing = 1
    
    $FrequencyChart.Deselect

EndFunc

Here's the problematic chunk of code from above in VBA format. It works.

'Chart data
    With Worksheets("Chart Data")
        FrequencyChart.SeriesCollection(1).XValues = .Range(.Cells(RowOffset, 2), .Cells(RowOffset, SetLength + 2))
        FrequencyChart.SeriesCollection(1).Values = .Range(.Cells(RowOffset + 1, 2), .Cells(RowOffset + 1, SetLength + 2))
        FrequencyChart.SeriesCollection.Add .Range(.Cells(RowOffset + 2, 2), .Cells(RowOffset + 2, SetLength + 2))
        FrequencyChart.SeriesCollection.Add .Range(.Cells(RowOffset + 3, 2), .Cells(RowOffset + 3, SetLength + 2))
    End With

[Locodarwin]

Incidentally, I can only call this function once; on a second call, I get another "Requested action with this object failed" at the following piece of code:

; Chart parameters
    With $FrequencyChart
        .Name = "Peak Gain " & $sChannel
        .HasTitle = 1 ; <------ RIGHT HERE is where the error occurs.
        .ChartTitle.Text = "Peak Gain - " & $sChannel
        .ChartType = $xlLine
        (....)

What's up with that??

-S

[Locodarwin]

*bump*

[Locodarwin]

*bump*

Does no one know the answer to this.

-S

[Jos]

It would help when you post something that we can test with.....The posted func won't just run like that .....

[bluebearr]

Well, I took a crack at this but couldn't discover a solution. I believe that it has to do with populating the SeriesCollection with some SeriesCollection.Add commands, but I couldn't get it to work.

However, I did convert the code into an Excel subroutine, so that others might try to troubleshoot and so that maybe you can take it to an Excel board and get help. Just create a worksheet named "Chart Data", create a new module in the Visual Basic editor, and then paste in the code below. When you get to the line in question, this is the error:

Run Time error '1004';

Unable to set the XValues property of the Series class.

BTW, I found that the line

.SeriesCollection(1).Name = "Spec Line (" & iSpecValue & " dBi)"

also returns a similar error ("Unable to set the Name property of the Series class.").

Hope this helps.

expandcollapse popup

Public Sub Test()
    ' iStartFrequency, iEndFrequency, iSpecValue
    iSpecValue = "300"
    
    Set FrequencyChart = Charts.Add '(Default, Sheets(Sheets.Count), Default)
   
    'iRowOffset = (iChartSize + 1) * (iCurrentChart - 1) + 1
    iRowOffset = 2
   
    'If iEndFrequency <= 3000 Then
        sChannel = "802.11b"
    'Else
    '   sChannel = "802.11a"
    'End If
   
'   iSetLength = (iEndFrequency - iStartFrequency) / iFrequencyStep
    iSetLength = 3
    
    ' -----This section added to prevent error when you create a chart with the same name
    For Each c In Charts
        If c.Name = "Peak Gain " & sChannel Then
            Application.DisplayAlerts = False
            Charts(c.Name).Delete
            Application.DisplayAlerts = True
        End If
    Next c
    ' ----- End section add.
   
    '; Chart parameters
    With FrequencyChart
        ' This code moved up here to prevent error with HasTitle if chart is created with no data
        ' selected on the worksheet
        .SetSourceData _
            Source:=Worksheets("Chart Data").Range(Worksheets("Chart Data").Cells(iRowOffset + 1, 2), _
            Worksheets("Chart Data").Cells(iRowOffset + 1, iSetLength + 2)), _
            PlotBy:=xlRows
        .Name = "Peak Gain " & sChannel
        .HasTitle = 1
        .ChartTitle.Text = "Peak Gain - " & sChannel
        .ChartType = xlLine
        .PlotArea.Select
        Selection.ClearFormats
    End With
   
    With FrequencyChart.Axes(xlValue)
        .MinimumScale = -20
        .MaximumScale = 10
        .MinorUnit = 1
        .MajorUnit = 2
        .CrossesAt = -20
        .ReversePlotOrder = 0
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
       
        .HasMajorGridlines = True
        .MajorGridlines.Border.Color = RGB(192, 192, 192)
        .MajorGridlines.Border.Weight = xlThin
        .MajorGridlines.Border.LineStyle = xlDot
    End With
   
    With FrequencyChart.Axes(xlCategory)
        .TickLabelSpacing = 1
        .TickMarkSpacing = 10
        .TickLabels.Orientation = 90
        .HasMajorGridlines = True
        .MajorGridlines.Border.Color = RGB(192, 192, 192)
        .MajorGridlines.Border.Weight = xlThin
        .MajorGridlines.Border.LineStyle = xlDot
    End With
    

    ' Chart data ------------------ where the problem starts
    With Worksheets("Chart Data")
        '  ----- next line returns the "Unable to set the XValues property of the Series class" error.
        FrequencyChart.SeriesCollection(1).XValues = .Range(.Cells(iRowOffset, 2), .Cells(iRowOffset, iSetLength + 2))  
        FrequencyChart.SeriesCollection(1).Values = .Range(.Cells(iRowOffset + 1, 2), .Cells(iRowOffset + 1, iSetLength + 2))
        FrequencyChart.SeriesCollection.Add (.Range(.Cells(iRowOffset + 2, 2), .Cells(iRowOffset + 2, iSetLength + 2)))
        FrequencyChart.SeriesCollection.Add (.Range(.Cells(iRowOffset + 3, 2), .Cells(iRowOffset + 3, iSetLength + 2)))
    End With
    
    With FrequencyChart
        ' ---------- Returns error "Unable to set the Name property of the Series class."
        .SeriesCollection(1).Name = "Spec Line (" & iSpecValue & " dBi)"
        .SeriesCollection(2).Name = "Horizontal Polarization"
        .SeriesCollection(3).Name = "Vertical Polarization"
    End With
   
    ' Chart formatting
    FrequencyChart.SeriesCollection(1).Select
    With Selection.Border
        .Color = (RGB(0, 0, 255))
        .Weight = xlThin
        .LineStyle = xlContinuous
    End With
   
    FrequencyChart.SeriesCollection(2).Select
    With Selection.Border
        .Color = RGB(153, 51, 102)
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
   
    FrequencyChart.SeriesCollection(3).Select
    With Selection.Border
        .Color = RGB(0, 128, 128)
        .Weight = xlMedium
        .LineStyle = xlContinuous
    End With
   
    With FrequencyChart
        .Legend.Position = xlLegendPositionLeft
        .Legend.Top = 2
        .Legend.Width = 150
       
        .PlotArea.Height = .ChartArea.Height * 0.85
        .PlotArea.Left = .ChartArea.Width / 2 - .PlotArea.Width / 2
        .PlotArea.Top = .ChartArea.Height / 2 - .PlotArea.Height / 2 + 24
       
        .ChartArea.Font.Size = 10
        .ChartTitle.Font.Size = 12
        .ChartTitle.Font.Bold = 1
    End With
   
    FrequencyChart.Axes(xlCategory).TickLabelSpacing = 1
   
    FrequencyChart.Deselect
End Sub

[bluebearr]

Maybe add this just before the problem section:

If $FrequencyChart.SeriesCollection.Count = 0 Then
        $FrequencyChart.SeriesCollection.NewSeries
    End If

[NvdM]

Hi Guys,

It's quite some time ago that this thread was placed, but I'd like to comment to it anyway.

I ran into the same problem regarding "SeriesCollection(1).XValues". After some invsetigation I noticed that the SeriesCollection(1).XValue can only be used after the relevant chart has been activated.

So

CODE

Local $FrequencyChart = $oExcel.Charts.Add

With $oExcel.Worksheets("Chart Data")

$FrequencyChart.SeriesCollection(1).XValues = .Range(.Cells($iRowOffset, 2), .Cells($iRowOffset, $iSetLength + 2))

$FrequencyChart.SeriesCollection(1).Values = .Range(.Cells($iRowOffset + 1, 2), .Cells($iRowOffset + 1, $iSetLength + 2))

EndWith

needs to be:

CODE

Local $FrequencyChart = $oExcel.Charts.Add

$oExcel.Worksheets("Chart Data").Activate

With $oExcel.ActiveChart

$FrequencyChart.SeriesCollection(1).XValues = .Range(.Cells($iRowOffset, 2), .Cells($iRowOffset, $iSetLength + 2))

$FrequencyChart.SeriesCollection(1).Values = .Range(.Cells($iRowOffset + 1, 2), .Cells($iRowOffset + 1, $iSetLength + 2))

EndWith

The problem is not an AutoIt limitation, but one from Excel. When you have an Excel macro created, in which you try to assign a value to the SeriesCollection(1).XValues, without having the relevant chart activated, Excel starts to complain.

I hope that my reply will still be usefull. At least I myself could have saved quite some time...

Cheers,

Nico.