Jump to content
Sign in to follow this  

Bad data type but only sometimes?

Recommended Posts

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
            .Rows($x).Height = 18
        .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).Shape.TextFrame.TextRange.ParagraphFormat.Alignment = 2
        .Cell(2,1).Shape.TextFrame.TextRange.Text= $sOverallPercent
        .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
                .Cell(4,$l).Shape.TextFrame.MarginLeft = 1
            If $l = 2 then
                .Cell(4,$l).Shape.TextFrame.TextRange.Text = "Pre"
            If $l = 3 then
                .Cell(4,$l).Shape.TextFrame.TextRange.Text = "Curr"
            .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
        .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
        .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"

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

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

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:


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


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
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...