[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 450
  • Last Modified:

Exception de HRESULT : 0x800A03EC Interop.excel

Hi I have error Exception de HRESULT : 0x800A03EC when I use very long range.
What is the max lenght I can use ?

This is my code

oSheet1.Range("C6,D6,E6,C7,D7,E7,C8,D8,C9,D9,E9,C10,C11,C12,C14,D14,E14,C15,C16,C17,C18,C19,C20,C21,C22,C23,C24,C25,C26,C27,C28,C29,C30,C31,C32,C33,C34,C35,C36,C37,C38,C39,C40,D40,E40,C41,D41,E41,C42,C43,C44,C45,C46,C47,C48,C49,C50,C51,C52,C53,D53,E53,C54,D54,E54,C55,D55,E55,C56,C57,C58,C59,C60,C61,C62,C63,C64,C65,C66,C67,D67,E67,C68,C69,C70,C71,C72,D72,E72,C73,D73,E73,C74,C75,C76,C77,C78,C79,D79,E79,C80,D80,E80,C81,C82,C83,D83,E83,C84,D84,E84,C85,C86,C87,C88,C89,C90,D90,E90,C91,D91,E91,C92,D92,E92,C93,D93,E93,C94,C95,C96,C97,C98,C99,C100,C101,C102,C103,C104,D104,E104,C105,D105,E105,C106,C107,C108,C109,C110,C111,C112,C113,C114,C115,C116,C117,C118,C119,C120,D120,E120,C121,D121,E121,C122,D122,E122,C123,D123,E123,C124,D124,E124,C125,D125,E125,C126,D126,E126,C127,D127,E127,C128,C129,D129,E129,C130,D130,E130,C131,D131,E131,C132,D132,E132,C133,D133,E133,C134,D134,C135,C136,C137,C138,C139,C140,C141,C142,D142,E142,C143,D143,E143,C144,D144,E144,C145,C146,D146,E146,C147,D147,E147,C148,C149,C150,C151,C152,C153,C154,C155,C156,C157,C158,C159,C160,C161,C162,D162,E162,C163,C164,C165,C166,D166,E166,C167,D167,E167,C168,D168,E168,C169,C170,D170,E170,C171,D171,E171,C172,D172,E172,C173,D173,E173,C174,D174,E174,C175,C176,C177,C178,C179,C180,C181,C182,C183,C184,C185,C186,C187,C188,C189,C190,C191,C192,C193,C194,C195,C196,C197,D197,E197,C198,D198,E198,C199,D199,E199,C200,D200,E200,C201,D201,E201,C202,D202,E202,C203,D203,E203,C204,D204,E204,C205,D205,E205,C206,D206,E206,C207,C208,C209,C210,C211,C212,C213,C214,D214,E214,C215,D215,E215,C216,D216,E216,C217,D217,E217,C218,D218,E218,C219,C220,D220,E220,C221,D221,E221,C222,D222,E222,C223,C224,C225,C226,C227,C228,D228,E228,C229,D229,E229,C230,D230,E230,C231,C232,D232,E232,C233,C234,C235,C236,C237,C238,C239,C240,C241,C242,C243,C244,C245,C246,C247,C248,C249,C250,C251,C252,C253,C254,C255,C256,C257,E257,C258,E258,C259,D259,E259,C260,D260,E260,C263,C267,C269,C272,C279,C282,C283,C288,D288,C289,D289,C290,C298,C299,C301,C302,C303,C305,C307,C308,C309,C322,C324,C325,D325,C328,C329,C331,C332,C333,C334,C335,C336,C337,C338,D338,E338,C339,D339,E339,C345,C346,C347,C349,C350,C352,D352,E352,C355,D355,C356,D356,C357,D357,E357,C358,D358,C359,D359,C361,D361,C362,C363,D363,C364,D364,E364,E365,C366,D366,C367,D367,E367,C368,C372,C373,C374,C375,C376,D376,E376,C377,D377,E377,C378,D378,E378,C379,D379,E379,C380,C381,D381,E381,C382,C383,C384,C385,C386,D386,E386,C387,C390,C391,D391,C392,C393,D393,C394,C395,C396,C397,C398,D398,E398,C399,D399,E399,C400,D400,C401,D401,C404,C405,D405,E405,C406,C407,C408,D408,E408,C409,D409,C411,D411,C412,D412,E412,C416,C417,C419,C420,C422,C428,C430,C435,C445,C446,C447,C448,C449,C453,C454,C456,C457,C458,C459,C460,C461,C462,C463,C467,C469,C471,D471,C472,C473,C474,C475,C476,C477,C478,D478,C485,C497,C498,C500,C501,C505,D505,E505,C515,C516,C517,C518,C519,C520,C521,C522,C523,C524,C525,C526,C527,C529,C530,C531,C532,C534,D534,E534,D535,E535,C537,D537,E537,C540,D540,E540,C541,D541,E541,C542,D542,E542,C543,D543,C544,D544,C545,D545,E545,C546,D546,E546,C547,D547,E547,C548,C549,C550,C551,C552,C554,D554,E554,C555,C556,D556,C559,C560,C561,C566,C568,C570,D570,C571,C572,C573,C574,C575,C576,C577,D577,E577,C580,D580,C581,D581,C582,D582,E582,C583,D583,C584,C585,C586,C587,C588,C593,D593,E593,C594,D594,E594,C595,D595,E595,D596,C597,D597,E597,C598,D598,E598,C599,D599,E599,C602,D602,E602,C604,D604,E604,C610,D610,E610,C611,D611,E611,C612,E628,C636,C638,C642,C653,C654,C656,C657,C659,C661,D661,E661,C664,C665,C666,C667,C668,C669,D669,E669,C670,C671,C674,C675,C676,C677,C678,C680,C681,C682,D682,C689,D689,E689,C690,C691,D691,C694,C697,D697,C698,D698,E698,C702,D702,E702,C703,D703,C704,D704,C705,D705,C706,C707,D707,E707,C709").Interior.Color = clrVoyante
0
GIP
Asked:
GIP
  • 6
  • 6
1 Solution
 
JAN PAKULACommented:
It seems, that you are not the only one:

http://stackoverflow.com/questions/7099770/hresult-0x800a03ec-on-worksheet-range
 
this error is occurring when you work with an old (xls) workbook opened in Excel 2007 or 2010. If this is the case, try first to save the file as new format workbook (xlsx), before you access the cells.
0
 
David L. HansenProgrammer AnalystCommented:
Will named ranges work for you?  It sure would be cleaner (and easier to work with) than what you've got.  Apologies if you've already considered this solution.

see this for a simple tutorial.
0
 
GIPAuthor Commented:
Hi,
How I can create it on xlsx ?

This is my code
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
oBook = oExcel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet)
Dim oSheet1 As Microsoft.Office.Interop.Excel.Worksheet
oSheet1 = oBook.Worksheets(1)

