Jump to content
Jewtus

Bad data type but only sometimes?

Recommended Posts

Jewtus

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

$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?

Share this post


Link to post
Share on other sites
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)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by Jewtus
fixed the issue

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×