Excel upload freezes with files saved when PAX is activated

With the enhancements to Excel upload in v2021.07, UX shows the Excel defined ranges on each tab. However, there appears to be a conflict if the Excel file contains certain hidden defined ranges, namely, the temporary ones that PAX creates. When a user clicks on the PAX add-in in the Excel ribbon, PAX creates some temporary hidden objects: (1) a “Cognos_Office_Connection_Cache” sheet and (2) a “non-visible” defined range on each sheet called “ID”.

Live example: https://ux.na.cubewise.com/Apliqo_Demo/#!/app/a10.v8

If I try to upload an Excel file with any of these non-visible defined ranges , the UX Excel Upload fails and gets stuck on the screen below:

The only workaround is to run VBA to remove these non-visible defined ranges from the Excel file before uploading. For example the code below removes these hidden ranges. Seeing that many customers have PAX in conjunction with UX, are there any modifications to the Excel upload component that can be done to deal with these non-visible ranges? Otherwise, any Excel that is saved with PAX activated will not be compatible with the UX Excel upload.

‌

Sub Remove_Hidden_Names()
  Dim xName As Variant
  Dim Result As Variant
  Dim Vis As Variant

  For Each xName In ActiveWorkbook.Names
   'If a name is not visible (it is hidden)...
   If xName.Visible = True Then
       Vis = "Visible"
   Else
       Vis = "Hidden"
   End If

   ' ...ask whether or not to delete the name.
   Result = MsgBox(prompt:="Delete " & Vis & " Name " & _
       Chr(10) & xName.Name & "?" & Chr(10) & _
       "Which refers to: " & Chr(10) & xName.RefersTo, _
       Buttons:=vbYesNo)

   ' If the result is true, then delete the name.
   If Result = vbYes Then xName.Delete

   ' Loop to the next name.
   Next xName
End Sub

‌

Comments (3)