none
Excel Charts are not being copied in a different language Office Platform, and giving error : The specified dimension is not valid for the current chart type RRS feed

  • Question

  • Hi All,

     

    Ok, the subject line of the message may not explain enough but I am stuck in a very weird Situation. We have developed an inHouse Addin, and has implemented that Addin in over 20 offices across World with different languages settings. We never had any problem and were enjoying a successful roll-out until we met Portuguese(Brazil). We are trying to copy a chart from our library to the active workbook.But whenever we are trying to copy the charts through code, we are getting the following error :

     

    The specified dimension is not valid for the current chart type.

     

    The simple line of code is giving that problem.

     

    chartShape.ChartArea.Copy()
    xlSheet.Paste(insertionPoint, False)
    

     


    To debug further, we debugged manually and used VBA to trace if it is a Dot Net bug, or an Excel Bug. And we used the following VBA code:

     

     

    Sub trial()
    
    
      Dim wkbS As Workbook
      Dim wkbT As Workbook<br/>  Set wkbS = Workbooks(2) ' source book 
      Set wkbT = Workbooks(1) ' target book
    
      Dim sht As Worksheet
      Dim sht1 As Worksheet
    
    
      Set sht = wkbS.Worksheets(1)
      Set sht1 = wkbT.Worksheets(1)
    
      sht.Shapes(1).Chart.ChartArea.Copy
      
      sht1.Paste sht1.Range("A1"), False
    
    End Sub
    
    


    We debugged the code by pressing F8 line by line in Excel VBA editor. And to our surprise, no error occurred in debug mode but the chart was not pasted in Target Workbook. The chart was copied in Clipboard correctly although, we confirmed it by pressing Ctrl+V and the chart was pasted in the workbook with no problems.

     

    But when we just ran the code without debugging, it threw us the error "The specified dimension is not valid for the current chart type." So this error didn't come in debug mode, and was suppressed for some reasons in debug mode. but in non debug mode, it threw error.

     

    Then we manually debugged the problem further, and found that the Portuguese environment is adding new sheets with xlSheetVeryHidden status, and conflicting with smooth execution of the code. As a result, we are getting the above error.

     

    I am not sure if we are going in the right direction but this problem is a kind of very weird which we never observed in most of the places in ROW. But it is coming in Brazil. Any idea?

     

    Thanks,

    Vikas

     

     


    http://excelnoob.blogspot.com
    Tuesday, August 30, 2011 11:09 AM

Answers

  • Hi Dot_Net_Noob,

     

    I found the Chart interface can’t convert from the shape interface. It seems the following snippet will approach your goal. Please try it.

            Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
            Dim wkbs As Excel.Workbook
            Dim wkbt As Excel.Workbook
            Try
                wkbs = Globals.ThisAddIn.Application.Workbooks(1)
                wkbt = Globals.ThisAddIn.Application.Workbooks.Add()
    
                Dim wkss As Excel.Worksheet
                Dim wkst As Excel.Worksheet
    
                wkss = wkbs.Worksheets(1)
                wkst = wkbt.Worksheets(1)
    
                Dim shape As Excel.Shape = wkss.Shapes.Item(1)
                wkst.Paste()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
        End Sub
    

    Have a good day,

     

    Tom

     

     

     


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 5, 2011 8:19 AM
    Moderator
  • Hi Tom!

    Your suggestion worked, but with a little tweak. The problem in copying shape was, when we copy and try to paste it in another worksheet, it was reporting a bug "Paste Method of Worksheet Failed". The problem was, copying a shape was creating a Lag and hence the paste command was being fired even before the shape was properly inserted in the Clipboard. So we have to find a workaround and push a "DoEvents" method and wait until it is properly copied. Then, we were able to paste the shape in destination sheet with no problems.

    Just thought, it might prove to be a help to others so shared it here.

     

    Thanks,
    Vikas


    My Blog My Profile
    • Marked as answer by Dot_Net_Noob Wednesday, September 14, 2011 8:35 AM
    Wednesday, September 14, 2011 8:35 AM

All replies

  • Hi Vikas,

     

    Thanks for posting in the MSDN Forum.

     

    Would you please clarify your Office version for further research?

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 1, 2011 12:55 AM
    Moderator
  • Ok, here are the environment settings:

    OS - Windows 7 64 Bit Portuguese Language

    Office 2007

    With Portuguese Keyboard setting and Primary editing language. office Menus are also set to display in Portuguese language, which is set through Microsoft Office Language Settings 2007 in the Start Menu.

    .Net Framework 3.5

    WPF + VB.net (3.5, that is Visual Studio 2008).

    Thanks,

    Vikas


    My Blog My Profile
    Thursday, September 1, 2011 7:53 AM
  • Hi Vikas,

     

    <<WPF + VB.Net(3.5,this is Visual Studio 2008)>>

    Do you mean you use WPF to handle you issue and you will use Microsoft.Office.Interop.Excel in you WPF application?

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 2, 2011 5:12 AM
    Moderator
  • Hi Tom!

    We have created .net AddIn using VB.net and the UIs have been developed in WPF. The issue which I raised was irrespective of the technology as the error is coming in core Interop method of pasting a Chart from One Workbook to another workbook when the language is set to Brazil.

    Hope it is clear!

    Thanks,
    Vikas


    My Blog My Profile
    Friday, September 2, 2011 6:31 AM
  • Hi Dot_Net_Noob,

     

    I found the Chart interface can’t convert from the shape interface. It seems the following snippet will approach your goal. Please try it.

            Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As Microsoft.Office.Tools.Ribbon.RibbonControlEventArgs) Handles Button1.Click
            Dim wkbs As Excel.Workbook
            Dim wkbt As Excel.Workbook
            Try
                wkbs = Globals.ThisAddIn.Application.Workbooks(1)
                wkbt = Globals.ThisAddIn.Application.Workbooks.Add()
    
                Dim wkss As Excel.Worksheet
                Dim wkst As Excel.Worksheet
    
                wkss = wkbs.Worksheets(1)
                wkst = wkbt.Worksheets(1)
    
                Dim shape As Excel.Shape = wkss.Shapes.Item(1)
                wkst.Paste()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
    
        End Sub
    

    Have a good day,

     

    Tom

     

     

     


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 5, 2011 8:19 AM
    Moderator
  • Hi Tom!

    Your suggestion worked, but with a little tweak. The problem in copying shape was, when we copy and try to paste it in another worksheet, it was reporting a bug "Paste Method of Worksheet Failed". The problem was, copying a shape was creating a Lag and hence the paste command was being fired even before the shape was properly inserted in the Clipboard. So we have to find a workaround and push a "DoEvents" method and wait until it is properly copied. Then, we were able to paste the shape in destination sheet with no problems.

    Just thought, it might prove to be a help to others so shared it here.

     

    Thanks,
    Vikas


    My Blog My Profile
    • Marked as answer by Dot_Net_Noob Wednesday, September 14, 2011 8:35 AM
    Wednesday, September 14, 2011 8:35 AM