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 Excel Addin - Remove Data
Moderators: EViews Gareth, EViews Moderator
-
- EViews Developer
- Posts: 790
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
Re: Eviews Excel Addin - Remove Data
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:
After clicking Remove:
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.
Steve
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:
After clicking Remove:
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.
Steve
Re: Eviews Excel Addin - Remove Data
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
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 Developer
- Posts: 790
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
Re: Eviews Excel Addin - Remove Data
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:
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
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
Re: Eviews Excel Addin - Remove Data
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?
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 Developer
- Posts: 790
- Joined: Tue Sep 16, 2008 3:00 pm
- Location: Irvine, CA
Re: Eviews Excel Addin - Remove Data
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.
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.
Who is online
Users browsing this forum: No registered users and 7 guests