Asked  7 Months ago    Answers:  5   Viewed   50 times

I am getting HRESULT: 0x800A03EC on Worksheet.range method. Number of rows are more than 70K. Office 2007.

Code:

Microsoft.Office.Interop.Excel.Range neededRange
    = currentWS.Range[cell.Cells[1, 1], cell.Cells[nRowCount, nColumnCount]];

Here my rowcount is more than 65530 . Breaks on this function. I have observed that it breaks only when row count goes more than 65530.

 Answers

44

This problem occurs if you are using a backwards compatible sheet (a .xls) instead of a .xlsx

To allow sheets to be opened in pre office 2007 version it can't contain more than 65k rows. You can check the number of rows in your sheet by using ctrl+arrowdown till you hit the bottom. If you try to get a range larger than that number of rows it will create an error

Tuesday, June 1, 2021
 
eek
answered 7 Months ago
eek
38

Concerning whether there is a way around COM or VBA I don't think that it is possible (at least not without any very dirty tricks). The reason is that the only way Office can execute external code (i.e. you add-in) is via COM. Even VSTO is still using the old IDTExtensibility2 COM interface underneath. IDTExtensibility2 is a COM interface that all add-ins for Microsoft Office applications must implement.

Before VSTO, Office add-ins had to implement this IDTExtensibility2 interface themselves. In such a COM based add-in (or COM-visible managed add-in) you can simply add your UDF as described here.

However, now with VSTO, there is an additional layer of abstraction: VSTO uses a so-called Solution Loader implementing IDTExtensibility2, which is a dll provided by the VSTO runtime. This means that your add-in is no longer COM-visible. Hence, if you added a UDF to your VSTO add-in it won't be visible to Office.

Paul Stubbs explains on his blog how to do with VSTO and VBA: How to create Excel UDFs in VSTO managed code

  1. Create a class with your functions in VSTO

    <System.Runtime.InteropServices.ComVisible(True)>
    Public Class MyManagedFunctions
        Public Function GetNumber() As Integer
            Return 42
        End Function 
    End Class
    
  2. Wire up your class to VBA in VSTO

    Private Sub ThisWorkbook_Open() Handles Me.Open
        Me.Application.Run("RegisterCallback", New MyManagedFunctions)
    End Sub
    
  3. Create Hook for managed code and a wrapper for the functions in VBA

    In a VBA module in your spreadsheet or document

    Dim managedObject As Object
    
    Public Sub RegisterCallback(callback As Object)
        Set managedObject = callback
    End Sub
    
    Public Function GetNumberFromVSTO() As Integer
        GetNumberFromVSTO = managedObject.GetNumber()
    End Function
    

Now you can enter =GetNumberFromVSTO() in a cell, when excel starts the cell value should be 42.

Monday, August 9, 2021
 
themihai
answered 4 Months ago
100

I've found the issue which was not letting me run my project in 'Debug' configuration mode, though it worked in 'Release' mode. At some point, the AddIn, got hard-disabled (not sure if that's the term to use). At that point, trying to re-enable from within Excel doesn't do anything. Within the registry, there is a folder where disabled AddIns are flagged. I deleted all of the keys from this folder except for "(Default)" and now my AddIn works when launching from Visual Studio 2010 in 'Debug' mode.

HKEY_CURRENT_USERSoftwareMicrosoftOffice14.0ExcelResiliencyDisabledItems

enter image description here

Tuesday, August 10, 2021
 
nasty
answered 4 Months ago
94

I reproduced your issue.. tried all: oWB.SaveAs / oWB._SaveAs / ( oXL.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet ).SaveAs / oWB.Application.ActiveWorkbook.SaveAs all throw exception: HRESULT: 0x800A03EC...

But I see you tried with: oWB.SaveCopyAs(strCurrentDir + strFile); and it works if I set next:

oWB.Saved = true;
oWB.SaveCopyAs( strCurrentDir + strFile );

Why you don't use SaveCopyAs ?

Monday, November 15, 2021
 
user113716
answered 3 Weeks ago
34

After hours of research and try, the solution is:

As it looks like a permission problem; as the WinForm is using the User who installed the Office while the windows service is using the LocalSystem user to log on.

In order to allow the LocalSystem user to run the Office, I needed to select the interactive user option in the Microsoft Excel Application Properties.

Step by Step:

  1. Reach MMC Component Services (For 32 bit OS, use mmc comexp.msc while for 64 bit OS , use mmc comexp.msc /32) in Run a command window (Windows + R keys):

Run A Command Window

  1. Explore the tree --> Select DCOM Config (Right-click on Microsoft Excel Application and run the properties) --> Enable The interactive user option in identity tab --> OK.

component Services

Wednesday, December 1, 2021
 
StoneThrow
answered 5 Days ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share