I have a (power) query created from a blank query where the source is excel.currentworkbook(), so that all tables are loaded and which I then filter as needed in the query. It's been working well for some time and I have had no need to do anything other than refresh it as needed, but when I went into the query editor today to check the source, I got a LONG error message which means nothing to me, and is pasted at the end of the this post. The query will still update though.
I also deleted the existing query and tried to recreate it but got the same error result. And as a test, I used the same technique on another existing workbook and it worked as I expected, but nothing will get it to work any more with this one workbook. Sorry, I can't upload it here.
I saw this technique in a video somewhere, and don't have the knowledge to work out what is now going wrong.
Henry
Here's the error message, sorry for the long post
Unexpected error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
Details:
Microsoft.Mashup.Client.Excel.Shim.NativeExcelException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED)) ---> System.Runtime.InteropServices.COMException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
--- End of inner exception stack trace ---
Server stack trace:
at Microsoft.Mashup.Client.Excel.Interop.NativeExcelInteropUtils.ValidateResult(Int32 result, Int32[] expectedValues)
at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsC2RBase.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.TryGetNamedObjectProperties(IntPtr workbookPointer, String name, NamedObjectProperties& namedObjectProperties)
at Microsoft.Mashup.Client.Excel.ExcelHost.<>c__DisplayClass6_0.<TryGetTable>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3_0`1.<SendAndMarshalExceptions>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_1.<SendAndMarshalExceptions>b__0(Object null)
Exception rethrown at [0]:
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Mashup.Host.Document.ExceptionExtensions.CaptureAndRethrow(Exception e)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions[T](SynchronizationContext context, Func`1 callback)
at Microsoft.Mashup.Client.Excel.ExcelHost.TryGetTable(PackageReference packageReference, String tableName, Int32 skip, Int32 take, Byte[]& content, Boolean& columnNamesGenerated, String& errorMessage)
at Microsoft.Mashup.Host.Document.Evaluation.ExcelService.TryGetTable(String name, Int32 skip, Int32 count, IDataReader& dataReader, Boolean& columnNamesGenerated, String& errorMessage)
at Microsoft.Mashup.Host.Document.Evaluation.Services.RemoteExcelServiceFactory.Stub.OnTableRequest(IMessageChannel channel, TableRequestMessage request)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)
I also deleted the existing query and tried to recreate it but got the same error result. And as a test, I used the same technique on another existing workbook and it worked as I expected, but nothing will get it to work any more with this one workbook. Sorry, I can't upload it here.
I saw this technique in a video somewhere, and don't have the knowledge to work out what is now going wrong.
Henry
Here's the error message, sorry for the long post
Unexpected error: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
Details:
Microsoft.Mashup.Client.Excel.Shim.NativeExcelException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED)) ---> System.Runtime.InteropServices.COMException: Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))
--- End of inner exception stack trace ---
Server stack trace:
at Microsoft.Mashup.Client.Excel.Interop.NativeExcelInteropUtils.ValidateResult(Int32 result, Int32[] expectedValues)
at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsC2RBase.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.TryGetNamedObjectProperties(IntPtr workbookPointer, String name, NamedObjectProperties& namedObjectProperties)
at Microsoft.Mashup.Client.Excel.ExcelHost.<>c__DisplayClass6_0.<TryGetTable>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass3_0`1.<SendAndMarshalExceptions>b__0()
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.<>c__DisplayClass2_1.<SendAndMarshalExceptions>b__0(Object null)
Exception rethrown at [0]:
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.Mashup.Host.Document.ExceptionExtensions.CaptureAndRethrow(Exception e)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)
at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions[T](SynchronizationContext context, Func`1 callback)
at Microsoft.Mashup.Client.Excel.ExcelHost.TryGetTable(PackageReference packageReference, String tableName, Int32 skip, Int32 take, Byte[]& content, Boolean& columnNamesGenerated, String& errorMessage)
at Microsoft.Mashup.Host.Document.Evaluation.ExcelService.TryGetTable(String name, Int32 skip, Int32 count, IDataReader& dataReader, Boolean& columnNamesGenerated, String& errorMessage)
at Microsoft.Mashup.Host.Document.Evaluation.Services.RemoteExcelServiceFactory.Stub.OnTableRequest(IMessageChannel channel, TableRequestMessage request)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.OnMessageWithUnknownChannel(IMessageChannel baseChannel, MessageWithUnknownChannel messageWithUnknownChannel)
at Microsoft.Mashup.Evaluator.MessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.ChannelMessenger.ChannelMessageHandlers.TryDispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.MessageHandlers.Dispatch(IMessageChannel channel, Message message)
at Microsoft.Mashup.Evaluator.Interface.IMessageChannelExtensions.WaitFor[T](IMessageChannel channel)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.WaitFor(Func`1 condition, Boolean disposing)
at Microsoft.Mashup.Evaluator.RemotePreviewValueSource.PreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Evaluator.Interface.TracingPreviewValueSource.get_SmallValue()
at Microsoft.Mashup.Host.Document.Analysis.PackageDocumentAnalysisInfo.PackagePartitionAnalysisInfo.SetPreviewValue(EvaluationResult2`1 result, Func`1 getStaleSince, Func`1 getSampled)