I do some test and the max range is 255 caracter.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
David L. HansenProgrammer AnalystCommented:
oSheet1.Range(yourSpecialRangeName).interior.color = clrVoyante
0
 
GIPAuthor Commented:
David no my original code was

Dim SelectedQuantity as string
oSheet1.Range(SelectedQuantity).interior.color = clrVoyante
I write all cell for you can see what I have on my string.

If I have more 255 character is give me the error

I build sub for resolve my problem.
But I want to know how if there a solution to upgrade at 65536

Public Shared Sub RangeConditionQuantite(dt As DataTable, ByRef oSheet As Excel.Worksheet)

     Dim ValeurMaxRange As Integer = 255
        Dim SCV As String = ""
        Dim Range As Excel.Range

        If dt.Rows.Count > 0 Then
            For Each row As DataRow In dt.Rows
                If SCV.ToString.Count + row(0).ToString.Count + 1 > ValeurMaxRange Then
                    Range = oSheet.Range(SCV)
                    Range.Validation.Add(Excel.XlDVType.xlValidateWholeNumber, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, nbMinPermisExcel, nbMaxPermisExcel)
                    Range.Validation.ErrorMessage = "Vous devez entrer un nombre compris entre " & nbMinPermisExcel & " et " & nbMaxPermisExcel
                    Range.Validation.ErrorTitle = "Condition quantite"
                    Range.Locked = False
                    SCV = ""
                End If
                If SCV.Length > 0 Then
                    SCV += ","
                End If
                SCV += row(0).ToString
            Next
            If SCV.Length > 0 Then
                Range = oSheet.Range(SCV)
                Range.Validation.Add(Excel.XlDVType.xlValidateWholeNumber, Excel.XlDVAlertStyle.xlValidAlertStop, Excel.XlFormatConditionOperator.xlBetween, nbMinPermisExcel, nbMaxPermisExcel)
                Range.Validation.ErrorMessage = "Vous devez entrer un nombre compris entre " & nbMinPermisExcel & " et " & nbMaxPermisExcel
                Range.Validation.ErrorTitle = "Condition quantite"
                Range.Locked = False
            End If
        End If
    End Sub

Open in new window

0
 
GIPAuthor Commented:
Sorry this is good sub

    Public Shared Sub RangeEtat(dt As DataTable, ByRef oSheet As Excel.Worksheet)

        Dim ValeurMaxRange As Integer = 255
        Dim SCV As String = ""
        If dt.Rows.Count > 0 Then
            For Each row As DataRow In dt.Rows
                If SCV.ToString.Count + row(0).ToString.Count + 1 > ValeurMaxRange Then
                    oSheet.Range(SCV).Interior.Color = clrVoyante
                    SCV = ""
                End If
                If SCV.Length > 0 Then
                    SCV += ","
                End If
                SCV += row(0).ToString
            Next
            If SCV.Length > 0 Then
                oSheet.Range(SCV).Interior.Color = clrVoyante
            End If
        End If

    End Sub

Open in new window

0
 
David L. HansenProgrammer AnalystCommented:
Can you reword your question please?  I don't mind if you just post in french...we can google translate if necessary.
0
 
GIPAuthor Commented:
Hi David,
When my cell selection is larger than 255 characters (8 bits) interop.excel gives me error 0x800A03EC. I wonder if we can increase that number to 65536 (16 bit). Although I solved my problem by making me a procedure that takes only 255 characters at a time (see my code above)
0
 
David L. HansenProgrammer AnalystCommented:
I can't help but think that there must be a far better tool for you than Excel.  I'm not sure why you'd need to do this in a spreadsheet.  Excel often gets used in ways that quickly cause more problems that it solves.
0
 
GIPAuthor Commented:
This is an Excel file that is sent to all customers to be able to make automatic purchases.
0
 
David L. HansenProgrammer AnalystCommented:
I believe the only way of overcoming the 255 issue is to handle the range through a named range, variable or function as you have done.  There is no way to alter the 255 character restriction intrinsically.

I know you may have no choice and most likely you are being told to make this work. For me however, I would not choose a spreadsheet as a means of making automatic purchases (especially not with Visual Studio at hand).
0
 
David L. HansenProgrammer AnalystCommented:
The original question is here.  That question was answered with several other helps given as well.
0
 
GIPAuthor Commented:
.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now