Free Microsoft Excel 2013 Quick Reference

Automation error unspecified Results

Hi there,

I have a workbook with a lot of VBA in it. It works fine on several other computers, but it no longer works on mine (I wrote the code, so it did work at one point in time). Now, when using certain routines, I get "Automation Error: Unspecified Error" quite frequently.

Most of the time this happens, it shows Workbook_SheetBeforeRightClick as the problem. I've tried removing the code from there and replacing it with a simple msgbox, and it still errors. I also tried removing the entire routine, then re-adding it through the Combo Boxes (where you choose the object, then choose the event). Just choosing the event there raises the error.

There has to be something wrong with my PC/Excel version, but I've reinstalled the program several times to no avail... Help!


I have written an Excel Add-In that adds an item to the
standard Excel menu, with various functions that are
called by the sub-menu items. I last worked on the code in
July 2003, at which time it compiled and executed without
problems. I now need to work on the code again, but it no
longer compiles. When I attempt to compile from the Visual
Basic window, it fails on the first line (Private
mobjExcelBook As Excel.Workbook), with the
message "Compile Error: Automation error". If I comment
out this line, it fails on the next object declaration
line: Private mcolSheets As VBA.Collection. The object
browser shows these objects, and the References are
correct, and unchanged since the previous work that
compiled. (Microsoft Excel 9.0 Object Library, EXCEL9.OLB,
and others)

When I shut down the Add-In, I get another error message
entitled "Microsoft Visual Basic", with the message:
Automation Error, Unspecified Error, and then the VBA
window opens again in debug mode.

Now the clincher: the code actually executes correctly
when I invoke the menu items, and does not throw any

I am developing on Windows 2000 Professional (5.0.2195 SP-
4), and using Excel 2000 (9.0.3821 SR-1). I open and run
this Add-In not as a selected Add-In in Excel, but by
simply double-clicking the .xla file.

I do not recall installing any other software since the
last time I worked on this code, other than a couple of
Windows updates. I have run Regclean.exe a couple of times
since these errors first appeared, and allowed it to
repair the registry.

The only other hint might be that I wrote a bit of Windows
API code when I was working on the Add-In in July. But at
the time, this compiled and executed correctly too.

Thanks for any help.


Don't know for sure, but go to the VB Editor
Tools menu > References
see if any are marked as missing.

If they are, uncheck them and try again.


"Francisco J. Melendez" > wrote in
message ...
> We have a spreadsheet with macros developed internally.
> The macro uses ole automation etc. The workbook functions
> properly in all machines except for one, where we get
> a "VBA Error: Automation error, Unspecified error" popup.
> The user is clicking on a form button that clears a
> predifined range. It works fine in all other machines.
> Have run repair for Office, checked all patches are
> installed. Same office configuration. No idea this does
> not show in the knowledge base when i search for it. Alll
> machines running windows 2000 and office 2000.

I have a friend that is using an Excel Spreadsheet and they have recently upgraded their computer.

Now when they open a file we have been passing back and forth. They get an error:
Automation Error
Unspecifid Error
Sometimes they can get past that, but when they go to save they get: "Cannot Save".
I briefly looked at the VB code, with not luck.

This is specific to her computer, so I am wondering if something else needs to be setup or configured properly. We have tried uninstalling, etc.

Any other ideas, or things we can look at?



I have created a macro that can load text data into a worksheet then perform
some various calculation and make some graphs from the data.

The macro works fine on my computer. However, when it is opend on another
computer an error "Automation Error, Unspecified Error" pops up. The
buttons that I created to begin the calculations and charting are displayed
but when they are clicked another "Automation Error" occurs.

I am on a Win2K machind runngin Excel 2000 9.0.3821 SR1, the other computer
is WinXP Pro Excel 2000 9.0.2720.

Is there some type of setting that excel need turned on to run macros?

Any advice would be welcome.




I have a folder of workbooks. I'm trying to cycle through them and produce graphs for each of the columns, but I keep running into a whole series of frustrating errors and I can't work this one out.

The line I keep having trouble with is:

