Run-time error '-2147467259 (80004005)'

T

TTUK

Board Regular
Joined
Apr 5, 2012
Messages
137
Hello all & Happy New Year!

I am getting a run-time error as per the title of '-2147467259 (80004005)'
It says on the VB error an 'Automation error' and 'Unspecified error'.

About the script -
It is activated when a user selects a cell containing the words 'Raise Follow-up Report', which is a hyperlink which runs this code below of Followup_Confirm.
Now probably, this isn't the prettiest code so any help welcomed!

Code: 
Sub Followup_Confirm()




    Dim Msg As String, Ans As Variant


               Msg = "Confirm you would like to raise a Follow-up form"


    Ans = MsgBox(Msg, vbYesNo)


    Select Case Ans


    Case vbYes
        Application.DisplayAlerts = False
        ' Copy date and paste in cell on row
        ActiveCell.FormulaR1C1 = "Form Raised"
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=R4C54"


        Selection.Copy


        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


        ' Select the worksheet that is to be saved
        Sheets("IIR_temp").Select
        
        
        ActiveSheet.Copy ' Copies active sheet to a new workbook
             
        Range("A2:BG16").Select
        Selection.Copy
        
        Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
         
        'Import CLS file to new workbook
        With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
             .DeleteLines StartLine:=1, Count:=.CountOfLines
            .AddFromFile "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIRSaveUpdate.txt"
        End With
        
        'Import BAS file to new workbook
        Dim filename As String
        filename = "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIR_Exchange.bas"
        Application.VBE.ActiveVBProject.VBComponents.Import (filename)


Application.DisplayAlerts = False


Application.CutCopyMode = False


[COLOR=#ff0000]ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52[/COLOR]
ActiveWorkbook.Close


Sheets("Register").Select
    
End Select
End Sub

It is debugging on the line - ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52
The script creates a new worksheet and paste's certain information into it.
Then saves (above) as the cell value of A1.

The cell A1 is referencing another workbook with the file path in - could this be the issue?

I appreciate any help to fix this!

Thank you
 
Sort by date Sort by votes
Hi RoryA,

I think I have narrowed down the cause of the error code.
In the IIE_Exchange.bas file which gets imported to create the module, there are 2 Functions.

Code: 
Function LastAuthor()LastAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Function


Function LastSavedTimeStamp() As Date
LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

If I remove these, then the code works. However, if I keep them in it fails.

The entire code in the .bas file is -
Code: 
Attribute VB_Name = "IIR_Exchange"Sub Export_IIR()
Application.EnableEvents = False
Application.ScreenUpdating = False
    
Dim x As Workbook
Dim y As Workbook
Dim lastRow As String


Call LastAuthor
Call LastSavedTimeStamp


Application.DisplayAlerts = False


'## Open both workbooks first:
Set x = ActiveWorkbook
Set y = Workbooks.Open("\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\AIR_Exchange.xlsx")




'Now, copy what you want from x:
x.Sheets("IIR_temp").Range("BI2:BK2").Copy


'Paste last row
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1




'Now, paste to y worksheet:
y.Sheets("AIR_Exchange").Range("A" & lastRow).PasteSpecial xlPasteValues
  With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With


Application.EnableEvents = False
Application.ScreenUpdating = False




'Close y:
y.Save
y.Close


Application.EnableEvents = True
End Sub


Function LastAuthor()
LastAuthor = ActiveWorkbook.BuiltinDocumentProperties("Last Author")
End Function


Function LastSavedTimeStamp() As Date
LastSavedTimeStamp = ActiveWorkbook.BuiltinDocumentProperties("Last Save Time")
End Function

The functions basically allow for the Last Author to be saved in BK2 and the date and timestamp to be entered in the BJ2.
This is for each time the document is saved.
The date is then copied and pasted into the Exchange workbook.

Know any way I can get around the Functions?!

When running this the file actually saves and is in the location, and working as intended.
The code just falls over showing the error

'-2147467259 (80004005)'
'Automation error' and 'Unspecified error'.


Thanks!
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
In an update to this. I have removed the functions following what was written above.
I've gotten the code to work with the following 2 imports.

IIR_Exchange.bas
Code: 
Attribute VB_Name = "IIR_Exchange"Sub Export_IIR()
Application.EnableEvents = False
Application.ScreenUpdating = False
    
Dim x As Workbook
Dim y As Workbook
Dim lastRow As String


Application.DisplayAlerts = False


'## Open both workbooks first:
Set x = ActiveWorkbook
Set y = Workbooks.Open("\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\AIR_Exchange.xlsx")




'Now, copy what you want from x:
x.Sheets("IIR_temp").Range("BI2:BK2").Copy


'Paste last row
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1




'Now, paste to y worksheet:
y.Sheets("AIR_Exchange").Range("A" & lastRow).PasteSpecial xlPasteValues
  With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With


Application.EnableEvents = False
Application.ScreenUpdating = False




'Close y:
y.Save
y.Close


Application.EnableEvents = True
End Sub

and, the IIRSaveUpdate.txt
Code: 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)Dim ws As Worksheet
Set ws = ActiveSheet
With ws.Range("BJ2")
.Value = Now
.NumberFormat = "dd/mm/yyyy hh:mm"
End With
ws.Range("BK2") = Environ("username")


