none
Excel.CurrentWorkbook() causing error: 'Exception from HRESULT: 0x800A03EC' RRS feed

  • Question

  • Hi,

    I was about to post a question (after reading around a dozen threads about 'Exception from HRESULT: 0x800A03EC') but found the source of my problem in a last attempt and a workaround.  Still, I decided to post here because I found nothing about it after what I would call a thorough web search.

    The problem occurred after refreshing the preview of a query that loads my workbook (Dropbox link: https://www.dropbox.com/s/97h1tbls932ztx1/NamedRangeIssue.xlsx?dl=0) onto Power Query so I can later reference any table without using the Excel.CurrentWorkbook function again (It's just how I like to organize my queries).  It didn't load and called an error message window saying that the unhandled error 'Exception from HRESULT: 0x800A03EC' occurred.

    My queries are the following:

    // ThisWorkbook
    //Loads all tables and named ranges in the current workbook.
    let
        Source = Excel.CurrentWorkbook()
    in
        Source
    
    // Ref
    //Tries to access the named range 'Ref' that references a cell in a deleted sheet.
    let
        Source = ThisWorkbook{[Name="Ref"]}
    in
        Source
    
    // DummyName
    /*  
    Successfully loads a named range by selecting it form the query 'ThisWorkbook' 
    using an item access expression even though 'ThisWorkbook' cannot be loaded 
    and returns the error 'Exception from HRESULT: 0x800A03EC'.
    */
    let
        Source = ThisWorkbook{[Name="DummyName"]}[Content]
    in
        Source
    
    // FilterNames
    //Filters all tables or named ranges that start with 'Dummy'.
    let
        Source = ThisWorkbook,
        FilterDummies = Table.SelectRows( Source, each [Name] <> "Ref" )
    in
        FilterDummies

    The error details are long, so I'll post them at the end (by the way, clicking the 'Send frown' option results in nothing).

    After a lot of reading without finding anything useful, I opened the Name Manager and saw a name called 'Ref' that had a missing reference.  The name's definition was something like:

    Ref = #REF!$A$1

    This was a named range I no longer needed that used to reference a sheet that I had deleted and was causing the 'ThisWorkbook' query to fail.  Removing this named range using the Name Manager fixed the issue, but it seems like a bug to me.

    The weird thing is that the error doesn't propagate to any of the queries that access other named ranges through the "corrupt" query, unless you want to load the named range with the reference issue.  You can filter the table or access a single record using an item access expression.  That seems to contradict the "M" Language Specification that reads:

    "Raising an error will cause the current expression evaluation to stop, and the expression evaluation stack will unwind until one of the following occurs:
     A record field, section member, or let variable – collectively: an entry – is reached. The entry is marked as having an error, the error value is saved with that entry, and then propagated. Any subsequent access to that entry will cause an identical error to be raised. Other entries of the record, section, or let expression are not necessarily" (Section 10.1, page 94)

    I played around with other formulas for the named range and only those that fail to specify a sheet reference showed the same behavior:

    Ref = !$A$1
    Ref = #REF!$A$1

    Other errors in named ranges result in a table value with a single error value or are missing in the workbook table.  I tried: 

    Ref = Sheet1!#REF!
    Ref = #REF!#REF!
    Ref = NA()

    and they do not appear in the table resulting from Excel.CurrentWorkbook(), which is not too bad, since they would probably be useless values.

    I could find no reason for wanting to have a named range that references a sheet that doesn't exist, but I have used the = !$A1 syntax for relative references across sheets (I don't use them since I discovered Power Pivot and Cube Formulas).  I would expect these kind of named ranges to return an error value instead of a table as other names do, or maybe not to be loaded at all, but causing the Excel.CurrentWorkbook function to crash seems like too much.

    Has someone experienced the same issue?

    Is the Power Query team aware of the issue?  If so, do they plan to do something about it?

    Thanks in advance for any feedback.

    The error details:

    Feedback Type:
    Frown (Error)
    
    Timestamp:
    2017-03-02T18:24:22.3162227Z
    
    Local Time:
    2017-03-02T12:24:22.3162227-06:00
    
    Product Version:
    2.41.4581.301 (PQ-CY16SU11) (x64)
    
    Release:
    November 2016
    
    Excel Version:
    16.0.4480.1000
    
    Excel Install Location:
    C:\Program Files\Microsoft Office\Office16\EXCEL.EXE
    
    IE Version:
    11.576.14393.0
    
    OS Version:
    Microsoft Windows NT 10.0.14393.0 (x64 es-MX)
    
    CLR Version:
    4.6.2. or later [Release Number = 394802]
    
    Workbook Package Info:
    1  - es-MX, fastCombine: Enabled.
    2* - en-US, fastCombine: Enabled.
    
    Peak Working Set:
    669 MB
    
    Private Memory:
    656 MB
    
    Peak Virtual Memory:
    34 GB
    
    Error Message:
    Excepción de HRESULT: 0x800A03EC
    
    Stack Trace:
    Microsoft.Mashup.Client.Excel.Shim.NativeExcelException: Excepción de HRESULT: 0x800A03EC ---> System.Runtime.InteropServices.COMException: Excepción de HRESULT: 0x800A03EC
       --- Fin del seguimiento de la pila de la excepción interna ---
    
    Server stack trace: 
       en Microsoft.Mashup.Client.Excel.NativeExcelFunctionsBase.ValidateResult(Int32 result, Int32[] expectedValues)
       en Microsoft.Mashup.Client.Excel.NativeExcelFunctionsMSI.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.TryGetNamedObjectProperties(IntPtr workbookPointer, String name, NamedObjectProperties& namedObjectProperties)
       en Microsoft.Mashup.Client.Excel.ExcelHost.<>c__DisplayClass1.<TryGetTable>b__0()
       en Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass61.<SendAndMarshalExceptions>b__5()
       en Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3.<SendAndMarshalExceptions>b__0(Object null)
    
    Exception rethrown at [0]: 
       en Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
       en Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions[T](SynchronizationContext context, Func1 callback)
       en Microsoft.Mashup.Client.Excel.ExcelHost.TryGetTable(PackageReference packageReference, String tableName, Int32 skip, Int32 take, Byte[]& content, Boolean& columnNamesGenerated, String& errorMessage)
       en Microsoft.Mashup.Host.Document.Evaluation.ExcelService.TryGetTable(String name, Int32 skip, Int32 count, IDataReader& dataReader, Boolean& columnNamesGenerated, String& errorMessage)
       en Microsoft.Mashup.Host.Document.Evaluation.Services.RemoteExcelServiceFactory.Stub.OnTableRequest(IMessageChannel channel, TableRequestMessage request)
       en Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       en Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       en Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       en Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
       en Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       en Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
       en Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
       en Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
       en Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func1 condition, Boolean disposing)
       en Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
       en Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
       en Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult21 result, Func1 getStaleSince, Func1 getSampled)
    
    Invocation Stack Trace:
       en Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
       en Microsoft.Mashup.Client.ClientShared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace)
       en Microsoft.Mashup.Client.ClientShared.FeedbackErrorInfo..ctor(String message, Nullable1 errorCode, String requestId, Exception exception)
       en Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()
       en Microsoft.Mashup.Client.ClientShared.UnexpectedExceptionHandler.HandleException(Exception e)
       en Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       en System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
       en System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
       en System.Delegate.DynamicInvokeImpl(Object[] args)
       en System.Windows.Forms.Control.InvokeMarshaledCallbackDo(ThreadMethodEntry tme)
       en System.Windows.Forms.Control.InvokeMarshaledCallbackHelper(Object obj)
       en System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       en System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
       en System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       en System.Windows.Forms.Control.InvokeMarshaledCallback(ThreadMethodEntry tme)
       en System.Windows.Forms.Control.InvokeMarshaledCallbacks()
       en System.Windows.Forms.Control.WndProc(Message& m)
       en System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
       en System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       en System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
       en System.Windows.Forms.Application.ThreadContext.LocalModalMessageLoop(Form form)
       en System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
       en System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
       en System.Windows.Forms.Form.ShowDialog(IWin32Window owner)
       en Microsoft.Mashup.Client.ClientShared.WindowManager.ShowDialog[T](T form, IWin32Window owner)
       en Microsoft.Mashup.Client.Excel.QueriesEditorWindowManager.ShowModalQueriesEditorWindow(IWin32Window ownerWindow, IWorkbookIdentity workbookIdentity, QueriesEditorParameters queriesEditorParameters, Queries queries)
       en Microsoft.Mashup.Client.Excel.DialogManager.TryShowModalEditor(IWin32Window ownerWindow, IWorkbook workbook, Query query, Boolean isNewQuery, Boolean navigatorExpanded)
       en Microsoft.Mashup.Client.Excel.ExcelRibbonHandler.<>c__DisplayClass43.<OnShowEditorAction>b__42(IWorkbook workbook)
       en Microsoft.Mashup.Client.Excel.Shim.IWindowContextExtensions.TryInvokeOnParentWorkbook(IWindowContext windowContext, Action1 action)
       en Microsoft.Mashup.Client.Excel.NativeRibbonHandler.<>c__DisplayClass1.<OnRibbonButtonAction>b__0()
       en Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)
       en Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.InvokeAndReturnHResult(Action action)
    
    
    Supports Premium Content:
    True
    • Changed type VossF Friday, March 31, 2017 12:29 AM No Response
    Thursday, March 2, 2017 7:26 PM