.Chart.SetSourceData Source:=wbOpen.Worksheets("Data1").Range(wbOpen.Worksheets("Data1").Cells(11, cycleCounter),
wbOpen.Worksheets("Data1").Cells(103, cycleCounter)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get the error:
'Run-time error '-2147467259 (80004005)':
Automation error
Unspecified error

I usually run this from the code screen. At the moment, it stops at the aforementioned line on the first chart of every new file that is opened. If I "Debug" then click on my spreadsheet where the charts are going and then go back to the code, it allows me to continue.

Here's the code:

    Dim wbOpen As Workbook 
    Dim wbNew As Workbook 
     'Change Path
    Dim strPath As String 
    Dim strExtension As String 
    Dim cycleCounter, rowCounter, numColumns, numRows As Integer 
    Dim srcRange, wbName As String 
     'Comment out the 3 lines below to debug
     'Application.ScreenUpdating = False
     'Application.Calculation = xlCalculationManual
     'On Error Resume Next
    strPath = ThisWorkbook.Worksheets("Reference").Range("B1").Value 
    If Right(strPath, 1) = "" Then 
         ' Do nothing
        strPath = strPath & "" 
    End If 
    ChDir strPath 
     'Change extension
    strExtension = Dir("*.xls") 
    Do While strExtension  "" 
        Set wbOpen = Workbooks.Open(strPath & strExtension) 
        wbName = wbOpen.Name 
        numColumns = Application.WorksheetFunction.CountA(wbOpen.Worksheets("Data1").Range("1:1")) 
        ThisWorkbook.ActiveSheet.Name = "Graphs " & wbName 
        For cycleCounter = 2 To numColumns + 1 
            numRows = Application.WorksheetFunction.CountA(wbOpen.Worksheets("Data1").Range("A:A")) 
            With ThisWorkbook.Worksheets("Graphs " & wbName).ChartObjects.Add(Left:=0, Width:=600, Top:=0 + (cycleCounter -
2) * 250, Height:=225) 
                .Chart.SetSourceData Source:=wbOpen.Worksheets("Data1").Range(wbOpen.Worksheets("Data1").Cells(11,
cycleCounter), wbOpen.Worksheets("Data1").Cells(103, cycleCounter)) 
                .Chart.ChartType = xlLine 
                .Chart.HasLegend = False 
                .Chart.HasTitle = True 
                .Chart.ChartTitle.Text = "=[" & wbName & "]Data1!R1C" & cycleCounter 
                .Chart.Axes(xlValue, xlPrimary).HasTitle = True 
                .Chart.Axes(xlValue, xlPrimary).AxisTitle.Text = "=[" & wbName & "]Data1!R2C" & cycleCounter 
                .Chart.SeriesCollection(1).XValues = "=[" & wbName & "]Data1!R11C1:R103C1" 
            End With 
        Next cycleCounter 
        strExtension = Dir 
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    On Error Goto 0 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hi All,

I am getting an unspecified automation error with my IE object.

It is declared at the top of my Module modGeneral using

Sub SomeSub() 
    Set ie = createobject("internetexplorer.application") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and for some reason when my program passes control to a sub in another module(modViewShipment), it seems to lose it.

Does anybody know what might be causing this?

PS if need be I can post more of my code. I wasn't sure what was relevant and what was not.

Many thanks to anyone that reads this, and many more thanks to the one that solves it.


I have 2 Excel spreadsheet statements in a workbook with Invoices and Amount on each. Sheet 1 is from our client and sheet 2 is from our Accounts department.

I am trying to Match the common Invoices in sheets 1 & 2 and put the columns onto Sheet 3. The following code was working fine untill yesterday afternoon!

Option Explicit
Public Sub Match_Invoices()
Dim intLoop As Integer
Dim objADODB_Connection As Object
Dim objRecordset As Object
Dim strSQL As String

On Error GoTo ErrorHandler
strSQL = ""
strSQL = strSQL & "SELECT "
strSQL = strSQL & "[Sheet1$].*, "
strSQL = strSQL & "[Sheet2$].* "
strSQL = strSQL & "FROM "
strSQL = strSQL & "[Sheet1$] "
strSQL = strSQL & "INNER JOIN "
strSQL = strSQL & "[Sheet2$] "
strSQL = strSQL & "ON "
strSQL = strSQL & "[Sheet1$].[Inv No] = [Sheet2$].[Doc Ref] "

Set objADODB_Connection = CreateObject("ADODB.Connection")

objADODB_Connection.Connectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & ActiveWorkbook.FullName & ";Extended Properties=Excel 8.0;"

Set objRecordset = objADODB_Connection.Execute(strSQL)
' At this point the Error is received. Error at the bottom of this coding!


For intLoop = 0 To (objRecordset.Fields.Count - 1)
Worksheets("Sheet3").Cells(1&, intLoop + 1) = objRecordset.Fields(intLoop).Name
Next intLoop

Worksheets("Sheet3").Range("A2").CopyFromRecordset objRecordset


Set objRecordset = Nothing
Set objADODB_Connection = Nothing

MsgBox Err.Number & " : " & Err.Description
End Sub

The following Message is received with the error_handler :-
-2147467259 : The connection for viewing your linked Microsoft Excel Worksheet was lost.

The following Message is received without the error_handler :-
Run-time error ‘-2147467259 (80004005)’:

Automation error
Unspecified error

Any Idea what went wrong since yesterday !
Thanks in advance !

When opening Excel, I get multiple instances of this error in a Microsoft
Visual Basic error box. It also happens when I open an existing spreadsheet
and when I edit the worksheet.

There is no error number.

Everything seems to work fine, it is just annoying as heck.

I have tried a repair, and a repair/reinstall. Neither has worked.

I have not uninstalled Office and reinstalled it.

Can anyone help?

Thanks in advance.


I have some straight-up VB experience, but when it comes to VBA I am a
little shaky on a few concepts. I am hoping someone here can help me with a
problem I have run into.

I recently wrote a macro that consists of a few user forms. On most
machines, it runs exactly as intended. On two machines, I get error messages
"Automation Error. An unspecified error has occurred.". I took out my error
trapping hoping to catch the offending code line, but it appears to be
happening before any code is executing.

Thinking perhaps it has something to do with control references (I added the
MS dialog control, and MS Picture combo during design), I tried to add these
components from the users computer, but cannot save the workbook afterwards.
I assume since these controls show up on the list of available
references/controls, that they are properly registered? Is there a way I
must reference them in code? Or am I completely barking up the wrong tree?
At this point, I am not sure what is going on... can anyone offer some


My login macro was built in Excel 2007.
Everything was working with Excel 2003 and 2007, IE 6 and Windows XP.
Just recently, my computer was upgraded to Windows 7 with Excel 2010 and IE 8.

Now when I run the macro, it shows Run-time error '-2147467259 (80004005)': Automation error Unspecified error
The macro can load the website in IE but it won't input my userID and password anymore.

Can anyone help me out here?
I am thinking if it has something to do with the new Excel.

Thank you very much.

I am extracting data from Access using an ADODB connection, and I've been
having no problems with it for most times that I run it.

But for one time period that I'm running, on this line

Range("A1").Offset(1, 0).CopyFromRecordset Recordset

I get this error

"Automation error - unspecified error"

Yet it still does the copy (I think correctly). So I could just bounce this
back up from the error handler, but I'm concerned that it happened at all.

Any advice appreciated, and of course, if I have not provided enough
information, feel free to berate me and request more...


If I run the following in the VB editor:

ActiveChart.ChartType = xlColumnClustered 
ActiveChart.SeriesCollection(1).Points(1).DataLabel.Top = 49.157 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get and error saying: Run-time error -2147467259 (80004005)
Automation error
Unspecified errorHowever, if I step through the macro using F8 it works fine. Is there some kind of problem with my code or is this some sort of Excel bug?

Would like to add totals to stacked column chart in Excel 2007.

I have followed same steps listed here but cannot position data labels above column.

Excel only gives me the options Center, Inside End, and Inside Base.

I also tried setting label position with VBA:

    For Each s In co.Chart.SeriesCollection 
        If s.Name = "Totals" Then 
            For Each dl In s.DataLabels 
                dl.Position = xlLabelPositionOutsideEnd 
            Next dl 
        End If 
    Next s 
Next co 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Also tried the following without luck:

dl.Position = xlLabelPositionAbove 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
For each VBA example I get following error:

Run-time error '-2147467259 (80004005)':
Automation error
Unspecified errorIt seems like Microsoft has removed this positioning option for stacked column charts because it works fine for normal column charts. Anyone find a way to do this otherwise?

I am trying to connect to an Oracle 9 database using the ADODB class.

I get the following error immediately when calling ADODB.Open:

Run-time error '-2147467259 (80004005)':
Automation error
Unspecified error

I'm running Excel 2003, I have Oracle 10 installed on the work station and I'm connecting to and Oracle 9 server.

I can successfully run queries using MS query, just not using ADODB.

Any thoughts?


    Dim Cn As ADODB.Connection 
    Dim Server_Name As String 
    Dim Database_Name As String 
    Dim User_ID As String 
    Dim Password As String 
    Dim SQLStr As String 
    Dim rs As ADODB.Recordset 
    Set rs = New ADODB.Recordset 
    Server_Name = "" 
    Database_Name = "WDSTEST" 
    User_ID = "user" 
    Password = "pass" 
    Set Cn = New ADODB.Connection 
    Cn.Open "Provider=MSDAORA;Server=" & Server_Name & ";Database=" & Database_Name & _ 
    ";User ID=" & User_ID & ";Password=" & Password & ";" 
    rs.Open SQLStr, Cn, adOpenStatic 
    With Worksheets("Raw Data").Range("a1:z500") 
        .CopyFromRecordset rs 
    End With 
    Set rs = Nothing 
    Set Cn = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

I have lost my formula bar. By using the commandbars function etc, I know that it is enabled and invisible. However, when i try to make it visible i get the error

Run time error -2147467259 (80004005)
Automation error
Unspecified error

I don't think it is a syntax error since the same command works and makes other toolbars visible/invisible.

The syntax i used in the immediate pane is:-
commandbars("formula bar").VISIBLE=True

Any help would be appreicated since i really need to get the formula bar back



(remove nothere from the email address if mailing direct)

"VJ" > wrote in message
> Can somebody suggest me why I am getting "Unspecified Error" when the
> following line of code executes.
> Debug.Print
Application.Workbooks("WBName.xlt").VBProject.VBCo mponents.Item("FormName").
> Before opening the workbook, I am unlocking it which is working fine but
> some reason I am not able to get reference to the Properties collection.
> works fine sometimes when I run in the debug mode.
> Help would be appreciated.
> Thanks,

I am having a really difficult time with this code. I am trying to fill in some fields on an webpage in an effort to eventually retrieve some data. I have read lots of posts on the topic and somehow I'm still confused.
My code crashes on this line and gives an Automation error, Unspecified Error message:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
No matter what I try, I can't get past this line.
I have also tried:


If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help would be greatly appreciated.

    Dim appIE As SHDocVw.InternetExplorer 
    appIE.Visible = True 
    appIE.navigate "" 
    Do While appIE.Busy: DoEvents: Loop 
        Do While appIE.readyState  complete: DoEvents: Loop 
            With appIE.document.all 
                .Item("cmd").Value = "FINDADDR" 
                .Item("cmdTemp").Value = "FINDADDR" 
                .Item("searchtool").Value = "ADDR" 'Search by' dropdown
                .Item("stnum").Value = "2417" 'house #' field
                .Item("stdir").Value = "" 
                .Item("stname").Value = "ponce de leon" 'street name' field
                .Item("sttype").Value = "BLVD" 'street type' field
            End With 
        End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Hello All

I am running Office2000 under Windows 2000 professional.

Until recently I have been using an excel add-in without a problem, but then
on Tuesday it stopped working - whenever I load excel I get a pop-up window
headed 'Microsof Visual Basic' and the message is "Automation Error -
Unspecified Error". The options are OK or Help - but clicking Help gives no
further info at all! Clicking OK load the 'Microsof Visual Basic' window,
which, when I close it, tells me that this will stop the debugger. I am then
returned to excel, which works fine except without the add-in.

I know that the add-in file itself is OK, because many other workstations
use it with no problems. Also my registry keys look OK - same as at other
workstations where the add-in works, and my list of references match those
at the other worstations. I have uninstalled and reinstalled the add-in a
number of times, same result.

The "Automation Error - Unspecified Error" message error occurs at the
follwing times:

at the very end of running the installation of the add-in
when initially loading excel (at which time the add-in is ticked in the list
under tools > addins
when I click on Tools > Synergy Reporting Path (this appears as the first
item under tools)
when I click OK after deselecting the add-in (in the list under tools >

Having deselected the add-in, excel works fine.

I hope someone can suggest what the problem might be - it's got me (and more
significantly the author of the add-in) stumped!

Many thanks
Leslie Isaacs

I am trying to use the code shown below to get information to transfer from
Excel to Access. When I hit "Run", I receive a Run Time Error with this code
'-2147467259(80004005)': Automation Error, Unspecified Error
In my references, I have selected: VBA, Excel 11.0 Object Library, OLE
Automation, MS Office 11.0 Object Library, MS Forms 2.0 Object Library, MS
ActiveX Data Objects 2.0 Library.
I don't know where to choose between ADO and DAO for data import or export, too.
If anyone has any ideas, please help!

Sub ADOFromExcelToAccess()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=L:AccessCostTracking.mdb;"
    Set rs = New ADODB.Recordset
    rs.Open "TotalCostSummary", cn, adOpenKeyset, adLockOptimistic, adCmdTable
    r = 2
    Do While Len(Range("A" & r).Formula) > 0
        With rs
            .Fields(Quote) = Range(A & r).Value
            .Fields(Job) = Range(B & r).Value
            .Fields(Est) = Range(C & r).Value
        End With
        r = r + 1
    Set rs = Nothing
    Set cn = Nothing
End Sub