Office 2010 Incompatibility? Excel to Word Chart/Range Copy

[lehou1]

Hello all,
Fairly new dabbling at VBA, working with some existing documents programmed by another person that make extensive use of macros to automatically generate report elements.

We have just upgraded a couple of machines to Win7/Office2010 from Office2007, and am having some temperamental issues copying charts and ranges from Excel into Word on the 2010 machines, despite it all working perfectly on 2007 machines.

Receiving a few different errors at different times (not reliably one or the other)-
1004: Application-defined or object-defined error
or
1004: The specified dimension is not valid for the current chart type
or
4605: This method or property is not available because the Clipboard is empty or not valid

All link variously to the following sets of code, although there are multiple chart objects and ranges (eg BarChart, CompTable etc) that seemingly randomly become isolated.

    Sheets("SortedData").ChartObjects("Piechart" & i).Copy
    wrdApp.Selection.Paste

        Sheets("Tables & Barcharts").Select
        Range("ComparisonTable" & i).Select
        Selection.Copy
        wrdApp.Selection.Paste

i is a variable number based on the number of things we are reporting on. What is most puzzling is that the code will execute seemingly random iterations properly without having a problem before failing (eg, stops at i = 1, i = 5, i = 7 etc).

When the error occurs and I go back to excel, all the charts, ranges appear to be on the right sheets and with the right name/label.

So what I can reason out:
-The code isnt fundamentally wrong as it works flawlessly in 2007 and *can* execute some iterations in 2010 before having trouble.
-There may be issues with the copying or clipboard?
-I don't know enough!

The most annoying part is that it isn't coming up with a consistent problem (I can usually troubleshoot/workaround those) but varies without me changing anything. I can isolate the vague area but I'm afraid I don't know enough to figure it out, and several hours of research online hasn't helped.

Can someone at least suggest what I should be looking in to if they can't provide a solution!?

*I've looked at Reference Libraries but had eliminated them after trying a few different options - am I maybe missing something there?

 

[RSXchin]

Would you mind posting the code, and highlighting in red the sections that the code breaks? Welcome to the forums.

 

[lehou1]

It's rather a lot of code, but this is the relevant part of the code I think.

For i = 1 To NumComps
    With wrdApp
        With .Selection.Find        'Put in the Comp Name
            .Text = "CompName" & i & " "
            .Replacement.Text = CompNames(i)
            .Forward = True
            .Wrap = wdFindContinue
            .MatchWholeWord = False
            .Execute Replace:=wdReplaceAll
        End With
        With .Selection.Find        'Put in the Comp Def
            .Text = "CompDefinition" & i & " "
'            .Replacement.Text = CompDefs(i)
            .Forward = True
            .Wrap = wdFindContinue
            .MatchWholeWord = False
            .Execute
        End With
        .Selection.TypeText (CompDefs(i))
        With .Selection.Find        'Find the location of the charts
            .Text = "BarChart" & i & " "
            .Forward = True
            .Wrap = wdFindContinue
            .MatchWholeWord = False
            .Execute
        End With
    End With
    
    'paste the barchart
    Sheets("Tables & Barcharts").Select
    [COLOR=Red]Sheets("Tables & Barcharts").ChartObjects("BarChart" & i).Copy[/COLOR]
    [COLOR=DarkOrange]wrdApp.Selection.Paste[/COLOR]
    
    'paste the piechart
    Sheets("SortedData").Select
    [COLOR=Red]Sheets("SortedData").ChartObjects("Piechart" & i).Copy[/COLOR]
    [COLOR=DarkOrange]wrdApp.Selection.Paste[/COLOR]
    
    With wrdApp.Selection.Find     'find the location of the comp rating table
        .Text = "CompRatingTable" & i & " "
        .Forward = True
        .Wrap = wdFindContinue
        .MatchWholeWord = False
        .Execute
    End With
    
            
    'paste the comp rating table
    Sheets("Tables & Barcharts").Select
    Range("CompRatingTable" & i).Select
    Selection.Copy
 [COLOR=DarkOrange]   wrdApp.Selection.Paste[/COLOR]
    
    If CaseNum <> 8 Then       'as long as there is not just self-ratings
        With wrdApp.Selection.Find     'find the location of the comparison table
            .Text = "ComparisonTable" & i & " "
            .Forward = True
            .Wrap = wdFindContinue
            .MatchWholeWord = False
            .Execute
        End With
    
        'paste the comparison  rating table
        [COLOR=Red]Sheets("Tables & Barcharts").Range("ComparisonTable" & i).Copy[/COLOR]
        'Selection.Copy
        [COLOR=DarkOrange]wrdApp.Selection.Paste[/COLOR]
    Else 'just delete the text
        With wrdApp.Selection.Find     'find the location of the comparison table
            .Text = "ComparisonTable" & i & " "
            .Forward = True
            .Wrap = wdFindContinue
            .MatchWholeWord = False
            .Execute
        End With
        wrdApp.Selection.Delete
    End If
    
    Next i
    
    'now paste in the charts for the final item
    
    With wrdApp.Selection.Find
        .Text = "BarChart16"
        .Forward = True
        .Wrap = wdFindContinue
        .MatchWholeWord = False
        .Execute
    End With
    
    Sheets("SortedData").Select
    ActiveSheet.ChartObjects("PieChart16").Copy
    wrdApp.Selection.Paste
    Sheets("Tables & Barcharts").Select
    ActiveSheet.ChartObjects("BarChart16").Copy
    wrdApp.Selection.Paste
    
    
    'now paste the ratings overview table
    With wrdApp.Selection.Find     'find the location of the comparison table
        .Text = "RatingsOverviewTable"
        .Forward = True
        .Wrap = wdFindContinue
        .MatchWholeWord = False
        .Execute
    End With
    
    Sheets("Tables & Barcharts").Select
    Range("RatingsOverviewTable").Select
    Selection.Copy
    wrdApp.Selection.Paste
    
    
    
    wrdApp.Selection.EscapeKey
    With wrdApp.Selection.Find     'find the location of the comparison table
        .Text = "         "
        .Replacement.Text = ""
        .Forward = True
        .Wrap = wdFindContinue
        .MatchWholeWord = False
        .Execute Replace:=wdReplaceAll
    End With

Sometimes it stops on red lines (with the 1004 errors), sometimes orange (with 4605). Red-line errors much more common.

 

[lehou1]

Any more thoughts on this? Seems to be happening at similar times to other documents - again with inconsistent error messages and stop locations. Only problems are on Office 2010 machines, Office 2007 works fine.