locked
Excel Interop COM Exception: The number must be between 10 and 400

    Dotaz

  • I have some code that has stopped working as we have progressed through different versions of the Excel Interop Libraries.  We're now running Microsoft Excel 14.0 Object Library Version 1.7.0.0

    We create a lot of Excel worksheets and populate the data via a Visual Basic .NET using Visual Studio .NET

    The code begins by starting Excel (Just about everyone has Excel 2010 now, although some are still on 2003 and 2007). I'm using the 2010 version when I get the Exception.  Here is the code:

    xlApp = New Excel.ApplicationClass
    xlWorkBook = xlApp.Workbooks.Add
    
    'Delete Extra Worksheets
    For Each sh In xlWorkBook.Worksheets
       If sh.Index > 1 Then sh.Delete()
    Next
    
    'Rename Worksheet 1 to Kit Cost
    xlWorkSheet = xlWorkBook.Worksheets(1)
    xlWorkSheet.Name = "New Worksheet Name"
    
    'Populates Header Information
    RptHeader = vbCrLf & "&""Arial,Bold""&12&UNew Worksheet Name&U" & Chr(10)
    RptHeader = RptHeader & "&""Arial,Regular""&10Timeframe: " & Me.dtpStartDate.Text & " - " & Me.dtpEndDate.Text
    
    HR = 1
    
    'Sets Page Margins & Paper Orientation
    With xlWorkSheet.PageSetup
      .CenterHorizontally = True
      .BottomMargin = 46.8
      .TopMargin = 75
      .FooterMargin = 0
      .HeaderMargin = 0
      .FitToPagesWide = 1
      .FitToPagesTall = 35
      .Zoom = False
      .PrintTitleRows = "$" & HR & ":$" & HR
      .Orientation = Excel.XlPageOrientation.xlLandscape
      .CenterHeader = RptHeader
      .LeftHeader = "&G"
      .LeftHeaderPicture.Filename = Logo
      .RightHeader = "Date: &D"
      .LeftFooter = "Creator: " & UserName
      .RightFooter = "Page &P of &N"
    End With
    

    Everything is fine until the first With clause statement. The line, xlWorkSheet.PageSetup.CenterHorizontally = True, causes a COM Exception with the message "The number must be between 10 and 400. Try again by entering a number in this range" Troubleshooting tips: Check the ErrorCode property of the exception to determine the HRESULT returned by the COM object.

    The ErrorCode reported is -2146827284

    I thought rearranging the order of the statements in the With Clause might make a difference, but I haven't been able to figure out what it's complaining about.  Can anyone point me in the right direction?

    Thanks!

    Visual Basic 2005


    Alan Edwards

    24. února 2012 16:03

Odpovědi

  • Hi Alan,

    Welcome to the MSDN Forum.

    Where do you run your code? In VS 2010?

    I tried your code, it didn't work for me:

    >> xlApp = New Excel.ApplicationClass

    Based on this article: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.applicationclass(v=office.14).aspx 

    This API supports the .NET Framework infrastructure and is notintended to be used directly from your code. 

    So your code shouldn't be complied successfully. So do you make your own "Excel.ApplicationClass"? And based on this, the property CenterHorizontally may not this one: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pagesetup.centerhorizontally(v=office.14).aspx, and it failed. So this should be the root cause.

    Here is my whole test code:

    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            test()
        End Sub
    
        Sub test()
            Dim xlApp = New Excel.Application
            Dim xlWorkBook = xlApp.Workbooks.Add
    
            'Delete Extra Worksheets
            For Each sh In xlWorkBook.Worksheets
                If sh.Index > 1 Then sh.Delete()
            Next
    
            'Rename Worksheet 1 to Kit Cost
            Dim xlWorkSheet = xlWorkBook.Worksheets(1)
            xlWorkSheet.Name = "New Worksheet Name"
    
            'Populates Header Information
            'RptHeader = vbCrLf & "&""Arial,Bold""&12&UNew Worksheet Name&U" & Chr(10)
            'RptHeader = RptHeader & "&""Arial,Regular""&10Timeframe: " & Me.dtpStartDate.Text & " - " & Me.dtpEndDate.Text
    
            ' HR = 1
    
            'Sets Page Margins & Paper Orientation
            With xlWorkSheet.PageSetup
                .CenterHorizontally = True
                .BottomMargin = 46.8
                .TopMargin = 75
                .FooterMargin = 0
                .HeaderMargin = 0
                .FitToPagesWide = 1
                .FitToPagesTall = 35
                .Zoom = False
                '.PrintTitleRows = "$" & HR & ":$" & HR
                .Orientation = Excel.XlPageOrientation.xlLandscape
                '.CenterHeader = RptHeader
                .LeftHeader = "&G"
                '.LeftHeaderPicture.Filename = Logo
                .RightHeader = "Date: &D"
                ' .LeftFooter = "Creator: " & UserName
                .RightFooter = "Page &P of &N"
            End With
        End Sub
    End Class
    

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    27. února 2012 10:10
    Moderátor

