PQ connections getting renamed, data refresh failing

[McDemon]

Hi All,

I have a somewhat complex workbook with 21 connections. I have some web API calls, SQL DB queries and connections to a few local spreadsheets. All connections have background refresh disabled. There are a couple queries which I only need refreshed on file open so these have 'Refresh data when opening file' enabled and 'Refresh this connection on Refresh All' is disabled. I have no power pivots and there is nothing in the Data model.

There are two form buttons invoking msoFileDialogFilePicker to allow the user to select the local excel source files.
There are several parameterized queries to set unique variables for the queries which pull the data I actually present to the user after a few query merges. All of these queries have 'Refresh this connection on Refresh All' enabled. These are refreshed from another form button which calls:

Public Sub RefreshAll()
Dim ret As VbMsgBoxResult


If ActiveWorkbook.Sheets("Company Info").Range("C7").Value = "" Or ActiveWorkbook.Sheets("Company Info").Range("C10").Value = "" Then
  ret = MsgBox("One or more of the AUVIK file paths is empty.  Would you like to continue without the inclusion of AUVIK data?", vbYesNo)
 
   If ret = vbYes Then
   Application.StatusBar = "PLEASE WAIT WHILE THE DATA IS BEING REFRESHED"
   UserForm.Show vbModeless
   ActiveWorkbook.RefreshAll
   Application.Wait (Now + TimeValue("0:00:03"))
   Unload UserForm
 Application.StatusBar = Null
   End If
   
   Else
   Application.StatusBar = "PLEASE WAIT WHILE THE DATA IS BEING REFRESHED"
   UserForm.Show vbModeless
   ActiveWorkbook.RefreshAll
   Application.Wait (Now + TimeValue("0:00:03"))
   Unload UserForm
 Application.StatusBar = Null
End If


End Sub

Once this data is loaded into the destination worksheets, the user can click another form button to copy these sheets to a new workbook, select the destination path, delete all of the queries and connections and save the new file. The two worksheets for export are generated by merging and joining data from the 'connection only' queries. There is only one sheet which loads from a query, named 'Connectwise Configurations', when the user calls RefreshAll().

Public Sub CopySheets()
Dim fname As String
Dim fpath As String
Dim cn As WorkbookConnection
Dim qr As WorkbookQuery
fname = ActiveWorkbook.Sheets("Company Info").Range("C2").Value
fname = Replace(Replace(fname, ".", ""), ",", "")
fname = fname & " Reconciliation"
Worksheets(Array("Matching Between CW and NAble", "All from CW w match from nAble ")).Copy
With ActiveWorkbook


     NewFileFilter = "Excel Macro-Enabled workbook (*.xlsx), *.xlsx"
            NewFileFormat = 61
      
    myTitle = "Select a folder"
      FileSaveName = Application.GetSaveAsFilename _
            (InitialFileName:=fname, _
             FileFilter:=NewFileFilter, _
             Title:=myTitle)
If Not FileSaveName = False Then
 
      ActiveWorkbook.SaveAs Filename:=FileSaveName, _
                    FileFormat:=NewFileFormat


On Error Resume Next
For Each cn In ActiveWorkbook.Connections
    cn.Delete
Next
For Each qr In ActiveWorkbook.Queries
    qr.Delete
Next
                    
Application.ScreenUpdating = False
ActiveWorkbook.Close SaveChanges:=True
Application.ScreenUpdating = True
    Else
      MsgBox "File NOT Saved. User canceled the Save."
    End If




    
End With
End Sub

If a user does not choose to export the worksheets, everything above works flawlessly. However, if an export is performed, it succeeds only once. When a new set of parameters is selected and the user calls RefreshAll() via the form button, they receive an "Unexpected Error - Something went wrong. If the problem continues, please restart excel" error with copy details, Send a frown and close options. Commenting out the section:

On Error Resume Next
For Each cn In ActiveWorkbook.Connections
    cn.Delete
Next
For Each qr In ActiveWorkbook.Queries
    qr.Delete
Next

has no impact on the issue.

Looking at the data from the "Copy Details" I see:

Feedback Type:
Frown (Error)


Error Message:
The formula path 'Section1/Connectwise Configurations' does not exist.  <--'[B]Connectwise Configurations' is the original name of the query[/B]


