Eviews Excel Addin - Remove Data

For notifying us of what you believe are bugs or errors in EViews.
Please ensure your copy of EViews is up-to-date before posting.

Moderators: EViews Gareth, EViews Moderator

DPA
Posts: 6
Joined: Wed Sep 14, 2016 11:04 am

Eviews Excel Addin - Remove Data

Postby DPA » Mon Dec 05, 2016 12:53 pm

Hello,

I am trying to use the Eviews Add-in for Excel, and if I try to remove one connection, the rest of the connections get removed too. Is there a solution to this problem?

Thanks in advance!

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Eviews Excel Addin - Remove Data

Postby EViews Steve » Mon Dec 05, 2016 1:07 pm

You'll have to be more clear.

Are you using our add-in for Excel 97? Or for Excel 2007?

Which version of EViews do you have installed?

How are you removing the connection? Are you using our add-in Manage Connections window? If so, are you highlighting just the one connection and clicking the Remove button? I just tested this in EViews 9 and that window only removes the selected connection and leaves the others behind.

Before clicking Remove:
screen1.png
screen1.png (20.86 KiB) Viewed 5740 times


After clicking Remove:
screen2.png
screen2.png (21.69 KiB) Viewed 5740 times


Or did you try removing the connection from the Workbook Connections dialog? (you can display this by clicking on the Data tab and clicking on the Connections button). That also only removes the single connection you have selected when you click on Remove.

screen3.png
screen3.png (20.97 KiB) Viewed 5740 times



Steve

DPA
Posts: 6
Joined: Wed Sep 14, 2016 11:04 am

Re: Eviews Excel Addin - Remove Data

Postby DPA » Wed Dec 07, 2016 2:58 pm

Hi Steve,

I am using the 1997 Add-in on Excel 2013. It only happens sometimes, but when I try to remove one connection using Manage > Remove Data, my other connections get removed too. Is this a bug that other people went through as well?

Thanks

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Eviews Excel Addin - Remove Data

Postby EViews Steve » Wed Dec 07, 2016 3:35 pm

You're the first to report that problem as far as I know.

We actually don't have the facilities to put out a new version of the Excel 97 add-in anymore.

I took a quick look at the old code and I came up with a fix that you can apply directly to your XLA file.

To do so, do the following:

1. Make a copy of the "EViews Add-In.xla" file located in your EViews subdirectory. Place a copy onto your desktop.
2. Double-click the file to open in Excel.
3. Press CTRL-F11 to open the VBA editor.
4. In the left tree view, right-click "frmManage" under VBAProject->Forms and select "View Code".
5. In the code editor (on the right), select "cmdRemove" in the top left dropdown. The right dropdown should be on "Click".
6. Replace the entire function there with the following replacement:

Code: Select all

Private Sub cmdRemove_Click()
    Const METHODNAME As String = "cmdRemove_Click"
    Dim lsStatus As String
    Dim lsSheet As String
    Dim lsRange As String
    Dim liListIndex As Integer
    Dim wsht As Worksheet
    Dim qt As QueryTable
    Dim found As Boolean
    Dim li As Variant
   
    On Error GoTo errorhandler
   
    If lbxMain.ListIndex < 1 Then
        Exit Sub
    End If
   
    Dim vbRet As VbMsgBoxResult
    Dim msg As String
    vbRet = MsgBox("Clear data as well?", vbYesNoCancel Or vbDefaultButton2 Or vbQuestion, "Confirm")
    If vbRet = vbCancel Then
        GoTo exithandler
    End If
   
    Dim cnt As Integer
    cnt = lbxMain.ListCount
    'first get a list of all selected items...
    Dim selected As New Collection
    For liListIndex = cnt To 1 Step -1
        If lbxMain.selected(liListIndex) Then
            selected.Add liListIndex
        End If
    Next
    For Each li In selected
        lsRange = lbxMain.List(li, 1)
       
        Set qt = GetQT(lsRange, wsht)
       
        If Not qt Is Nothing Then
            If vbRet = vbYes Then
                qt.ResultRange.Clear
            End If
           
            'because this qt might be under a listobject,
            'we'll call a common routine to delete it
            Call DeleteQT(wsht, qt)
            lbxMain.RemoveItem li
        End If
    Next
   
exithandler:
    On Error Resume Next
    Exit Sub
   
errorhandler:
    Dim res As VbMsgBoxResult
    res = EV_DisplayError(METHODNAME, lsStatus, Err.Description, Err.Number)
    Select Case res
        Case vbRetry
            Stop
            Resume
        Case vbAbort
            Resume exithandler
        Case vbIgnore
            Resume Next
    End Select
End Sub


7. Once replaced, click on the Debug menu/Compile VBA Project to make sure there are no compile errors.
8. Finally, click the Save icon to save the changes. Close the editor window and close Excel. If Excel asks to save changes, click Yes.
9. Copy the XLA file back into the EViews subdirectory.
10. Run EViews, then type in the command "REGCOMPONENTS" to open the Register Components dialog. Click "Yes (All)" to re-register everything. This should overwrite your Excel's add-in with the new one you just edited.
11. Restart Excel and try your remove connections again.

Feel free to play around with this code. This version of the add-in was released just as an example of how you can use our OLEDB driver.

If you ever reinstall EViews again (and you still need this older addin), you should make a backup copy of the XLA file you edited so you can re-instate it after a re-install.

Steve

DPA
Posts: 6
Joined: Wed Sep 14, 2016 11:04 am

Re: Eviews Excel Addin - Remove Data

Postby DPA » Thu Dec 08, 2016 8:36 am

Thank you so much your help, Steve. I am going to try using your latest add-in (Eviews Excel Add-in for 2007), and try to see if the problem persists. If we find that the new add-in also automatically removes other connections, we will look into changing the xla file

Also, when we tried to add the 2007 addin, because the 1997 addin is still there, we get a warning message saying that both the addins cannot be there at the same time. Will using the new addin affect any data that was imported using the older ('97) addin?

EViews Steve
EViews Developer
Posts: 789
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Eviews Excel Addin - Remove Data

Postby EViews Steve » Thu Dec 08, 2016 8:40 am

I'm not sure why you would get an error saying you can't have both. I've seen both be active on a single Excel installation and they both work fine.

In the newer versions of Excel, go to File/Options/Add-Ins/Manage/Excel Add-ins/Go to turn off the Excel 97 add-in. Then go back into to File/Options/Add-Ins/Manage/COM Add-ins/Go to make sure the 2007 version is turned on. Then on your Add-Ins Ribbon tab, you should only see the newer EViews add-in.

Our add-ins only create Excel OLEDB connections in your spreadsheet so there should be no changes in your spreadsheets when you switch to the newer version.


Return to “Bug Reports”

Who is online

Users browsing this forum: No registered users and 18 guests