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
GIPAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JAN PAKULAICT Infranstructure ManagerCommented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.