Tuesday, June 23, 2009
Peltier Technical Services, Inc., Copyright © 2023, All rights reserved.
In recent months I have encountered a particularly troublesome error. It occurs in Excel 2003 and 2007 (and perhaps in earlier versions, but I don’t recall any such cases). The error is heralded by an unusually unhelpful error message, even my Microsoft’s standards: Microsoft Visual Basic – System Error &H80004005 (-2147467259). Unspecified error.
Pardon my French, but WTF?
To rub salt into the wound, this is followed up with an Out of Memory error:
Sometimes these occur once, sometimes twice, when Excel is started, when a workbook with code is opened, or when an add-in is opened.
Association with RefEdit Controls
Closer examination sheds some light on the problem. It seems to be related to our old friend, the RefEdit control. If a workbook or add-in has a UserForm with a RefEdit, this error may occur. When the error occurs, it may be impossible to view the UserForm in the VB Editor, or if the UserForm can be viewed, any RefEdits have been stripped off the form. UserForms without RefEdit controls can be viewed with no problems.
On a computer that experiences the problem, the RefEdit library (refedit.dll) is not available in the references under the VB Editor’s Tools menu,and sometimes it turns up as a MISSING reference in this list. The RefEdit library is also not found in the list of Additional Controls which can be added to the VBE’s Controls Toolbox. If the RefEdit control had been present on the Controls Toolbox, its place may be taken by a gray square with a tooltip of “Unknown”.
Interestingly enough, the RefEdit library is present on the machine, in the Microsoft Office program directory. But the library is apparently not accessible.
There have been some recent changes in the RefEdit control, I believe on more than one occasion, but I have never documented the changes. There are no outwardly visible differences in the control, but the file size has changed, the file created or modified date has changed, and the file version has changed.
History
This famous Unspecified Error, its hex code 80004005 burned into many a programmer’s retinas, has been around for a long time, in Visual Basic 6 and probably earlier. It has been blamed on many factors, many of which seem like wild guesses, and most of which are irrelevant to our situation here. There are a lot of instances in which this error occurred in projects which dealt with retrieval of data from data controls or databases (Access, Oracle, SQL Server, and others). This error has also been associated with ODBC, ADO, IMAP, ASP and IIS, and DLLs from Microsoft (INET and HTML controls) and third parties. I suspect that this is a catch-all error message when there is an internal error in VB, when there is no specific information but the error is somehow associated with various controls or libraries.
At least one Microsoft Knowledge Base article discusses this error in the context of Excel. OFF2000: “System Error &H80004005 (-2147467259)” Running Macro with UserForm attributes the error to opening a file with a userform that contains a Microsoft DirectAnimation Sprite control (daxctle.ocx), whatever that is. Apparently that was the Office 2000 version of the problem, and in Office 2003/2007 it’s kicked off by a RefEdit control.
Google finds innumerable links to this error in web sites and newsgroups, related to Visual Basic, Office, and recently, Excel. I have spent dozens of hours poring through search results looking unsuccessfully for relief.
What makes this problem so much harder to document and troubleshoot is that it does not occur on all computers which have had nominally the same upgrades. One user will be hobbled by this error, while a user in the adjoining cubical will show no signs of it. I have had substantially identical VMs on the same computer, some with the problem, some immune.
Remedies
This problem has surfaced among users of my charting utilities, but usually I’ve been able to fix it. Also, one of my clients has a large Excel add-in package which has experienced this error. So far I think we’ve successfully treated all infections. (And no, I’m not the Excel Doctor.)
I have tried a wide range of remedies. A few involve chicken’s blood at full moon, but most involve various Windows and Office activities. Some remedies work some of the time, but what works on one computer may not work on another.
Because this problem was sporadic when I first encountered it, I didn’t document the exact steps taken to diagnose and remedy the issue. I’ve generally followed a brute force approach until recently.
In mild cases, deleting all files in the user’s temp directory and in the C:Documents and SettingsUser NameApplication DataMicrosoftForms directory will clear up the problem. This is all that my main machine needed, but generally it’s like trying to boil the ocean.
Reregistering the refedit.dll does not seem to help. I have tried replacing an older version of this library with a newer version from another computer, but I don’t recall this ever having helped.
Lately it seems that almost all cases in Excel 2003 are fixed using the Help menu’s Detect and Repair command, so this is my first step in treating the malady. Excel 2007 has a similar command deep under the Office Orb (Excel Options > Resources > Run Microsoft Office Diagnostics > Diagnose). I’ve used this command, but it has never given me the same warm fuzzy feeling of a Detect and Repair operation. However, just today an end user ran the diagnostics, and the problem was cleared up.
There are a couple preventative measures which seem to make add-ins resistant to the unspecified error. Open the add-in in Excel 2000, uncheck the reference to the RefEdit library, compile the code (ignoring compile errors related to new Excel 2002-2003-2007 syntax), and save and close the add-in. The compile step may not be necessary. I’ve also removed RefEdit controls and replaced them in 2000, but this seems to be unnecessary (as well as tedious).
I’ve seen two or three cases in which these last few steps did not seem to work. But in at least one of these cases, I’m not sure the end user followed instructions and actually ran the Detect and Repair or Diagnostic command.
Update 23 June 2009
Bob Flanagan has shared a link to his detailed protocols for fixing this and other problems with Office installations at What to do if Excel add-ins do not run. Thanks, Bob.
Other Experiences
Have you encountered this problem? Have you documented it? Have you successfully treated it?
If so, I want to hear about it. Please leave a comment with as many details as you can muster.
- Remove From My Forums
-
Question
-
I have an error that I can not make sense of. I open a database connection and recordset from table1 and then I close the recordset and reopen a new recordset from table2. The code works for table1 but fails with an automation error when i try to open table2.
I have checked spell errors to see if my table names match and i did not find anything wrong.Private Function Fn_GetFastenersStockAlerts(ByVal strPartNumber As String, ByVal dbConn As ADODB.Connection) As FastenerClass 'Dim dbConn As ADODB.Connection Dim dbRs As ADODB.Recordset Dim dbRs2 As ADODB.Recordset Dim dbQuery As String Dim dbPath As String Dim strFasteNerSize As String Set Fn_GetFastenersStockAlerts = New FastenerClass Set dbRs = New ADODB.Recordset dbQuery = "SELECT * FROM STOCKFASTENERS WHERE PARTNUMBER = '" & strPartNumber & "';" dbRs.Open dbQuery, dbConn If dbRs.EOF <> True And dbRs.BOF <> True Then Fn_GetFastenersStockAlerts.lnLength = dbRs!Length Fn_GetFastenersStockAlerts.lnStockQty = dbRs!QTYONHAND Else Fn_GetFastenersStockAlerts.lnLength = 0 Fn_GetFastenersStockAlerts.lnStockQty = 0 End If dbRs.Close Set dbRs = Nothing strFasteNerSize = Fn_GetFastenerSize(strPartNumber) Set dbRs2 = New ADODB.Recordset dbQuery = "SELECT MAXLENGTH FROM STOCKFASTNERMAXLENGTH WHERE SIZE = '" & strFasteNerSize & "';" dbRs2.Open dbQuery, dbConn If dbRs2.EOF <> True And dbRs2.BOF <> True Then Fn_GetFastenersStockAlerts.lnMaxLength = dbRs!Length Else Fn_GetFastenersStockAlerts.lnMaxLength = 0 End If dbRs2.Close End FunctionIt is successful on dbRs.Open I get the records that I want out of it. It errors out on dbRs2.Open with Automation error and Unspecified Error.
Any help is appreciated.
Thank you
Answers
-
STOCKFASTNERMAXLENGTH it is a table. Where Size is my primary key. I got a dent in my head from banging on the wall but still no solution. I am just unable to understand why the same line of code works for a different table but not this one.
It’s not obvious to me what the problem is, either. I would suggest you try two approaches to debugging the problem. First, try bracketing and fully qualifying fielding names (especially SIZE), in case there’s a conflict with a reserved word:
dbQuery = «SELECT SFL.[MAXLENGTH] FROM STOCKFASTNERMAXLENGTH SFL WHERE SFL.[SIZE] = ‘» & strFasteNerSize & «‘;»
If that doesn’t work, you might try running the query using DAO instead of ADO, like this:
Dim rs2 As DAO.Recordset
Set rs2 = CurrentDb.OpenRecordset(dbQuery)
(with other changes as needed), just to see if DAO gives you a more helpful error message. That code example assumes that the table STOCKFASTNERMAXLENGTH is in the current database where the code is running. If the table is actually in some
external database, you’d have to use the DAO OpenDatabase method to open a Database object on it, and use that object as the base for the OpenRecordset call.
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html-
Marked as answer by
Tuesday, October 11, 2016 8:38 PM
-
Marked as answer by
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Contact US
Thanks. We have received your request and will respond promptly.
Log In
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips Forums!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts - Keyword Search
- One-Click Access To Your
Favorite Forums - Automated Signatures
On Your Posts - Best Of All, It’s Free!
*Tek-Tips’s functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Students Click Here
ComboBox.Clear unspecified errorComboBox.Clear unspecified error(OP) 26 Apr 04 10:45 I have a combobox on a userform that is populated according to the code below The user then makes adjusments to certain entries — the code is then re-run to produce a new list with the corrected entry removed. To do this I use the following: AnalCodesUserForm.JobNumberComboBox.Clear When the code re-runs though, the code breaks and I get the unspecified error message and it highlights the above line? Case «None» Has anyone seen something like this before? I couldn’t find much about combobox.clear questions. Red Flag SubmittedThank you for helping keep Tek-Tips Forums free from inappropriate posts. |
Join Tek-Tips® Today!
Join your peers on the Internet’s largest technical computer professional community.
It’s easy to join and it’s free.
Here’s Why Members Love Tek-Tips Forums:
Talk To Other Members- Notification Of Responses To Questions
- Favorite Forums One Click Access
- Keyword Search Of All Posts, And More…
Talk To Other MembersRegister now while it’s still free!
Already a member? Close this window and log in.
Join Us Close
Troubleshooting
Problem
After receiving a Microsoft update(s), users trying to access TM1 Perspectives see the following error message:
Symptom
Microsoft Visual Basic
Automation Error
Unspecified Error
Cause
Environment
Excel 2007 on Windows
Resolving The Problem
*** Excel must be closed before performing these steps***
Unregister and re-register the mscomctl.ocx file.
NOTE: The following must be performed on the machine where problem is occurring when logged into the machine as a user in the local Administrators group.
Open a command prompt window.
When applicable (Windows Vista, Windows 7, Windows Server 2008 R2), you must use the «Run as Administrator» mode/option when opening the command prompt window
Run the appropriate commands indicated below.
For 64 bit machines:
regsvr32 /u C:WindowsSysWOW64mscomctl.ocx
regsvr32 C:WindowsSysWOW64mscomctl.ocx
For 32 bit machines:
regsvr32 /u C:WindowsSystem32mscomctl.ocx
regsvr32 C:WindowsSystem32mscomctl.ocx
Once the file has successfully re-registered, start Perspectives to confirm whether this has resolved the problem.
For additional information including for other Operating system and Excel versions, please see the following Technote:
http://www-01.ibm.com/support/docview.wss?uid=swg21438688
OR
Upgrade to Office 2010
[{«Product»:{«code»:»SS9RXT»,»label»:»Cognos TM1″},»Business Unit»:{«code»:»BU053″,»label»:»Cloud u0026 Data Platform»},»Component»:»TM1 Perspectives»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»9.5.2;10.1.0″,»Edition»:»All Editions»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}},{«Product»:{«code»:»SSDL22″,»label»:»IBM Planning Analytics Express»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Cognos Xcelerator Perspectives»,»Platform»:[{«code»:»»,»label»:»Windows 2003 server»},{«code»:»PF033″,»label»:»Windows»}],»Version»:»10.1;10.2.1″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]