Copying worksheets between workbooks - The object invoked hasdisconnected from its client

M

Matt

I have an Excel workbook that has functionality to open a different
workbook (template) from an intranet http address, then copy a
collection of worksheets into the workbook containing the code.
Essentially, the user is selecting to Add calculation worksheets to
their shell of a workbook from our central repository of templates.
The collection of worksheets added can be anywhere between 5 - 20
worksheets all with intertwined calculations... this is why they need
to be added as a collection, as to not lose the references between
worksheets.

This code was working just fine until recently. Recent changes were
small and had nothing to do with this functionality at all... mostly
just some VBA code reorganization. But now, some, but now all of my
users are seeing:
Error -2147417848 (&H80010108): The object invoked has disconnected
from its clients.
when the following line is being called. The entire function is
included below.
wkbk.Worksheets(GetWorksheetArray(Plan, False)).Copy
before:=wkbkDest.Worksheets("Security")
I have seen this article (http://support.microsoft.com/kb/319832/en-
us) in the Microsoft Knowledge Base and I have tried to conform with
what it says, but to no avail.

Has anyone come across anything like this? Anyone have any ideas?

Thank you for looking.

Here's the rest of my subroutine:

Public Sub AddPensionRemote(iTemplateNumber As Integer, ByRef Plan As
PlanDataType)
'copies the template, renames sheets, removes unnamed sheets, etc.

Dim xl As Excel.Application
Set xl = Excel.Application
xl.EnableEvents = False

Dim wkbkDest As Object
Set wkbkDest = xl.ActiveWorkbook

'Get the proper plan template as a workbook object
Dim wkbk As Object, sFilename As String
Select Case Plan.CountryTemplate
Case "United States"
'sFilename = "TemplateUSPen.xls?" + TemplateVersion("US", 0)
sFilename = "TemplateUSPen.xls"
Case "Canada"
'sFilename = "TemplateCanPen.xls?" + TemplateVersion("CAN", 0)
sFilename = "TemplateCanPen.xls"
Case Else
'sFilename = "TemplateUSPen.xls"
MsgBox "Unknown country"
Exit Sub
End Select

'To try to avoid Excel crashing, we will open template in a new,
hidden instance of Excel
Set wkbk = xl.Workbooks.Open(GetServerName() & sFilename, , True)
wkbk.Application.EnableEvents = False
wkbk.Windows(1).Visible = False

xl.ScreenUpdating = False
xl.Calculation = xlCalculationManual
xl.DisplayAlerts = False

'copy the template plan sheets as a unit - "before" sheet must be
visible
wkbkDest.Worksheets("Security").Visible = True

'Application.DisplayAlerts = False
wkbk.Worksheets(GetWorksheetArray(Plan, False)).Copy
before:=wkbkDest.Worksheets("Security")
wkbk.Close False


wkbkDest.Worksheets("Security").Visible = xlSheetVeryHidden


UpdateLinksToCurrentFile TemplateWorkbook

RenameTemplateWorksheets Plan, iTemplateNumber, False, 0
Plan.Template = iTemplateNumber

xl.Calculation = xlCalculationAutomatic
xl.ScreenUpdating = True
xl.DisplayAlerts = True
xl.EnableEvents = True

End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top