object disconnected from clients? 1

[claudehenri]

Hi

I'm tying to save a file which is opened from a template. When the SaveAs is executed (the last line) I get this error. I've got no idea?

Run-Time Error '-2147417848 (80010108)':

Automation Error
The object invoked has disconnected from its clients.

Sub SaveCrane()

With SaveFile
For Each Item In Worksheets
'set printareas for each worksheet, here
Next Item
End With
1 SaveFileName = Application.GetSaveAsFilename
(CraneProject, FileFilter:="Microsoft
Excel Workbook (*.xls), *.xls",
Title:="Save Data File")
Temp = Right(SaveFileName, Len(SaveFileName) -
InStrRev(SaveFileName, "\"))
For l = 1 To Workbooks.Count
If Workbooks(l).Name = Temp Then
ReplaceFile = MsgBox("A file of this name is already
open!, do you wish to close this
file in order to save under this
name?", vbYesNo + vbDefaultButton2)
If ReplaceFile = vbYes Then
Workbooks(l).Close
Exit For
Else
GoTo 1
End If
End If
Next l
With Application.FileSearch
.LookIn = Left(SaveFileName,
InStrRev(SaveFileName, "\"))
.Filename = Temp
If .Execute = 1 Then
ReplaceFile = MsgBox("Do you wish to replace the
file", vbYesNo + vbDefaultButton2)
If ReplaceFile = vbNo Then GoTo 1
End If
End With
If Temp <> False Then SaveFile.SaveAs _
Filename:=SaveFileName

End Sub

 

[PHV]

How is instantiated SaveFile ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[claudehenri]

Sorry. SaveFile is declared Public as Object
and this is how i have assigned SaveFile in another sub

Workbooks.Open (ThisWorkbook.Path & "\SaveFile.xlt")
Set SaveFile = ActiveWorkbook

C-H.

 

[claudehenri]

Maybe i should tell you that I have an event macro SaveFile. The event is Workbook_BeforeSave(....)

all I am doing on this event is formating headers and footers.

C-H.

 

[PHV]

You may try this:
Set SaveFile = Workbooks.Add(ThisWorkbook.Path & "\SaveFile.xlt")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[claudehenri]

Hi

My real problem is why is my workbook "disconnecting"!

I have been thinking. In Workbook_BeforeSave() event I use ThisWorkbook as the code that is running at the time is in the workbook I want to modify. Would this get confused with the Workbook actually used the .Save command

Claude-Henri

 

[claudehenri]

Hi

Still having problems trying to save my file. I have narrowed down where the error occurs but have no idea why it is happening.

Public SaveFile As Object
Sub OffShoreCrane()

Set SaveFile = Workbooks.Add(ThisWorkbook.Path & "\SaveFile.xlt")
CraneProject = "Test"
SWL
End Sub
Sub SWL()
For j = 4 To 1 Step -1
SaveFile.Sheets.Add AfTer:=Worksheets("Geometry & Loads"), Type:=ThisWorkbook.Path & "\Wave.xlt"
Next j
End Sub

if I place the following lines of code after "SaveFile.Sheets.Add............"

Temp = Application.GetSaveAsFilename(CraneProject, FileFilter:="Microsoft Excel Workbook (*.xls), *.xls", Title:="Save Data File")
SaveFile.SaveAs Filename:=Temp, AddToMru:=True

I get this error

Run-Time Error '-2147417848 (80010108)':
Automation Error
The object invoked has disconnected from its clients.

The error does not occur if I place the last 2 lines of code anywhere before "SaveFile.Sheets.Add............"

Really appreciate some help on this one.

Claude-Henri

 

[claudehenri]

If I add sheets just using this line of code

SaveFile.Sheets.Add AfTer:=Worksheets("Geometry & Loads")

i.e. just a blank sheet, the error doesn't occur. I have also tried the line of code below, and I get the same error.

SaveFile.Sheets.Add AfTer:=Worksheets("Geometry & Loads"), Type:="Wave.xlt"

Claude-Henri

 

[ajoshi2211]

CH,
I am experiencing exactly the same problem. I have not been able to find a solution yet. However, the following seem to be the situations that generate this error.
1. Having a Multi-page form.
2. Corruption of the Excel workbook. The method to clean the workbook is to export each of the objects from the workbook, save the workbook and then import the objects.
3. The use of global references in code without setting a variable references to them. For example in the following code snippet Cells refers to global worksheet object and not to any specific worksheet object (Range is OK)

With Someworksheet
.Range(Cells(1,1),Cells(1,5)).Value = "x"
end with

It seems that this kind of referencing typically results in a problem when the code is run the second time.

Incidentally I have tried all these approaches. At one time the cleaning of the VBA project did seem to work but I started getting the problem again after a while.

Hope all this helps. If you do find a solution please post it. I will do the same.

Thanks

Alok

 

[claudehenri]

Thanks for the info, I haven't found a solution to the problem, so I'm working things another way, to avoid it. I will let you know if I get a solution.

C-H.

P.S. I'll give you a star just for answering, it has been fairly dry on help

 

[Rofeu]

Hi,

I also have this problem and can not find a solution.

I ran it down to the following part of that leads to this error:

'Setting the conditions:

Dim wsMvmntIN As New Worksheet
Set wsMvmntIN = Application.Workbooks(TrimToName(strCurrentDate)).Sheets.Add

'Part where the error seems to occur:

wsCurrentDate.Rows(lnMatchRow).Copy Destination:=wsMvmntIN.Rows(lnDestRow)

The odd thing is, that it doesn't happen every time I run my procedure. It seems to me that it has to do with the workbook in which the sheet needs to be created.

So far, I haven't been able to come with any reason as to why it happens.

I hope this helps. I will keep an eye out for any postings for solutions or post my solution if I ever do find it.

Cheers

 

[Rofeu]

In addition to the above:

In my code, the worksheet does get created, just when I call it the 3rd time, I get the error. I've tried creating a new variable that sets the created worksheet:

Dim wsNewMvmntIN As New Worksheet
Set wsNewMvmntIN = Application.Workbooks(TrimToName(strCurrentDate)).Sheets.Add

wsNewMvmntIN.Name = strNewName1

Dim wsMvmntIN As Worksheet
Set wsMvmntIN = Application.Workbooks(TrimToName(strCurrentDate)).Sheets(strNewName1)

This results in the same thing, which would imply that Ajoshi's 3rd point is not part of the reason.

The strange thing in my case is, that I have a second variable dimmed, set and called the exact same way which does not give me any problems and works exactly as it is supposed to.

Cheers,

Roel

 

[Rofeu]

The error definitely occurred in the following line:

[wsPreviousDate.Rows(lnMatchRow).Copy _ Destination:=wsMvmntOUT.Rows(lnDestRow)]

I've replaced it with:

[For AllCol = 1 To x
wsMvmntOUT.Cells(lnDestRow, AllCol) = wsPreviousDate.Cells(lnMatchRow, AllCol)
Next]

and this works.

My findings now are:

1. The first part did actually execute before going into error.

2. It seems that the problem doesn't lie in the variable object itself but in it's placement in the code. I've tried the first part without the [Destination:=]
That results in a different error which comes down to the same thing (Copy Method of range object failed).

3.The error occurs on the second time the object is called within a procedure.

4. It's not constant. I've some files for which it works fine and others for which it goed into error. I could not find any differences in the called files that could be the cause of the error.

 

[TonyJollans]

Hi Roel,

From my experience 'object disconnected from client' seems to occur quite often in Office 2003 and is often due to implicit instantiation. It also seems to be because of changed behaviour with regards to the lifetime of objects and object variables.

In your latest posted code you say you get the error without the Destination parameter. Without the destination the only object involved is wsPreviousDate - how is this instantiated?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[Rofeu]

Hi, Tony,

thanx for your reply. I think I was a bit unclear about the destination parameter. I just took the part "Destination:=" off:

[wsPreviousDate.Rows(lnMatchRow).Copy wsMvmntOUT.Rows(lnDestRow)]

The error occurs on the wsMvmntOUT and I think you said it with the changed behavior with regards to the lifetime.

wsPreviousDate is instantiated as follows:

[dim wsPreviousDate as worksheet
wsPReviousDate = Application.Workbooks(TrimToName(strPreviousDate)).Sheets(cSource)]

But I'm quite positive that that is not the problem.
I've also tried to have a new worksheet added and only then set it as an object, but that resulted in the same thing.

It sounds to me like the variable actually loses it's object after having it used once within a parameter, but every attempt to set it again resulted in the same error.

As I said, I've managed to circumvent this error, but I'd like to understand the reason for this error.

 

[PHV]

[highlight]Set [/highlight]wsPReviousDate = Application.Workbooks(TrimToName(strPreviousDate)).Sheets(cSource)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Rofeu]

sorry, I missed the [Set] in my copy and paste action. It is actually present in my code.

 

[claudehenri]

The thing is with this is you stop all macros running and try to save the file as normal and excel still crashes. Is it a programming problem or a EXCEL problem?

Claude-Henri