Všechny reakce

  • Hi Alan,

    Welcome to the MSDN Forum.

    Where do you run your code? In VS 2010?

    I tried your code, it didn't work for me:

    >> xlApp = New Excel.ApplicationClass

    Based on this article: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.applicationclass(v=office.14).aspx 

    This API supports the .NET Framework infrastructure and is notintended to be used directly from your code. 

    So your code shouldn't be complied successfully. So do you make your own "Excel.ApplicationClass"? And based on this, the property CenterHorizontally may not this one: http://msdn.microsoft.com/en-us/library/microsoft.office.interop.excel.pagesetup.centerhorizontally(v=office.14).aspx, and it failed. So this should be the root cause.

    Here is my whole test code:

    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
        Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
            test()
        End Sub
    
        Sub test()
            Dim xlApp = New Excel.Application
            Dim xlWorkBook = xlApp.Workbooks.Add
    
            'Delete Extra Worksheets
            For Each sh In xlWorkBook.Worksheets
                If sh.Index > 1 Then sh.Delete()
            Next
    
            'Rename Worksheet 1 to Kit Cost
            Dim xlWorkSheet = xlWorkBook.Worksheets(1)
            xlWorkSheet.Name = "New Worksheet Name"
    
            'Populates Header Information
            'RptHeader = vbCrLf & "&""Arial,Bold""&12&UNew Worksheet Name&U" & Chr(10)
            'RptHeader = RptHeader & "&""Arial,Regular""&10Timeframe: " & Me.dtpStartDate.Text & " - " & Me.dtpEndDate.Text
    
            ' HR = 1
    
            'Sets Page Margins & Paper Orientation
            With xlWorkSheet.PageSetup
                .CenterHorizontally = True
                .BottomMargin = 46.8
                .TopMargin = 75
                .FooterMargin = 0
                .HeaderMargin = 0
                .FitToPagesWide = 1
                .FitToPagesTall = 35
                .Zoom = False
                '.PrintTitleRows = "$" & HR & ":$" & HR
                .Orientation = Excel.XlPageOrientation.xlLandscape
                '.CenterHeader = RptHeader
                .LeftHeader = "&G"
                '.LeftHeaderPicture.Filename = Logo
                .RightHeader = "Date: &D"
                ' .LeftFooter = "Creator: " & UserName
                .RightFooter = "Page &P of &N"
            End With
        End Sub
    End Class
    

    I hope this will be helpful.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    27. února 2012 10:10
    Moderátor
  • I haven't transitioned the code to 2010 yet, so we're still running on VS 2005.  We use the line "Imports Excel = Microsoft.Office.Interop.Excel" in the code. We haven't written our own ApplicationClass object.  I'd like to transition to VS2010, but I don't know how that would affect the other systems we use (which are pretty old).

    I didn't notice that the code was using ApplicationClass instead of Application at all. I looked this morning and the "New Excel.ApplicationClass" call is all over the code.  There are only a few places where it is "New Excel.Application".  I'm assuming that calling it "New Excel.Application" is the correct way instead of using the ApplicationClass. 

    VS2005 doesn't complain about using ApplicationClass and most of the complaints only showup when you use the PageSetup methods. The other method calls seem to still work in VS2005, which is why the errors were intermittant and hard to find.   This is probably where we're getting a lot of errors during run time.  Something must have changed in the InterOp libraries to make this more stringent.  This code used to work with the older intreop libraries, but as we've upgraded our tools to Office 2010 (we haven't upgraded from VS2005 yet) I've noticed more weird behaviors.  I'll have to correct all of those ApplicationClass usages.

    Thanks for finding that.  I don't think I would have found that on my own. At least not for a long time.  You found it fast!

    Thanks agian!


    Alan Edwards

    27. února 2012 16:09
  • Hi Alan,

    It is my pleasure. And welcome to join this forum.

    Best regards,


    Mike Feng
    MSDN Community Support | Feedback to us
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    28. února 2012 6:51
    Moderátor