Bad data type but only sometimes?

[Jewtus]

This is my code to build and format a table in PowerPoint:

expandcollapse popup

$oPPTTable = $oSlide.Shapes.AddTable(9,3,10,70).Table
    With $oPPTTable
        .ApplyStyle("{5940675A-B579-460E-94D1-54222C63F5DA}",False); No Style, Table Grid... See https://msdn.microsoft.com/en-us/library/office/hh273476(v=office.14).aspx
        For $x=1 to 9
            For $y=1 to 3
                .Cell($x,$y).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = 2
                .Cell($x,$y).Shape.TextFrame.TextRange.Font.Name= "Arial"
                .Cell($x,$y).Shape.TextFrame.TextRange.Font.Size= 10
                .Cell($x,$y).Shape.TextFrame.VerticalAnchor = 1
            Next
            .Rows($x).Height = 18
        Next
        .Cell(1,1).Merge($oPPTTable.Cell(1,3))
        .Cell(1,1).Shape.TextFrame.TextRange.Font.Bold= True
        .Cell(1,1).Shape.TextFrame.TextRange.Text = "Overall % Comp:"
        .Cell(1,1).Shape.Fill.ForeColor.RGB = $RGB_Gray
        .Cell(2,1).Merge($oPPTTable.Cell(2,3))
        .Cell(2,1).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = 2
        .Cell(2,1).Shape.TextFrame.TextRange.Text= $sOverallPercent
        .Cell(3,1).Merge($oPPTTable.Cell(3,3))
        .Cell(3,1).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = 2
        .Cell(3,1).Shape.TextFrame.TextRange.Font.Bold= True
        .Cell(3,1).Shape.TextFrame.TextRange.Text = "Indicators"
        .Cell(3,1).Shape.Fill.ForeColor.RGB = $RGB_Gray
        For $l=1 to 3
            If $l = 1 then
                .Cell(4,$l).Shape.TextFrame.TextRange.Text = "Area"
                .Cell(4,$l).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = 1
                .Cell(4,$l).Shape.TextFrame.MarginLeft = 5
            Else
                .Cell(4,$l).Shape.TextFrame.MarginLeft = 1
            EndIf
            If $l = 2 then
                .Cell(4,$l).Shape.TextFrame.TextRange.Text = "Pre"
                FormatCell($sPrevOverall,.Cell(5,$l))
                FormatCell($sPrevSchedule,.Cell(6,$l))
                FormatCell($sPrevScope,.Cell(7,$l))
                FormatCell($sPrevResources,.Cell(8,$l))
                FormatCell($sPrevBudget,.Cell(9,$l))
            EndIf
            If $l = 3 then
                .Cell(4,$l).Shape.TextFrame.TextRange.Text = "Curr"
                FormatCell($sOverall,.Cell(5,$l))
                FormatCell($sSchedule,.Cell(6,$l))
                FormatCell($sScope,.Cell(7,$l))
                FormatCell($sResources,.Cell(8,$l))
                FormatCell($sBudget,.Cell(9,$l))
            EndIf
            .Cell(4,$l).Shape.TextFrame.TextRange.Font.Bold= True
            .Cell(4,$l).Shape.Fill.ForeColor.RGB = $RGB_Gray
            .Cell(4,$l).Shape.TextFrame.MarginRight = 1
        Next
        .Columns(1).Width = 50
        .Columns(2).Width = 30
        .Columns(3).Width = 30
        For $l=5 to 9
            .Cell($l,1).Shape.TextFrame.MarginLeft = 1
            .Cell($l,1).Shape.TextFrame.MarginRight = 1
            .Cell($l,1).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = 1
            .Cell($l,1).Shape.TextFrame.TextRange.Font.Name= "Arial"
            .Cell($l,1).Shape.TextFrame.TextRange.Font.Size= 9
        Next
        .Cell(5,1).Shape.TextFrame.TextRange.Text = "Overall"
        .Cell(6,1).Shape.TextFrame.TextRange.Text = "Overall2"
        .Cell(7,1).Shape.TextFrame.TextRange.Text = "Overall3"
        .Cell(8,1).Shape.TextFrame.TextRange.Text = "Overall4"
        .Cell(9,1).Shape.TextFrame.TextRange.Text = "Overall5"
    EndWith

On this line:

.Rows($x).Height = 18

 

I get an error after about 4 loops through this function that says Bad variable type. It always seems to happen at the same place, but I have a number of these lines elsewhere (to format other tables) and this seems to be the only one causing issues. Anyone have any ideas on how to fix/handle this event?

[Jewtus]

It also seems to happen on this as well:

.Columns(1).Width = 50
        .Columns(2).Width = 30
        .Columns(3).Width = 30

(i've been commenting them out just to see if the script will crash elsewhere)

[Jewtus]

I cleaned up all my code and isolated the creation of the base slide into a different function and use .Duplicate to create the new slide.... I'm getting this same error on the .duplicate command as well.  It seems to happen on the 3rd loop every single time. I've globalized my variables to see if I was maybe reusing a variable that was causing an issue but still no luck.

Full error:

Quote

err.description is:     
err.windescription:    Bad variable type.

err.number is:     80020008
err.lastdllerror is:     0
err.scriptline is:     2268
err.source is:     
err.helpfile is:     
err.helpcontext is: 

I'll create a full sample of my code for others to mess with to see if they can figure out what is going on.

[Jewtus]

Update:

I figured out the issue... the data sets I was using had a query that called a function. That function didn't have a declared obj for the connection, but I had used a connection object on starting the script so it was able to recycle that obj for the connection. Once I added the create connection obj into the function, the problem seemed to go away.

Edited June 23, 2016 by Jewtus
fixed the issue