none
Power Query error in VBA tool RRS feed

  • Question

  • I'm working on a client project that consists of a combination of VBA and Power Query.

    Description:

    To overall goal is to transform .csv files from one format to another format that is outputted to an Excel workbook.

    1) Step 1 - When the data conversion tool file is opened, the excel version is checked and an initialization process is started.

    2) The initialization process removes the Excel ribbon, formula bar, scroll bar and all shortcuts. All the user sees are some buttons on the screen.

    3) The user clicks a button and instructed to get the files (.csv) that they want converted.

    4) The user selects the files through a standard File Open dialog box.

    5) The files are copied to a temp folder on the user's Desktop.

    I should note at this point that there are a number of Power Query queries in the conversion tool file that are preprogrammed to do various things. In VBA, at the appropriate points in the program, one or more queries are refreshed. There are several parameters that VBA writes to Excel sheets that serve as sources or parameters to the queries.

    6) VBA writes the temp folder path on a sheet. This path serves as a parameter for the source of a couple of queries (including the main query).

    7) The path connection is refreshed, so that the path parameter in PQ is updated with the current source.

    8) One query written back to Excel shows the files listed (before combining), with additional columns to verify that each file is valid (has the expected column names in a specific order), and a Row Count column that is used to verify that the total rows would not exceed to maximum rows of an Excel worksheet. VBA reads these column values and acts appropriately.

    9) After validation is checked, a dialog box is displayed that allows the user to specify values that are used in new columns that are added in the conversion process (the value for each column is copied down the entire column).

    10) VBA writes the values entered in the dialog to an Excel table. This table serves as the source of a PQ query. After the values are written to the table, the connection is updated.

    11) The main query that imports the files from the temp folder and does virtually all of the conversion work is refreshed, since all of the queries and parameters it needs to work with have been refreshed.

    12) The main query data (which is written to an Excel sheet) is then copied to a new workbook, which can be saved to end the conversion process or additional files can be processed (with new values to fill down in columns) and appended to the new workbook. A third option allows the processed data to be appended to a file that was previously processed and saved.

    I should note that the conversion tool is bilingual. Depending on the Excel language, all dialog boxes appear in either English of French. The language ID is written to an Excel sheet (msoLanguageID value) and the connection refreshed prior to step (3). The main query in PQ uses this language ID value to determine whether English or French description columns will be used from tables that are joined in the query.

    So the tool works perfectly on my system (version of Excel is 365 Pro Plus). However as soon as the client opens the tool he gets an unexpected error. The client is probably using a volume license version of Office 2016, or a government licensed version. At any rate, all the M functions that I am using are functions that were available when Office 2016 shipped. I suspect that the error is occurring when an attempt to refresh the language ID connection is executed (this is the only connection that is refreshed in the initialization process). 

    Details of error:

    Feedback Type:

    Frown (Error)

     

    Error Message:

    Exception from HRESULT: 0x800A03EC

     

    Stack Trace:

       at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsBase.ValidateResult(Int32 result, Int32[] expectedValues)

       at Microsoft.Mashup.Client.Excel.NativeExcelFunctionsMSI.Microsoft.Mashup.Client.Excel.INativeExcelFunctions.LoadMashupData(IntPtr workbookPointer)

       at Microsoft.Mashup.Client.Excel.Shim.NativeWorkbookStorageServices.LoadMashupData(IWorkbook workbook, Boolean throwOnCorrupted, MashupCustomXmlPart& data)

       at Microsoft.Mashup.Client.Excel.Shim.IWorkbookStorageServicesExtensions.TryGetPackage(IWorkbookStorageServices workbookStorageServices, IWorkbook workbook, PackageComponents& excelPackage, PackageValidationResult& validatePackageResult)

       at Microsoft.Mashup.Client.Excel.WorkbookPackageInfo.InitializeFromWorkbook(ExcelServices excelServices, IWorkbook workbook, IWorkbookIdentity workbookIdentity, Boolean reloadingFromWorkbook)

       at Microsoft.Mashup.Client.Excel.PackageManager.InitializeWorkbookPackageInfo(IWorkbook workbook, IWorkbookIdentity workbookIdentity, Boolean allowCreateNew, Boolean reloadingFromWorkbook)

       at Microsoft.Mashup.Client.Excel.PackageManager.EnsureWorkbookPackageInfo(IWorkbook workbook, Boolean allowCreateNew, Boolean reloadingPackage)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.GetWorkbookQueries(IWindowContext windowContext, PackageManager packageManager)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CacheEntry.Create(IWindowContext windowContext, IWorkbookServices workbookServices, PackageManager packageManager)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.CreateAndSetCacheEntry(IWindowContext windowContext)

       at Microsoft.Mashup.Client.Excel.ActiveQueryCache.GetOrCreateCacheEntry(IWindowContext windowContext)

       at Microsoft.Mashup.Client.Excel.CustomTaskPaneManagers.UpdateQueriesTaskPaneVisibility(IWindowContext windowContext)

       at Microsoft.Mashup.Client.Excel.ExcelEventHandler.PerformPeriodicUpdate(IEnumerable`1 queriesTaskPaneVisibilityUpdateWindows)

       at Microsoft.Mashup.Client.Excel.NativeEventHandler.<OnIdle>b__0()

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

     

    Stack Trace Message:

    Exception from HRESULT: 0x800A03EC

     

    Invocation Stack Trace:

       at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()

       at Microsoft.Mashup.Client.UI.Shared.StackTraceInfo..ctor(String exceptionStackTrace, String invocationStackTrace, String exceptionMessage)

       at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo)

       at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.ReportException(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, Boolean useGDICapture)

       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.<>c__DisplayClass1.<HandleException>b__0()

       at Microsoft.Mashup.Host.Document.SynchronizationContextExtensions.SendAndMarshalExceptions(SynchronizationContext context, Action callback)

       at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)

       at Microsoft.Mashup.Host.Document.ExceptionHandlerExtensions.HandleExceptions(IExceptionHandler exceptionHandler, Action action)

       at Microsoft.Mashup.Client.Excel.NativeEventHandler.OnIdle()

       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.<OnIdleCallback>b__3a()

       at Microsoft.Mashup.Client.Excel.ExcelCallbackManager.InvokeAndReturnHResult(Action action)

       at Microsoft.Mashup.Client.Excel.NativeExcelConnectorBase.OnIdleCallback()

     

    Supports Premium Content:

    True

     
    Monday, November 5, 2018 6:35 PM

Answers

All replies

  • Hi Colin,

    I'm trying to reproduce the error you are getting.
    I wonder if it's possible to get the Office and PowerQuery version numbers from the client machine. Office version is under File -> Account -> About Excel, and PowerQuery version is under New Query -> Query Options -> Diagnostics.
    I will also appreciate if you can send me the problematic workbook. Just make sure that your workbook doesn’t contain any sensitive information in it – remove the sensitive data or scramble it before sharing the workbook with me.

    Thanks,
    Tal
    Excel Team

    Tuesday, December 4, 2018 6:41 PM
  • Hi Tal,

    Thanks for the response. I've made several discoveries since my original post.

    1) The error shows up in Excel 2016 Pro Plus. It's on a government machine (Canada), so I assume that it is some kind of volume license. I've asked the customer to update to the most recent service pack (currently pending IT action).

    2) The error is not reproducible in Office 365 or Excel 2010 Pro Plus (even with a year old version of the Power Query addin). I had to visit the customer site to troubleshoot the problem.

    3) The error surfaces when a command to hide the ribbon is executed (see below). The error bypasses all of VBA's error handling, so cannot be ignored with "On Error Resume Next".

    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"

    4) After the error is dismissed, the tool works normally (since VBA error handling is bypassed, the error does not terminate the program). The error is displayed every time the workbook is opened.

    I will mail you a copy of the workbook (will an xlsm file make it through your firewall?) along with sample files and some instructions.

    Tuesday, December 4, 2018 8:13 PM
  • Hi,

    Can you please try sending it to tapeleg@microsoft.com?

    Thanks.

    • Marked as answer by Colin Banfield Thursday, January 24, 2019 6:51 PM
    Wednesday, December 5, 2018 3:00 PM
  • Done.
    Thursday, December 6, 2018 12:40 AM
  • Hi Colin. Were you and Tal able to resolve this issue?

    Thanks,
    Ehren

    Thursday, January 24, 2019 6:34 PM
    Owner
  • Hi Ehren,

    Yes. Thanks for the reminder!

    Thursday, January 24, 2019 6:52 PM
  • Any idea how it was fixed?
    I am getting the same error when trying to refresh a PowerQuery with VBA using ActiveWorkbook.RefreshAll 

    Any help would be much appreciated!

    Monday, July 29, 2019 1:01 PM
  • My client issue was fixed in the February 2019 build of Office 2016 standalone. My issue had nothing to do with what you're reporting, so your issue may or may not be fixed. 

    I've not had any issues with refreshing PQ tables in VBA to date, but I have never used ActiveWorkbook.RefreshAll to refresh tables.

    Monday, July 29, 2019 3:57 PM