"-2147417848 Automation error The object invoked has disconnected from its clients" - When Copying Worksheets from One Workbook to Another.

[pedie]

Hi, 'm recieving this error message "-2147417848 Automation error The object invoked has disconnected from its clients" - When Copying Worksheets from One Workbook to Another.
And sometime it happen that workbook fails to open up...

[FONT=Courier New]Set MnWb= Workbooks.Open(Filename:=myMainWbstr, ReadOnly:=True, UpdateLinks:=False, CorruptLoad:=True)[/FONT]

Error happens in the line below...
Sometime when i manually F8 one line after another it goes through...

[FONT=Courier New]Set [FONT=Courier New]XWb[/FONT]= Workbooks.Open(Filename:=[FONT=Courier New]XWbFile[/FONT], ReadOnly:=True)
                        For Each sh In [FONT=Courier New]XWb[/FONT].Worksheets
                            If sh.Name <> "Notes" And sh.Visible = xlSheetVisible Then
                                [U]Workbooks(XWb.Name).Sheets(sh.Name).Copy After:=Workbooks(MnWb.Name).Sheets(Workbooks([FONT=Courier New]MnWb[/FONT].Name).Sheets.Count)
[/U]                                Sheet4.Cells(i, 36).Value = Sheet4.Cells(i, 36).Value + 1
                            End If
                        Next sh[/FONT]

 

[JapanDave]

Try this,

       Set XWb = Workbooks.Open(Filename:=XWbFile, ReadOnly:=True)
                    
                        For Each sh In XWb.Worksheets
                            If sh.Name <> "Notes" And sh.Visible = xlSheetVisible Then
                                sh.Copy MnWb.Sheets(1)
                                Sheet4.Cells(i, 36).Value = Sheet4.Cells(i, 36).Value + 1
                            End If
                        Next sh

 

[pedie]

Thanks for quick answer but the problem is at the workbooks.open method. I have tried different ways but there seems to be something which I don't understand...When I place/hover I cursor over xwb it shows "nothing" which is why it is failing to copy the sheet...What could be the reason? I have tried searching in web but of no much help...pls help...

 

[JapanDave]

I know you may have tried this and it does not do anything different to your original code but try this and see if the file name is correct in the Intermediate window.

myMainWbstr = "YourFilePathHere"Workbooks.Open Filename:=myMainWbstr, ReadOnly:=True, UpdateLinks:=False, CorruptLoad:=True
Set MnWb = ActiveWorkbook
Debug.Print MnWb.Name

 

[JapanDave]

That above code was for some reason when I posted ,joined two lines together. It is supposed to look like this.

It did it again, what is up with the formatting of posts on this site.

myMainWbstr = "YourFilePathHere"    
Workbooks.Open Filename:=myMainWbstr, ReadOnly:=True, UpdateLinks:=False, CorruptLoad:=True
    Set MnWb = ActiveWorkbook
    Debug.Print MnWb.Name

 

[pedie]

Hi ***anDave, when i debug the file name is reflecting correctly. I confirmed it by copying the link and manually running the link which opened the file.

thanks again.

 

[JapanDave]

So does the above code go through when you run it normally?

 

[pedie]

Hi, sorry coudnt respond earlier.
sometime xwb fails to open and gives and error at
sh.Copy MnWb.Sheets(1) 'Error is - Variable ..... not defined...

however when xwb opens it still gives and error at
sh.Copy MnWb.Sheets(1) 'Error is - Automation error2147.....

Set XWb = Workbooks.Open(Filename:=XWbFile, ReadOnly:=True)
                        For Each sh In XWb.Worksheets
                            If sh.Name <> "Notes" And sh.Visible = xlSheetVisible Then
                                sh.Copy MnWb.Sheets(1)
                                Sheet4.Cells(i, 36).Value = Sheet4.Cells(i, 36).Value + 1
                            End If
                        Next sh

 

[pedie]

ok, i finally found the problem....XWbFile = Range("A1").value & Range("A2").value & Range("A3").value
now i made xwv = Range("A2").value and it worked perfect...

thanks for all the help...