The object invoked has disconnected from its clients. 80010108 null

[c***@gmail.com]

I have run this script weekly for over 3 years now and it has worked perfectly. However, I recently added 2 more files to open and then close after I run a macro in the xlsm file. Since I added the extra 4 lines of code I have been getting this message:

Script: \\location\000.vbs
Line: 44
Char: 1
Error: The object invoked has disconnected from its clients.
Code: 80010108
Source: (null)

Keep in mind that all of the code that I know I got from Google.
This 000.vbs file is executed from within a Access database that first deletes last weeks Excel files and then creates new Excel files by exporting query results based on a preset date range and then the database vb has a msgBox line that effectively causes pause until the 000.vbs runs. After this 000.vbs runs I click the "OK" box in the database and the vb continues. The purpose of all this mess is to automate weekly reporting that would take hours to do otherwise. In the code below you will see "StaffMeeting.xlsm". That is an Excel file where, once all these files are opened, I run a macro that pulls in information from still another source and does some calculations based on all of these linked xls files and gives all the information that I could be questioned about in the staff meeting.

The code in 000.vbs is as follows

Dim objExcel, objWorkbook1, objWorkbook2, objWorkbook3, objWorkbook4, objWorkbook5, objWorkbook6, objWorkbook7, objWorkbook8, objWorkbook9, objWorkbook10, objWorkbook11, objWorkbook12, objWorkbook13, objWorkbook14, objWorkbook15, objWorkbook16, objWorkbook17, objWorkbook18, objWorkbook19, objWorkbook20, objWorkbook21, objWorkbook22
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open ("\\location\QtyByDie.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open ("\\location\QtyByDieWeek.xlsx")
Set objWorkbook3 = objExcel.Workbooks.Open ("\\location\QtyByType.xlsx")
Set objWorkbook4 = objExcel.Workbooks.Open ("\\location\QtyJobsComp.xlsx")
Set objWorkbook5 = objExcel.Workbooks.Open ("\\location\Qty_By_Hour.xlsx")
Set objWorkbook6 = objExcel.Workbooks.Open ("\\location\Dieshop.xls")
Set objWorkbook7 = objExcel.Workbooks.Open ("\\location\Incomplete.xlsx")
Set objWorkbook8 = objExcel.Workbooks.Open ("\\location\ProdSumRep.xls")
Set objWorkbook9 = objExcel.Workbooks.Open ("\\location\QtyYanks.xlsx")
Set objWorkbook10 = objExcel.Workbooks.Open ("\\location\WklyDowntime.xls")
Set objWorkbook11 = objExcel.Workbooks.Open ("\\location\Production.xls")
Set objWorkbook12 = objExcel.Workbooks.Open ("\\location\EDM.xlsx")
Set objWorkbook13 = objExcel.Workbooks.Open ("\\location\Duration.xlsx")
Set objWorkbook14 = objExcel.Workbooks.Open ("\\location\RobotWeekly.xlsx")
Set objWorkbook15 = objExcel.Workbooks.Open ("\\location\CJQuery.xlsx")
Set objWorkbook16 = objExcel.Workbooks.Open ("\\location\YanksAtSetup.xlsx")
Set objWorkbook17 = objExcel.Workbooks.Open ("\\location\PrssCllsByOp.xlsx")
Set objWorkbook18 = objExcel.Workbooks.Open ("\\location\QAMISCH.xlsx")
Set objWorkbook19 = objExcel.Workbooks.Open ("\\location\QtyProjects.xlsx")
Set objWorkbook20 = objExcel.Workbooks.Open ("\\location\QtyTypeMaint.xlsx")
Set objWorkbook21 = objExcel.Workbooks.Open ("\\location\QtyProjects.xlsx")
Set objWorkbook22 = objExcel.Workbooks.Open ("\\location\StaffMeeting.xlsm")
msgBox "Intentional Pause - Run Macro in staff meeting spreadsheet"
objWorkbook1.Close
objWorkbook2.Close
objWorkbook3.Close
objWorkbook4.Close
objWorkbook5.Close
objWorkbook6.Close
objWorkbook7.Close
objWorkbook8.Close
objWorkbook9.Close
objWorkbook10.Close
objWorkbook11.Close
objWorkbook12.Close
objWorkbook13.Close
objWorkbook14.Close
objWorkbook15.Close
objWorkbook16.Close
objWorkbook17.Close
objWorkbook19.Close
objWorkbook20.Close
objWorkbook21.Close
objWorkbook18.Close

[R.Wieser]

ceejmo,

Since I added the extra 4 lines of code I have been getting this message.

Before, after or while you're running that macro ?

Have you tried to run only those four lines (without executing the macro) ?
Does it disconnect too ?

If not, what happens when you replace those last four open commands by ones
opening some dummy/unused files ?

Have you tried to copy those (last) four files to the local machine and open
them from there ?

-- What I'm thinking of is that you might be exceeding a "maximum of open
objects/remote files" limit.

Regards,
Rudy Wieser


-- Origional message:
<***@gmail.com> schreef in berichtnieuws
e077ccea-cb83-43d7-9de4-***@googlegroups.com...
I have run this script weekly for over 3 years now and it has worked
perfectly. However, I recently added 2 more files to open and then close
after I run a macro in the xlsm file. Since I added the extra 4 lines of
code I have been getting this message:

Script: \\location\000.vbs
Line: 44
Char: 1
Error: The object invoked has disconnected from its clients.
Code: 80010108
Source: (null)

Keep in mind that all of the code that I know I got from Google.
This 000.vbs file is executed from within a Access database that first
deletes last weeks Excel files and then creates new Excel files by exporting
query results based on a preset date range and then the database vb has a
msgBox line that effectively causes pause until the 000.vbs runs. After this
000.vbs runs I click the "OK" box in the database and the vb continues. The
purpose of all this mess is to automate weekly reporting that would take
hours to do otherwise. In the code below you will see "StaffMeeting.xlsm".
That is an Excel file where, once all these files are opened, I run a macro
that pulls in information from still another source and does some
calculations based on all of these linked xls files and gives all the
information that I could be questioned about in the staff meeting.

The code in 000.vbs is as follows

Dim objExcel, objWorkbook1, objWorkbook2, objWorkbook3, objWorkbook4,
objWorkbook5, objWorkbook6, objWorkbook7, objWorkbook8, objWorkbook9,
objWorkbook10, objWorkbook11, objWorkbook12, objWorkbook13, objWorkbook14,
objWorkbook15, objWorkbook16, objWorkbook17, objWorkbook18, objWorkbook19,
objWorkbook20, objWorkbook21, objWorkbook22
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open ("\\location\QtyByDie.xlsx")
Set objWorkbook2 = objExcel.Workbooks.Open ("\\location\QtyByDieWeek.xlsx")
Set objWorkbook3 = objExcel.Workbooks.Open ("\\location\QtyByType.xlsx")
Set objWorkbook4 = objExcel.Workbooks.Open ("\\location\QtyJobsComp.xlsx")
Set objWorkbook5 = objExcel.Workbooks.Open ("\\location\Qty_By_Hour.xlsx")
Set objWorkbook6 = objExcel.Workbooks.Open ("\\location\Dieshop.xls")
Set objWorkbook7 = objExcel.Workbooks.Open ("\\location\Incomplete.xlsx")
Set objWorkbook8 = objExcel.Workbooks.Open ("\\location\ProdSumRep.xls")
Set objWorkbook9 = objExcel.Workbooks.Open ("\\location\QtyYanks.xlsx")
Set objWorkbook10 = objExcel.Workbooks.Open ("\\location\WklyDowntime.xls")
Set objWorkbook11 = objExcel.Workbooks.Open ("\\location\Production.xls")
Set objWorkbook12 = objExcel.Workbooks.Open ("\\location\EDM.xlsx")
Set objWorkbook13 = objExcel.Workbooks.Open ("\\location\Duration.xlsx")
Set objWorkbook14 = objExcel.Workbooks.Open ("\\location\RobotWeekly.xlsx")
Set objWorkbook15 = objExcel.Workbooks.Open ("\\location\CJQuery.xlsx")
Set objWorkbook16 = objExcel.Workbooks.Open ("\\location\YanksAtSetup.xlsx")
Set objWorkbook17 = objExcel.Workbooks.Open ("\\location\PrssCllsByOp.xlsx")
Set objWorkbook18 = objExcel.Workbooks.Open ("\\location\QAMISCH.xlsx")
Set objWorkbook19 = objExcel.Workbooks.Open ("\\location\QtyProjects.xlsx")
Set objWorkbook20 = objExcel.Workbooks.Open ("\\location\QtyTypeMaint.xlsx")
Set objWorkbook21 = objExcel.Workbooks.Open ("\\location\QtyProjects.xlsx")
Set objWorkbook22 = objExcel.Workbooks.Open ("\\location\StaffMeeting.xlsm")

msgBox "Intentional Pause - Run Macro in staff meeting spreadsheet"
objWorkbook1.Close
objWorkbook2.Close
objWorkbook3.Close
objWorkbook4.Close
objWorkbook5.Close
objWorkbook6.Close
objWorkbook7.Close
objWorkbook8.Close
objWorkbook9.Close
objWorkbook10.Close
objWorkbook11.Close
objWorkbook12.Close
objWorkbook13.Close
objWorkbook14.Close
objWorkbook15.Close
objWorkbook16.Close
objWorkbook17.Close
objWorkbook19.Close
objWorkbook20.Close
objWorkbook21.Close
objWorkbook18.Close

[Evertjan.]

Post by R.Wieser

Since I added the extra 4 lines of code I have been getting this message.

Before, after or while you're running that macro ?
Have you tried to run only those four lines (without executing the
macro) ? Does it disconnect too ?
If not, what happens when you replace those last four open commands by
ones opening some dummy/unused files ?
Have you tried to copy those (last) four files to the local machine and
open them from there ?
-- What I'm thinking of is that you might be exceeding a "maximum of
open objects/remote files" limit.

That's a good thought.

Most references to this error suggest either exeeding a limit or a right of
access.

[GS]

FWIW
You could simplify this entire process by orders of magnitude if you
just use 1 Excel file with separate sheets for your data/reports. This
file can also contain your macro. You could use ADODB to pull data from
Access.

This approach will eliminate any need to open/delete any other Excel
files, and your links to other data will be internal refs to other
sheets within the same workbook. Also, your presentation would be all
contained in a single file.

Also, ADODB does not require any files be opened to access/update data.

[c***@gmail.com]

What I ended up doing was to remove the following from the VBS:

msgBox "Intentional Pause - Run Macro in staff meeting spreadsheet"
objWorkbook1.Close
objWorkbook2.Close
objWorkbook3.Close
objWorkbook4.Close
objWorkbook5.Close
objWorkbook6.Close
objWorkbook7.Close
objWorkbook8.Close
objWorkbook9.Close
objWorkbook10.Close
objWorkbook11.Close
objWorkbook12.Close
objWorkbook13.Close
objWorkbook14.Close
objWorkbook15.Close
objWorkbook16.Close
objWorkbook17.Close
objWorkbook19.Close
objWorkbook20.Close
objWorkbook21.Close
objWorkbook18.Close

and add this macro to the one spreadsheet that I wanted to keep open:

Sub Auto_Open()
' This sub will close all workbooks
' except the workbook in which the code is located.
Dim WkbkName As Object

On Error GoTo Close_Error
Application.ScreenUpdating = False

For Each WkbkName In Application.Workbooks()
If WkbkName.Name <> ThisWorkbook.Name Then WkbkName.Close
Next

' If everything runs all right, exit the sub.
Exit Sub

' Error handler.
Close_Error:
MsgBox Str(Err) & " " & Error()
Resume Next
End Sub