Automation Error - Object invoked has disc . . . .

[Staticbob]

Guys,

I am having massive problems with this f***ing error again !

Automation error - The Object invoked has disconnected from its clients

The code is posted below. This error always occurs on the saveas line. I am doing this on lots of different sheets, this is just an example of where it is erroring now. This code has been happily working for weeks prior to this, but when the error occurs once, it seems to 'stick'.

I cannot find a resolution for this anywhere. It is not machine specific, so can't be temp folder / resources.

Any help / advice / solution would be GREATLY appreciated !!!

If anybody would like to volunteer to take a look at the file and see the error occuring then feel free !!

Thanks in advance,
Bob

Private Sub UserForm_Activate()
'Saves Notice and Updates Log
Application.ScreenUpdating = False
Dim netNoticelocation As String
Dim docname As String
Dim savepath As String
On Error GoTo errorlog
Sheets("Notice log").Unprotect PASSWORD:="florence"
Sheets("Notice").Unprotect PASSWORD:="florence"
netNoticelocation = Sheets("Maintenance Information").Range("C9").Value
pno = Sheets("directory").Range("G3").Value
'copy data
With Sheets("Notice log")
.Range("F65536").End(xlUp).Offset(1, 0) = Range("J1")
.Range("F65536").End(xlUp).Offset(0, -3) = Range("B5")
.Range("F65536").End(xlUp).Offset(0, -5) = Range("B4")
.Range("F65536").End(xlUp).Offset(0, 1) = Range("E12")
.Range("F65536").End(xlUp).Offset(0, 2) = Range("G4")
End With
'set hyperlink
Dim docsub As String
Dim docpath As String
docsub = Range("J1").Text
docpath = netNoticelocation & "Notice" & Range("J1").Value & ".xls"
Sheets("notice log").Range("C65536").End(xlUp).Offset(0, 1) = "=hyperlink(""" & docpath & """,""" & docsub & """)"
'add new formatted row
Sheets("notice log").Activate
Range("C65536").End(xlUp).Offset(1, 0).Activate
Rows(ActiveCell.Row + 1).Insert
Rows(ActiveCell.Row).Copy Range("a" & ActiveCell.Row + 1)
'save break out doc and log
Application.EnableEvents = False
Application.DisplayAlerts = False
Sheets("notice").Activate
ActiveSheet.Copy
Call FUBARVBA
ActiveSheet.SaveAs Filename:=netNoticelocation & pno & " - Notice" & Range("J1").Value & ".xls"
ActiveWindow.Close
'Saves down NTC Log
Sheets("notice log").Activate
ActiveSheet.Copy
Call FUBARVBA
ActiveSheet.SaveAs Filename:=netNoticelocation & pno & " - NTC Log.xls"
ActiveWindow.Close
Sheets("notice").Activate
Application.EnableEvents = True
Application.DisplayAlerts = False

[CBrine]

StaticBob,
I believe your problem is that you are trying to save the Activesheet object. You can't save a worksheet, only a workbook.Try

ActiveWorkbook.Saveas Filename:=netNoticeLocation & pno & "-NTC Log.xls"

HTH

Edit: I'll be damed, I checked it out to see if it was possible, and the saveas does work at the Worksheet Level???? I would say give the workbook.saveas a try anyway, and see what happens.

[mdmackillop]

Hi Bob,
Can you zip the file and attach it to your reply (Hit Go Advanced below for this option)
MD

[Ivan F Moala]

Couple of Q's

What is the FUBARVBA function doing ? Setting any pagebreaks ?

your copy data code is incorrect IF you are NOT intending to Reference the Active sheet.

Your code as you have it now........

'copy data
With Sheets("Notice log")
.Range("F65536").End(xlUp).Offset(1, 0) = Range("J1")
.Range("F65536").End(xlUp).Offset(0, -3) = Range("B5")
.Range("F65536").End(xlUp).Offset(0, -5) = Range("B4")
.Range("F65536").End(xlUp).Offset(0, 1) = Range("E12")
.Range("F65536").End(xlUp).Offset(0, 2) = Range("G4")
End With

is actually getting data from the ActiveSheet and NOT Sheets("Notice log"). This will be OK if you are actually on this sheet but will yeild the wrong results if you aren't.
You will need to fuly qualify your addresses eg.

'copy data
With Sheets("Notice log")
.Range("F65536").End(xlUp).Offset(1, 0) = .Range("J1")
.Range("F65536").End(xlUp).Offset(0, -3) = .Range("B5")
.Range("F65536").End(xlUp).Offset(0, -5) = .Range("B4")
.Range("F65536").End(xlUp).Offset(0, 1) = .Range("E12")
.Range("F65536").End(xlUp).Offset(0, 2) = .Range("G4")
End With

[Staticbob]

Thanks for the replies gents,

CB - I know that the save sheet isn't the best way of doing things, but this code was originally swiped from a Mr Excel post and it seems to work fine. I have lots of other modules using this method and it works OK.

Ivan - I am actually copying data from the active sheet (notice) to the notice log, so this code works OK. I know I should fully qualify my refs tho !
The FUBARVBA module just strips VBA and buttons from the copied sheet, it is not inserting any page breaks. Code is below

Like I said, this HAS been working fine in the past. This is also just 1 of 34 workbooks I have out there, all from the same template and using the same code. This error has shown itself on 3 of those workbooks, all at different times and from different modules, but always on the same line of code (saveas)

This file is opened accross the network. I have noticed sum strange temp files (no extension) in the folder the file is opened from with hex numbers for filename. eg 1BE5B556, 1AC3E000 ? Could these be causing the problem ? the error still occurs if these are deleted.

Thanks again for the interest gents, any more suggestions / solutions greatly appreciated !

Bob

Sub FUBARVBA()
Set oVBComps = ActiveWorkbook.VBProject.VBComponents
For Each oVBComp In oVBComps
Select Case oVBComp.Type
Case 1, 2, 3 'Standard Module, Class Module, Userform
oVBComps.Remove oVBComp
Case Else
With oVBComp.CodeModule 'Worksheet or workbook code module
.DeleteLines 1, .CountOfLines
End With
End Select
Next oVBComp
       
   'remove shapes
        On Error Resume Next
        With ActiveSheet
            .Shapes("asarrow").Delete
            .Shapes("btnsave").Delete
            .Shapes("btnpublish").Delete
            .Shapes("btnprint").Delete
            .Shapes("ashelp").Delete
            .Shapes("btnbrowse").Delete
        On Error GoTo 0
        End With
End Sub

[XL-Dennis]

Bob,

You seems to work with a userform to save and "clean" up. When working with several forms I?ve noticed that Excel is some times unable to remember the active workbook, active sheet etc.

The workaround for it is to
a) use variables for storing the names of the activesheet, workbook etc.
b) send the workbook-variable as parameter to the FUBARVBA()-sub.
c) save with the activesheet-variable.

In general I always recommend to avoid using the Active-syntax.

Network-files may need more time to be handled depending on the network-traffic and any delay may cause strange behaviour when we use automation. If this is the case set a timer or use application.wait to let Excel do the work before moving on.

Kind regards,
Dennis

[Ivan F Moala]

Bob,

You seems to work with a userform to save and "clean" up. When working with several forms I?ve noticed that Excel is some times unable to remember the active workbook, active sheet etc.

The workaround for it is to
a) use variables for storing the names of the activesheet, workbook etc.
b) send the workbook-variable as parameter to the FUBARVBA()-sub.
c) save with the activesheet-variable.

In general I always recommend to avoid using the Active-syntax.

Network-files may need more time to be handled depending on the network-traffic and any delay may cause strange behaviour when we use automation. If this is the case set a timer or use application.wait to let Excel do the work before moving on.

Kind regards,
Dennis

Hi CM Dennis
Yes, I just noticed it was via a Useform and that it could possible be saved to a network Dir. I concur with your comments.

It might pay to throw in a Do Event in there ..... ??

[Staticbob]

OK guys,

Here is the code that is ran from the Notice sheet, from a button. This code just checks for mandatory data, then calls the user form, code as above. I have used a userform so that I can display a message on screen while the code is executing. My users were seeing the hourglass and thinking the machine had crashed. All my user form has is the company logo, and a message "Saving - Please Wait" in the title bar.

How do I pass the required variables into the userform_activate and FUBARVBA code. Have in mind that the FUBARVBA module is called from lots of different places.

Thanks again,
Bob

Private Sub btnsave_Click()
Dim liAnswer As Integer, wsData As Worksheet
Dim strArray(0 To 4, 0 To 1) As String, lDim1 As Long
ActiveSheet.Range("A7").Activate
Const strWbkName As String = "Bluestone Project Workbook"
Const strPwd As String = "florence"
'User confirmation
liAnswer = MsgBox("Have you finished with this Notice?", vbYesNo, strWbkName)
If liAnswer = vbNo Then
MsgBox "Notice Not Saved", vbCritical, strWbkName
Exit Sub
End If
'Check for mandatory data
Set wsData = ThisWorkbook.Worksheets("notice")
strArray(0, 0) = "B4"
strArray(0, 1) = "Please select who the notice is issued to !"
strArray(1, 0) = "B5"
strArray(1, 1) = "Please enter a subject !"
strArray(2, 0) = "G4"
strArray(2, 1) = "Please enter a date !"
strArray(3, 0) = "A27"
strArray(3, 1) = "Please enter a response date !"
strArray(4, 0) = "E27"
strArray(4, 1) = "Please select who is issuing this Notice !"
For lDim1 = 0 To 4
With wsData.Range(strArray(lDim1, 0))
If .Value = "" Then
wsData.Unprotect PASSWORD:=strPwd
.Activate
.Interior.ColorIndex = 3
MsgBox strArray(lDim1, 1), vbCritical, strWbkName
.Interior.ColorIndex = 0
wsData.Protect PASSWORD:=strPwd
Exit Sub
End If
End With
Next lDim1
'Check for e-mail module
If Sheets("email").Range("J26") = 0 Then
If Sheets("directory").Range("L19") Then
MsgBox "There are no e-mail settings for Notice" & vbNewLine & _
"Please set-up email and re-try", vbOKOnly, strWbkName
Sheets("email").Activate
Exit Sub
End If
End If
frmsavenotice.Show
End Sub

[XL-Dennis]

Bob

The following example may hopefully get You started:

In a standardmodule:

Option Explicit
Public stWorkbook As String
Sub View_Form()
'Fullname gives both the pathway and the
'name of the active workbook.
stWorkbook = ActiveWorkbook.FullName
UserForm1.Show
End Sub
 
Sub FUBARVBA(stWBook As String)
MsgBox "The active workbook path and name is: " & stWorkbook
End Sub

In the userform's module

Option Explicit
Private Sub CommandButton1_Click()
'Call the sub and send the parameter to it.
FUBARVBA (stWorkbook)
End Sub
 
Private Sub UserForm_Initialize()
'Just an example...
Me.Label1.Caption = stWorkbook
End Sub

Hi CM Ivan
Of course You?re right about the Do Event

Kind regards,
Dennis

[Staticbob]

Thanks Dennis, that'll do nicely.

So where do I code the Do Events or application.wait in my code ?

Thanks
Bob

[XL-Dennis]

Bob,

Since the issue refer to saving after cleaning up I would place it before:

ActiveSheet.SaveAs Filename:=netNoticelocation & pno & " - NTC Log.xls"

You may need to play around with both the DoEvents as well as the Wait.

Good luck and mail back with the output