Stack Trace:
   at Microsoft.Mashup.Host.Document.FormulaEditor.ConvertToTableAndRemoveStructuralColumns(PackageEditor packageEditor, FormulaPath formulaPath, Boolean sanitizeColumnNames)
   at Microsoft.Mashup.Host.Document.Storage.PackageStorageManager.GetPartsBytes(Byte[] partsBytes, Action`1 packageTransformation)
   at Microsoft.Mashup.Host.ProviderShared.TransformedMashupResourcePackage.RefreshTransformedPackage()
   at Microsoft.Mashup.Client.Excel.OleDbProvider.ExcelMashupDataSource.CreateMashupResourcePackage(String providerString, WorkbookIdentifierType workbookIdentifierType, String workbookIdentifier, String dataSourceLocation)
   at Microsoft.Mashup.Client.Excel.OleDbProvider.ExcelMashupDataSource.<>c__DisplayClass8_0.<CreateSession>b__0()
   at Microsoft.Mashup.Host.ProviderShared.MashupFileHost.Microsoft.Mashup.OleDbProvider.IMashupDocument.CreateEvaluator(Guid dialect, String commandText, Boolean forColumnInfo)
   at Microsoft.Mashup.OleDbProvider.MashupSession.CreateRowset(Guid dialect, String commandText, Boolean forColumnInfo)
   at Microsoft.Mashup.OleDbProvider.MashupCommand.CreateRowset(Boolean forColumnInfo)
   at Microsoft.Mashup.OleDbProvider.TracingCommand.CreateRowset(Boolean forColumnInfo)


Stack Trace Message:
The formula path 'Section1/Connectwise Configurations' does not exist.


Invocation Stack Trace:
   at Microsoft.Mashup.Host.Document.ExceptionExtensions.GetCurrentInvocationStackTrace()
   at Microsoft.Mashup.Client.UI.Shared.FeedbackErrorInfo..ctor(String message, Exception exception, Nullable`1 stackTraceInfo, String messageDetail)
   at Microsoft.Mashup.Client.Excel.Native.NativeUserFeedbackServices.RaiseErrorDialog(IWindowHandle activeWindow, IUIHost uiHost, FeedbackPackageInfo feedbackPackageInfo, Exception e, LocalizedString dialogTitle, LocalizedString dialogMessage, Boolean useGDICapture)
   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__DisplayClass14_0.<HandleException>b__0()
   at Microsoft.Mashup.Client.UI.Shared.UnexpectedExceptionHandler.HandleException(Exception e)
   at Microsoft.Mashup.Client.Excel.OleDbProvider.ExcelMashupClassFactory.TraceAndReportException(String method, Exception exception)
   at Microsoft.Mashup.OleDbProvider.TracingCommand.CreateRowset(Boolean forColumnInfo)
   at Microsoft.OleDb.Command.Microsoft.OleDb.ICommandText.Execute(IntPtr punkOuter, Guid& iid, DBPARAMS* pParams, DBROWCOUNT* pcRowsAffected, IntPtr& ppv)
   at Microsoft.OleDb.PInvokeInterop.CommandTextTypeInfo.Execute(IntPtr objHandle, IntPtr pUnkOuter, Guid& iid, DBPARAMS* pParams, DBROWCOUNT* cRowsAffected, IntPtr& ppv)




Supports Premium Content:
False


Formulas:




section Section1;


shared #"Connectwise Configurations (2)" = let    [B]***<-- Somewhere during the worksheet export, Excel made another iteration of many of the 'connection only' queries and appended '(2)' to the end of each of them, causing all subsequent refreshes to fail.  If I save the workbook, close and re-open it, all of the renamed connections appear with the '(2)' after their names and several have a blue question mark icon over the name.  These slowly disappear - one by one, but not until after the initial data load is completed. [/B]

The 'Load to' value is zeroed out and the connection properties say that it is not used in this workbook. By renaming the 'Connectwise Configurations' query back to the original name, the refresh succeeds but the export process triggers the whole renaming process again and increments my connection names to '(3)'.

I have googled for 2 days and can't find anything similar. The only hits were related directly to an issue with power pivots and Data models. The information in the Stack Trace is Greek to me. :Cry::Cry: Any direction is greatly appreciated!

Thanks in advance