Answers

  • Hi there. I can't reproduce the error you're seeing (even with the steps you provided), but one of my colleagues said this looks like an issue we've seen before. I've filed a bug and we'll investigate.

    Thanks!

    Ehren

    Monday, April 17, 2017 8:03 PM
    Owner

All replies

  • Hi there. I downloaded your example xlsx, but can't seem to reproduce the issue. What steps should I take to get the error you're seeing?

    Thanks,

    Ehren

    Wednesday, April 12, 2017 5:52 PM
    Owner
  • Create a new sheet and then delete 'Sheet1'.  Go to the Query Editor and refresh the 'ThisWorkbook' query preview.
    Monday, April 17, 2017 3:40 PM
  • Hi there. I can't reproduce the error you're seeing (even with the steps you provided), but one of my colleagues said this looks like an issue we've seen before. I've filed a bug and we'll investigate.

    Thanks!

    Ehren

    Monday, April 17, 2017 8:03 PM
    Owner
  •  

    This issue was resolved when i used below mentioned statement. Please do not use other parameter in these functions.

    mWorkBook = xlApp.Workbooks.Open(FilePath)

    mWorkBook.Save();

    RESOLVED


    .NET,C#,SQL SERVER, Sharepoint 2010

    Wednesday, April 25, 2018 7:44 AM