Upgrading DB from Access 2003 to Access 2007

Question

All,

I have been tasked to upgrade an Access from 2003 to 2007. The purpose of the DB is to create a power point presentation of data that is automatically generated from an excel spreadsheet. I am able to import the data, but when trying to generate it into the power point, I receive an error. Looks like the error occurs when trying to take the data from the excel spreadsheet and putting in into a table on the power point presentation. This code was done by someone else and I am uncertain as to what is different between the 2 versions. Please HELP! All below is part of the code..Not too sure why it separates the way it does..sorry.Let me know if you need more info. See error and code below:

Error: run-time error -2147467259 (800004005) Method 'table' of object 'shape' failed

'Replace all tags with values from datarow
  With shape.Table

        .Cell(1, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(1, 1).shape.TextFrame.TextRange.Text, "<ID>", ID)
        .Cell(1, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(1, 3).shape.TextFrame.TextRange.Text, "<TITLE>", TITLE)
        .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<STATUS>", status)
        .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<OWNER>", OWNER)
' is part of WBS in cost changes (Perry Sedlar)            
        .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<REP>", REP)
        .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<ACTION>", ACTION)    
' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
        .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<MCE>", MCE)
        .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<ISSUE>", ISSUE)
' is part of WBS in cost changes (Perry Sedlar)          
        .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<ACTION>", ACTION) 
' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
        .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<PM>", PM)0 
' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
        .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<HA>", HA)
        .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<MP>", MP)
' is part of WBS in cost changes (Perry Sedlar)                      
        .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<PM>", PM) 
' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
        .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<LVL>", LVL) 
' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
        .Cell(2, 8).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 8).shape.TextFrame.TextRange.Text, "<PROB>", Prob)
        .Cell(2, 8).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 8).shape.TextFrame.TextRange.Text, "<EXP>", EXP)
' is part of WBS in cost changes (Perry Sedlar)                      
       .Cell(2, 8).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 8).shape.TextFrame.TextRange.Text, "<LVL>", LVL)
        .Cell(3, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(3, 1).shape.TextFrame.TextRange.Text, "<DESCRIPTION>", DESC)
        .Cell(4, 10).shape.TextFrame.TextRange.Text = Replace(.Cell(4, 10).shape.TextFrame.TextRange.Text, "<C>", C)
        .Cell(4, 10).shape.TextFrame.TextRange.Text = Replace(.Cell(4, 10).shape.TextFrame.TextRange.Text, "<S>", S)
        .Cell(4, 10).shape.TextFrame.TextRange.Text = Replace(.Cell(4, 10).shape.TextFrame.TextRange.Text, "<T>", T)
        .Cell(4, 10).shape.TextFrame.TextRange.Text = Replace(.Cell(4, 10).shape.TextFrame.TextRange.Text, "<O>", o)
        .Cell(2, 8).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 8).shape.TextFrame.TextRange.Text, "<ARO>", Trend_Arrow)



        If IssueFileRead = "N" Then ' Check to see if running Issue or Risk..  templates are now different (Perry Sedlar)

            ' This code is for Risk Slides (Perry Sedlar)
            .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<OCCUR>", OCCUR)
            .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<OCCUR_FACT>", OCCUR_FACT)
            .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<MIT>", MIT)
            .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<MIT_FACT>", MIT_FACT)
            .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<OPP>", OPP)
            .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<OPP_FACT>", OPP_FACT)
            .Cell(2, 7).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 7).shape.TextFrame.TextRange.Text, "<IPT>", IPT)
            .Cell(2, 7).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 7).shape.TextFrame.TextRange.Text, "<WBS>", WBS)
        Else

            ' This code if for Issue Slides (Perry Sedlar)
            .Cell(2, 1).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 1).shape.TextFrame.TextRange.Text, "<REP>", REP)
            .Cell(2, 3).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 3).shape.TextFrame.TextRange.Text, "<ACTION>", ACTION) ' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
            .Cell(2, 5).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 5).shape.TextFrame.TextRange.Text, "<PM>", PM) ' Shifted to the left after removing Gov't Rep column (Perry Sedlar)
            .Cell(2, 8).shape.TextFrame.TextRange.Text = Replace(.Cell(2, 8).shape.TextFrame.TextRange.Text, "<LVL>", LVL)
        End If

    End With
End Sub

score 0 · Answer 1 · answered Apr 27 '13 at 02:04

"Method 'table' of object 'shape' failed"

We'll probably need to see the code prior to where the error occurs. Have you set a reference to a shape and made sure that the shape is in fact a table?

Also, since Shape is a reserved word, I'd use something else as the variable name.

Dim oSh as Shape
Dim oTbl as Table
Set oSh = [fill in the blank here ... your call]
If oSh.HasTable Then
  Set oTbl = oSh.Table
  With oTbl
    ' [ do your stuff here ]
  End With
Else
  MsgBox "OOpsie.  Barking up the wrong shape here."
End If

If this needs to go from Excel to PPT, you have a budget of 70 bucks or so and want to get it done quickly and w/o going through Access, have a look at http://www.pptools.com/merge/

Full disclosure: it's a commercial product that I wrote/sell.
(Moderators: if mentioning this isn't acceptable, feel free to whack the last two paragraphs)

Sorry I am just getting back with you. I was in training all week last week. Is there a way that I can put all of the code in without having the code be too long for comment box? — user2324835, May 06 '13 at 12:45
Also, I tried your code and where it says oSh.HasTable, I recieve a compile error: method or data member not found — user2324835, May 06 '13 at 15:05

score 0 · Answer 2 · answered May 09 '13 at 14:59

0

The code libaries are different and there for some of the syntax is not allowed to be used. DAO are also different from 2003 to 2007. There will need to be a rewrite of the program for it to work properly. Thanks for the help.

answered May 09 '13 at 14:59

user2324835

1

Upgrading DB from Access 2003 to Access 2007

2 Answers2