Excel VBA Macro Runs Fine Until I turn On add-in Power Query

B

BennyBB

New Member
Joined
Sep 6, 2013
Messages
33
My macro runs fine until I turn on power-query. Once this is turned on I get an error message.

How can I run the macro error free with power-query turned on?

Win7/64+Office2013/32


Error Message:
"
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
Microsoft.Mashup.Client.UI.Shared.Com.ComWrapperException: Cannot cast null to type 'System.Double'. ---> System.NullReferenceException: Object reference not set to an instance of an object.
at Microsoft.Mashup.Client.UI.Shared.Com.ComWrapper.As[T](Object value)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Client.UI.Shared.Com.ComWrapper.As[T](Object value)
at Microsoft.Mashup.Client.Excel.Com.ExcelComWrapper.AsIntFromDouble(Object value)
at Microsoft.Mashup.Client.Excel.Com.Application.get_Build()
at Microsoft.Mashup.Client.Excel.Com.ApplicationFeatures..ctor(IApplication application)
at Microsoft.Mashup.Client.Excel.Com.Application..ctor(Object application, Boolean enableEvents)
at Microsoft.Mashup.Client.Excel.AddIn.TryCreateApplication(Object appObject, IApplication& application)
at Microsoft.Mashup.Client.Excel.AddIn.Extensibility.IDTExtensibility2.OnConnection(Object application, ext_ConnectMode connectMode, Object addInInst, Array& custom)

