Problems with Excel 2010 and the SmartView Addin

 2013-02-16    Problem solving    0    286

After installing the Oracle SmartView addin in Excel 2010, the Excel application started to behave rather strange.

Problem #1

When quitting the Excel application while a workbook/addin with a password protected VBProject is open (not an unusual situation), the user would be prompted from 1 to more than 100 times to enter a VBProject password. Rather annoying, especially since the user only see the name of the password protected workbook/addin and will probably not suspect that the problem is caused by the SmartView addin.

Problem #2

When the user opens a workbook that contains macros, and later closes this workbook, the workbook VBProject will still be visible in the Visual Basic Editor window, even if the VBProject is not password protected. If the user opens the same workbook more than once, a new "ghost copy" of the VBProject will be added to the Visual Basic Editor window each time. After working with Excel for a while the user might be informed that Excel is out of memory, and the Excel application will crash soon after this. Excel will also display the out of memory error message and crash if the user tries to click on one of the "ghost" VBProjects in the Visual Basic Editor window.

Problem solutions

The obvious solution to the problem is to disable or uninstall the SmartView addin if you don't need to use it. Another solution, provided by the helpful people at Oracle, is to use the menu SmartView Options, Extensions and disable the option Dynamic Query Building Environment. Problem #1 can also be avoided by adding the code below to the ThisWorkbook module in workbooks/addins with protected VBProjects:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application.VBE.MainWindow
        If Not .Visible Then
            .Visible = True ' show the VBE window
            .Visible = False ' hide the VBE window
        End If
    End With
End Sub

Showing the VBE window at least once will, for some strange reason, prevent the SmartView addin from annoying the user with repeated passwords prompts.