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
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