************** Loaded Assemblies **************
mscorlib
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
----------------------------------------
Microsoft.Mashup.Client.Excel
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Microsoft Power Query for Excel/bin/Microsoft.Mashup.Client.Excel.dll
----------------------------------------
Extensibility
Assembly Version: 7.0.3300.0
Win32 Version: 7.00.9466
CodeBase: file:///C:/Windows/assembly/GAC/Extensibility/7.0.3300.0__b03f5f7f11d50a3a/Extensibility.dll
----------------------------------------
office
Assembly Version: 15.0.0.0
Win32 Version: 15.0.5075.1001
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/office/15.0.0.0__71e9bce111e9429c/office.dll
----------------------------------------
Microsoft.Mashup.Client.UI
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Microsoft Power Query for Excel/bin/Microsoft.Mashup.Client.UI.DLL
----------------------------------------
Microsoft.Mashup.Document
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Microsoft Power Query for Excel/bin/Microsoft.Mashup.Document.DLL
----------------------------------------
Microsoft.Mashup.DocumentServices
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Micr...cel/bin/Microsoft.Mashup.DocumentServices.DLL
----------------------------------------
System
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.Net/as..._4.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
Microsoft.Mashup.Client.Packaging
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Micr...cel/bin/Microsoft.Mashup.Client.Packaging.DLL
----------------------------------------
System.Core
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.Net/as...4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
----------------------------------------
Microsoft.Mashup.Client.Models
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Micr... Excel/bin/Microsoft.Mashup.Client.Models.DLL
----------------------------------------
Microsoft.MashupEngine
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Microsoft Power Query for Excel/bin/Microsoft.MashupEngine.DLL
----------------------------------------
Microsoft.Mashup.Client.AddinTelemetry
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Micr...in/Microsoft.Mashup.Client.AddinTelemetry.DLL
----------------------------------------
Microsoft.Mashup.ProviderShared
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Micr...Excel/bin/Microsoft.Mashup.ProviderShared.DLL
----------------------------------------
System.Windows.Forms
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.Net/as....0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
Microsoft.Office.Interop.Excel
Assembly Version: 15.0.0.0
Win32 Version: 15.0.4569.1506
CodeBase: file:///C:/Windows/assembly/GAC_MSI...e111e9429c/Microsoft.Office.Interop.Excel.dll
----------------------------------------
Microsoft.Practices.Unity
Assembly Version: 2.1.505.0
Win32 Version: 2.1.505.2
CodeBase: file:///C:/Program Files (x86)/Microsoft Power Query for Excel/bin/Microsoft.Practices.Unity.DLL
----------------------------------------
System.Configuration
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3062.0 built by: NET472REL1
CodeBase: file:///C:/Windows/Microsoft.Net/as....0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 4.0.0.0
Win32 Version: 4.7.3190.0 built by: NET472REL1LAST_C
CodeBase: file:///C:/Windows/Microsoft.Net/as...v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
Microsoft.Mashup.EventSource
Assembly Version: 1.0.0.0
Win32 Version: 2.59.5135.201
CodeBase: file:///C:/Program Files (x86)/Microsoft Power Query for Excel/bin/Microsoft.Mashup.EventSource.DLL
----------------------------------------
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
For example:
<configuration>
<system.windows.forms jitdebugging="true">
</system.windows.forms>
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
"
Macro Code:
"
Sub Update_All()
'
' Update_All Macro
'
' Keyboard Shortcut: Ctrl+m
'
Application.Calculation = xlManual
ActiveWorkbook.Connections("Query from MS Access Database").Refresh
ActiveWorkbook.Connections("Query from MS Access Database1").Refresh
ActiveWorkbook.Connections("Query from MS Access Database2").Refresh
ActiveWorkbook.Connections("Query from MS Access Database3").Refresh
Application.Calculation = xlAutomatic
Calculate
ActiveWorkbook.RefreshAll
Calculate
'
Sheets("747_OSIP_BY_DATE").Select
Cells.Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
'
Sheets("747_By_Change_#").Select
Columns("B:B").Select
Selection.ColumnWidth = 20
Columns("K:K").Select
Selection.ColumnWidth = 30
Range("A2").Select
Sheets("767_ACMS_BY_DATE").Select
Columns("E:E").Select
With Selection
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("E7").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Range("E1").Select
With Selection
.HorizontalAlignment = xlRight
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'
Sheets("767_OSIP_BY_DATE").Select
Range("J8").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'
Sheets("767_By_Change_#").Select
Columns("B:B").Select
Selection.ColumnWidth = 20
Columns("K:K").Select
Selection.ColumnWidth = 30
'
Call Update_A1_Selected_Move_to_tab_1st
Call Date_Update
End Sub
“
I have tried using the following commands to try to suppress the error message popup without success:
Application.VBE.MainWindow.Visible = True
Application.VBE.MainWindow.Visible = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.EnableCancelKey = xlDisabled
Any other ideas?</configuration>
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Why all the refreshes instead of just the RefreshAll?
Why force Calculation multiple times even after you've turned Calculations to Automatice?
Have you updated the PowerPivot Add-in to the latest version?
You should avoid using those select statements as they are unneccesary.
Example
Code: 
[COLOR=#333333]Sheets("747_By_Change_#").Select[/COLOR]
[COLOR=#333333]Columns("B:B").Select[/COLOR]
[COLOR=#333333]Selection.ColumnWidth = 20[/COLOR]
use
Code: 
[COLOR=#333333]Sheets("747_By_Change_#").Col[/COLOR][COLOR=#333333]umns("B:B").[/COLOR][COLOR=#333333]ColumnWidth = 20[/COLOR]

Using With statements reduces the dots "." which can slow code and make it more difficult to read. Performance not an issue in small code, unless it is looping 100's of thousands of time, but the read is still better.
 
Upvote 0
Thanks for your critic of my VBA programming. I don't have the power pivot add-in loaded at all. Only power query.

My code all runs fine if I unload the add-in power query, but errors out when I have it loaded. How can I suppress the error above?


If it helps........ When I eliminate all the code except for:

Code: 
ActiveWorkbook.Connections("Query from MS Access Database").Refresh
    ActiveWorkbook.Connections("Query from MS Access Database1").Refresh
    ActiveWorkbook.Connections("Query from MS Access Database2").Refresh
    ActiveWorkbook.Connections("Query from MS Access Database3").Refresh

I still have the same problem. Any ideas?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Error “Select method of Range class failed” when selecting range in VB.Net Excel workbook
Replies
2
Views
3K
rsmith19
R
N
  • Question Question
Powerpivot for Excel 2010 Error Msg Inputting Formula
Replies
0
Views
929
numberpro11
N
gino59
  • Question Question
PowerPivot issue when trying to enter DAX formula
Replies
0
Views
3K
gino59
gino59
S
  • Question Question
Frustrated: works on my PC but now others!
Replies
5
Views
2K
Andrew Poulsom
Andrew Poulsom
S
  • Question Question
PowerPivot unhandled exception error only when double-clicking Excel file
Replies
0
Views
3K
salsgang
S

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top