Call Export_IIR


End Sub

I'm no longer getting the error as per title, but now getting the error.
Run-time error '91': Object variable or With block variable not set

The link is still the same which is selected.
ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52
of the module which runs and executes the .bas and .txt imports to the newly created Workbook.

Code: 
Sub Followup_Confirm()



    Dim Msg As String, Ans As Variant


               Msg = "Confirm you would like to raise a Follow-up form"


    Ans = MsgBox(Msg, vbYesNo)


    Select Case Ans


    Case vbYes
        Application.DisplayAlerts = False
        ' Copy date and paste in cell on row
        ActiveCell.FormulaR1C1 = "Form Raised"
        ActiveCell.Offset(0, 1).Select
        ActiveCell.FormulaR1C1 = "=R4C54"


        Selection.Copy


        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


        Sheets("IIR_data").Select
        Range("A6").Select
        Selection.Copy
        Sheets("IIR_temp").Select
        Range("A1").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Application.CutCopyMode = False


        
        ' Select the worksheet that is to be saved
        Sheets("IIR_temp").Select
        
        
        ActiveSheet.Copy ' Copies active sheet to a new workbook
             
        Range("A2:BG16").Select
        Selection.Copy
        
        Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
         
        'Import BAS file to new workbook
        Dim filename As String
        filename = "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIR_Exchange.bas"
        Application.VBE.ActiveVBProject.VBComponents.Import (filename)
        
         
        'Import CLS file to new workbook
        With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
             .DeleteLines StartLine:=1, Count:=.CountOfLines
            .AddFromFile "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIRSaveUpdate.txt"
        End With




Application.DisplayAlerts = False


Application.CutCopyMode = False


[B]ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52[/B]
ActiveWorkbook.Close


Sheets("Register").Select
    
End Select
End Sub
 
Upvote 0
Hi All,

This issue has now been solved.
I figured it out and the cause of the issue was that

Code: 
[COLOR=#333333]Application.EnableEvents = False[/COLOR]

Had not been set back to True and wouldn't allow the next command to run successfully causing the error.

Thank you to RoryA to helping to try and resolve this!


Thanks,
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Compile error - Trying to send an email using VBA/Excel
Replies
6
Views
575
gorgkhan
G
2
  • Solved
Run-Time Error 1004 - Select method of range class failed
Replies
5
Views
995
DanteAmor
DanteAmor
A
  • Question
Run time error 9: subscript out of range when opening a workbook in VBA
Replies
5
Views
3K
Jimmywick
J
2
  • Solved
Function RangetoHTML(rng As Range) Run-time error 1004
Replies
4
Views
4K
yuliuyan07
Y
S
  • Question
Run-time error '1004': Method 'SaveAs' of object'_Workbook' failed
Replies
5
Views
3K
Joe4
Joe4

Forum statistics

Threads
1,215,501
Messages
6,125,169
Members
449,212
Latest member
kenmaldonado

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top