Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Object invoked has disconnected from its clients Results

I have some code that was intermittently throwing up this error: "Error -2147417848 (&H80010108): The object invoked has disconnected from its clients. "

The first line in the code was to show a userform and this is where the code crashed. After much google'ing and research found lots of "The object invoked has disconnected from its clients" Posts but no matches to my problem. After about 7 hours of frustration found that it only happened if there wasn't an activesheet/workbook open in excel.

Problem solved by checking for activesheet if not there then create a dummy workbook prior to showing the userform, then close after the form is unloaded....

Hi

I seem to be having a problem when creating new sheets in a workbook, only after deleting a few. The sheets are created and deleted through VBA Code.

To give you an example: -

Lets say I run Macro1 which adds 2 sheets and copies 1 sheet & renames it, all sheets have data on them.

Next I run Macro2 which deletes the sheets that I have created and the 1 that was copied.

Now when I run Macro1 again it will create the two sheets but it fails on the thrid sheet, which its meant to copy from another.
It gives the error message : -
"-2147417848 (&H80010108): The object invoked has disconnected from its clients."

Now the temporary solution that works is, After running Macro1 and Macro2, close Excel down and then reopen and run Macro1 again and it works fine.
I think its something to do with Excel reorganising the sheets in the workbook when the workbook is opened.

The code for Macro2 is


	VB:
	
 AddLoanSht() 
     
     ' the variables below are global String variables
    mortgageIntLoanName = acName & "IntLoans" 
     
    Sheets("IntLoans").Copy After:=Sheets(1) 
    Sheets("IntLoans (2)").Select 
    Sheets("IntLoans (2)").Name = mortgageIntLoanName 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I dont reckon there is anything wrong with the code as it works whenever Excel is fresh. (then again I might be wrong)

Any help is greatly appreciated.
Many Thanks

Trying to put in validations for a set of Columns and am
using some macros and functions internally to do so.

Following is the code that I'm using to generate the
validations and then apply them to the specifid columns:

-----------------------------------------------------------
Sub CreateValidation(sheetValidation As String, _
rangeStart As String, rangeEnd As
String, _
sheetData As String, colData As
Integer, _
rowDataStart As Integer)

On Error GoTo Errorhandler
Sheets(sheetValidation).Range(rangeStart,
rangeEnd).Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=CreateValidationText
(sheetData, colData, rowDataStart)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Exit Sub

Errorhandler:
MsgBox Err.Number
MsgBox Err.Description
MsgBox Err.Source

End Sub

Function CreateValidationText(sheetData As String, colData
As Integer, rowDataStart As Integer)

Dim retValue As String

For rowNumber = rowDataStart To 65535
If Sheets(sheetData).Cells(rowNumber,
colData).Value = "" Then
Exit For
Else
retValue = retValue & ", " & Sheets
(sheetData).Cells(rowNumber, colData).Value
End If
Next rowNumber

MsgBox "Length: " & Len(retValue)
CreateValidationText = retValue
End Function
-----------------------------------------------------------

I apply the validation using the following call:
CreateValidation "Reports", "H4", "H65535", "DD - Report",
14, 2

I'm using the same code for 7 different sets of columns,
it just fails for the above call and generates the error:
Automation Error: The Object Invoked Has Disconnected from
Its Clients

Please help !

Trying to put in validations for a set of Columns and am
using some macros and functions internally to do so.

Following is the code that I'm using to generate the
validations and then apply them to the specifid columns:

-----------------------------------------------------------
Sub CreateValidation(sheetValidation As String, _
rangeStart As String, rangeEnd As String, _
sheetData As String, colData As Integer, _
rowDataStart As Integer)

On Error GoTo Errorhandler
Sheets(sheetValidation).Range(rangeStart, rangeEnd).Select

With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:=CreateValidationText(sheetData, colData,
rowDataStart)
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Exit Sub

Errorhandler:
MsgBox Err.Number
MsgBox Err.Description
MsgBox Err.Source

End Sub

Function CreateValidationText(sheetData As String, colData As Integer,
rowDataStart As Integer)

Dim retValue As String

For rowNumber = rowDataStart To 65535
If Sheets(sheetData).Cells(rowNumber, colData).Value = "" Then
Exit For
Else
retValue = retValue & ", " &
Sheets(sheetData).Cells(rowNumber, colData).Value
End If
Next rowNumber

MsgBox "Length: " & Len(retValue)
CreateValidationText = retValue
End Function
-----------------------------------------------------------

I apply the validation using the following call:
CreateValidation "Reports", "H4", "H65535", "DD - Report", 14, 2

I'm using the same code for 7 different sets of columns,
it just fails for the above call and generates the error:
Automation Error: The Object Invoked Has Disconnected from
Its Clients

Please help !

I posted this question:

"Say I have the following data all in the same column:

/****** Object: [Table1]
AD
RW
SD
TD
GG
/****** Object: [Table 2]
LK
IM
HJ
/****** Object:[Table 3]
PQ
WE

Before each row containing /****** I want to insert a new, empty row. How can I do this?

Things to note:
- in between the rows containing '/******' there is a different number of rows of data each time
- each row containing /****** is followed by data which is different each time e.g. Table 1, Table 2, Table 3"

The following code was suggested to me:

"Public Sub InsertBeforeAsterisks()
Range("A1").Select
Do Until ActiveCell = ""
While ActiveCell <> "/******"
ActiveCell.Offset(1, 0).Select
Wend
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveCell.Offset(2, 0).Select
Loop
End Sub"

However, when I try and run this code, Excel stops responding and I get and error: "the object invoked has disconnected from its clients"

Any ideas how to resolve this? Could it have anything to do with running on Vista?

Thanks

Pulling a bunch of data from the Yahoo finance website.. Basically the web-query macro re-runs itself once it finishes.... some sort of loop.

I've had various errors but managed to get it working, after around 200 lines of data, I get the error:

"error: the object invoked has disconnected from its clients"

as I result i've had to auto-save everytime the macro executes.

Anyone had any experience with this error or suggestions to fix? Also anything that will speed it up?

When I debug this line is highlighted:

.Refresh BackgroundQuery:=False

Attached is the the file, and the code is below:

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 8/17/2009 by John Cooper
'

'
    Application.ScreenUpdating = False
    
    Selection.Copy
    Sheets("Sheet2").Select
    Range("A1").Select
    ActiveSheet.Paste
    Rows("2:119").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Range("A2").Select
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://finance.yahoo.com/q?s=" & Range("a1").Value, Destination:=Range("A2"))
        .Name = "q?s=GOOG&="
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = """table2"""
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
    ActiveCell.Offset(4, 1).Range("A1").Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    Sheets("Sheet2").Select
    ActiveCell.Offset(2, 0).Range("A1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveSheet.Paste
    
    Application.ScreenUpdating = True
    ActiveCell.Offset(1, -8).Range("A1").Select
        
    Shell "RunDll32.exe InetCpl.cpl,ClearMyTracksByProcess 255"
    ActiveWorkbook.Save
    
    Application.Run "stockcodesNEW.xls!Macro4"


p.s I have had to clear the cache everytime the web-query executes in order to over-come a previous error.

Hi

I get this error if I move away from excel to some other window while my macro is running.

-2147417848 automation error the object invoked has disconnected from its clients

I was not getting this earlier.

MSDN says "Make sure that you set Option Explicit at the top of each file in your project."

I am adding some sheets at run time. How can I add Option Explicit at the top of added sheets? And, will that solve my problem?

Are there any otehr suggestions?

Thanks
Ajay

Hi All,

Could anyone shed some light on the following??

I have written some VBA code which controls a spreadsheet as follows:

As soon as the file is opened a userform appears, which is the size of the screen. This has several controls which are the only user interface. You never get to actually see excel or the worksheets. There are several different control buttons which call up different userforms to accept input from the user and this data is stored in different worksheets. Some of the buttons also pool the data in several different arrangements on a worksheet and print the sheet out.

The issue I am having is as follows:

One of the buttons calls a userform (called calender) which is the main user interface. When the calender form is called it gathers some data from a worksheet using the activate event. From user input several different variables are calculated and updated on the calender form using the afterupdate and change events. There is a save button which then updates these values to the correct worksheet for storage and the calender form closes back to the main menu form. On the main menu form there are 2 buttons for 'close and save' and 'close and don't save' which do exactly that to the workbook. Once one of these buttons has been pressed, the relevant procedure is carried out and the workbook closes.

This is all working fantastically well and I thought I had completed the project but then when I was road testing it I found a bug which I can't get to the bottom of.........................

When you call the main menu form straight from the VBA design window everything works fine all day long. When you open the file (which calls the main menu) everything works fine first time around. However if you choose the 'close and save' and the workbook closes, and then you close excel, then re-open the file, the main menu loads fine, but when you click the calender form (which looks up data from a worksheet) half of the values, update and then the whole thing freezes in a strange manner. The other values never update, all of the other controls cease to work although you can click the buttons and change the values - just nothing happens with them at all. Its as though the original macro is in some sort of eternal loop.

I have tried the following to de-bug. Originally the value updated on the calender form using the activate event. I tried changing this to initialize event and re ran. Same thing. First time round fine - then save/close - close excel - re-open and error - only this time it never actually loads the calender form, I get "Runtime Error '-2147417848 (80010108) Automation Error. The object invoked has disconnected from its clients" - and back to nothing working.

I have tried more explicitly naming where the variables update and are sourced from to ensure that the correct control has the focus but no joy. It seems strange to me that it always works first time, and crashes second time???
Even more strange is that if you perform the save/close but DO NOT close excel and re-open the file it works absolutely fine again. Its as though when you close the workbook and ALSO close excel something happens that won't let some of the variables update. When I take these variables out of the code the whole thing works absolutely fine no matter what you do.

Has anyone got a clue? Why does it work from design mode flawlessly and from the first runnning flawlessly, and even if you close and re-open without closing excel, but faults when you exit the workbook and exit excel then re-open.
(ps I have also tried resetting the variables to 0 on exit but still no joy)

The variables that fault are very simple along the lines of textbox1.value + textbox2.value.

Please help............................................................

Workbook is protected, sheet is protected userinterfaceonly, have checked and the correct range is selected.

Following code fails on the .Add method with an automation error - 2147417848 (80010108) - The object invoked has disconnected from it's clients. If the sheet is fully unprotected, there is no error - problem is, I need the sheet and the workbook structure protected - ...

With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=Choices
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

Replacing the .Delete and .Add with a single .Modify results in another error...1004 Application-defined or object-defined error.

I'd like the current code to apply the validation shown on the protected workbook/ sheet...can you help?

Oh, I have searched (here, vbaexpress, cpearson, microsoft support forums, google, expertexchange) and I can find the error but not as a result of my particular circumstances.

Thanks

Hi,

I am copying sheets in a macro...
Copy a sheet, rename the copy, copy the sheet again, and
rename the copy again...

Usually on the third copy,I get the following error
message:

Run-time error '-2147417848 (80010108)':
Automation error
The object invoked has disconnected from its clients

If I then try to do a manual copy, Excel crashes and sends
a Dump to Microsoft.

If I start again and do the copies manually, then on the
third time, Excel crashes and sends a Dump to Microsoft.

I was thinking I was running out of resources, so I
deleted a large number of sheets and reduced the size of
the workbook by 60%, but the problem still occurs evrery
time.

Any ideas gratefuly received.

Don

Hello,

I have a rather compex macro I've been developing. It is used to perform some statistical analysis on tesults from a materials testing program. The macro is designed to perform goodness of fit tests on the results to detemine an appropriate probability distribution that best describes the data. All the test results are stored in a single worksheet in an Excel workbook.

The macro loops through the full set and collects the results into an array according to a set of grouping parameters. The array is then output to a new workbook where the goodness of fit tests is conducted and probability plots are generated. I'm examinining the goodness of fit using both Normal and Lognormal probability distributions. To save space in my thesis, I want to put two probability plots (normal and lognormal) for each material roperty on the same chart sheet. The procedure does this by first generating each probability plot on separatechart sheets then moving them both onto the same sheet.

Everything was working fine up until about an hour ago when I started getting a Runtime Error -2147417848 (80010108), "The Object Invoked Has Disconnected from Its Clients". This error does not occur until several sets of output workbooks have been generated. It happens at the line of code that tells Excel to move the two probability plots onto the same sheet. Has anyone any possible explanations?

Earlier in the day, I had the error about exceeding the maximum allowable number of font sizes that can be used in a single workbook so I modified my registry to disable the AutoScale fonts option when plotting charts. Could this have contributed to the latest problem? Also, with the crrent set of results, the macro generates ten separate output workbooks and takes about five minutes to run. To save time, I frequently use Excel 2007 to open up the completed output workbooks to see how they're working out while the macro is still running and generating outputs in Excel 2000. Could this be contributing to the problem? If so, how come this did not happen till now?

If you need any post parts of the code, please don't hesitate to ask

http://support.microsoft.com/kb/215573

Can anyone help please.

The following macro was developed as part of an overall program. I developed it on a computer using WinXp and Office 2003. It work well.

I wanted to test it on a computer using Win2K with Office 2000. This computer recently had the OS reinstalled (Win2k SP4 and then updated via the internet and Office 2000 was reinstalled and also updated via the internet)

I have two problems.

1. When I load the spreadsheet the error message: 'The Macro ATPVBAEN.xla Auto_Add cannot be found'. Included in my opening macro is code to select the apprpriate addins. See below.


	VB:
	
 
AddIns("Analysis ToolPak - VBA").Installed = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I cancel VB and unselect and reselect the VBA Analysis Addin the macro that uses this addin works OK but the spreadsheet can'y seem to find it by itself.

2. My seconfd problem is that when I rrun the macro below I get the run-time error '-21474417848 (80010108)' Automation Error - The object has disconnected from its clients' and the macro execution does not complete.

Are there any suggestions on how to fix these issues please.

Thank you for any help

Regards

cdt


	VB:
	
 CopyButt_Click() 
     
    Worksheets("Config").Range("RegWordFile").Value = Date 
     
    Dim sFilename As String 
     
    sFilename = Sheets("Config").Range("LoggedInAs") 
     
    Dim sdate As String 
    sdate = Format(Date, "yyyy.mm.dd") 
     
    Dim appWD As Word.Application 
     
    Set appWD = CreateObject("Word.Application.8") 
    appWD.Visible = False 
     
     
    appWD.Documents.Add 
    Sheets("Config").Range("Regcopy").Copy 
    appWD.Selection.Paste 
     
    appWD.Selection.TypeParagraph 
    appWD.Selection.TypeParagraph 
    appWD.Selection.TypeParagraph 
    appWD.Selection.TypeParagraph 
     
    appWD.Selection.Font.Size = 20 
    appWD.Selection.Font.Name = "Verdana" 
    appWD.Selection.TypeText Text:="This registration was created on: " 
    appWD.Selection.InsertDateTime DateTimeFormat:="d MMMM yyyy" 
     
    appWD.Selection.WholeStory 
    appWD.Selection.Font.Hidden = False 
     
     
    appWD.ActiveDocument.Protect Password:="rid1953", NoReset:=False, Type:= _ 
    wdAllowOnlyReading 
     
    appWD.ActiveDocument.SaveAs Filename:="C:Cattle GM Reg -  " & sFilename & " " & sdate, FileFormat:= _ 
    wdFormatDocument, LockComments:=True, Password:="", AddToRecentFiles:= _ 
    True, WritePassword:="rid1953", ReadOnlyRecommended:=True, _ 
    EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _ 
    :=False, SaveAsAOCELetter:=False 
     
    appWD.ActiveDocument.Close 
     
    appWD.Quit 
     
    Worksheets("Config").Range("RegWordFile").Value = "Yes" 
    Call AllowCancel 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am a Newbie to VBA.

When I execute the combobox on the userform for the date I get automation error stating that the object invoked has disconnected from its clients.

What have I missed?

I have been search through a lot of forums and I cannot figure out how to correct my issue. The intent of my spreadsheet is to read the emails in a certain folder in my outlook inbox, parse those emails and display them in a different format on another sheet within excel. I have one sub pull in the emails to my "Import Sheet", and another sub that manipulates those fields and places the data into another sheet ("TMS_Log") in my desired format.
Within the import sheet cell 'A1' represents the first email body, cell 'A2' represents the second email body and so on. I have no issues with my sub that brings in the email contents, my issue resides only with my sub that manipulates the data. To make things even more complicated my code works at home using Excel 2007, but does not work within my office computer (where I need it to work) using Excel 2003. When I step through the sub it works properly about 2-3 times and then it gets an error. Below is the error that I recieve:

System Error &H80010108(-2147417848). The object invoked has disconnected from it's clients. MFR VZB090813-003 PPSM.

Unfortunately I am not the best with VBA and a lot of my code is just a hodge podge of code that I have found on the web and threw together to test what it does. Many times when reading the explanations of what to do to fix things I get lost becuase of my lack of VBA background.

Below is the block of code that I have issues with.

Sub cellparse()

Dim Contents As String
Dim a
Dim x As Integer
Dim currentrow As Long
Dim blah As String
'Dim emailcount
Dim y
Dim blahblah As String

blah = "a"
blahblah = "b"
x = 0
sheetonerow = 2
currentrow = 1
'emailcount = Workbooks(1).Sheets(2).Column.CountA

Do
'Parse the content in column A into individual cells in column B.
'Contents = Range("A1").Value
Workbooks(1).Sheets(2).Select
Cells(currentrow, blah).Select
Contents = Workbooks(1).Sheets(2).Cells(currentrow, blah).Value
a = Split(Contents, Chr(10))
Range("B1").Resize(UBound(a) + 1) = Application.WorksheetFunction.Transpose(a)
'Change the vertical fields into horizontal fields.
Workbooks(1).Sheets(2).Select
Range("B4:B21").Select
Selection.Copy
Workbooks(1).Sheets(2).Range("C1").Select
Range("C1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=True
'Select values to cut from "Import Sheet"
'Sheets("Import Sheet").Select
Workbooks(1).Sheets(2).Range("C1:T1").Select
Selection.Copy
Workbooks(1).Sheets(1).Activate
Workbooks(1).Sheets(1).Cells(sheetonerow, blahblah).Select
'Workbooks(1).Sheets(1).Range("B2").Select
Workbooks(1).Sheets(1).Paste
'Switch to Sheet 2 and Clear Column B of import sheet
Workbooks(1).Sheets(2).Activate
Columns("B:T").Select
Selection.Clear
'Increment the Import Sheet column A row by 1
currentrow = currentrow + 1
'Increment the Do count by 1
x = x + 1
'Increment The TMS Log Sheet 1 row
sheetonerow = sheetonerow + 1
Loop Until x > 10
End Sub

Any help with this would be much appreciated! I attached the excel spreadsheet and changed around the content of the email to reflect only 5 generic email messages. Thanks in advance I am very green with VBA.

Hi All,

I seem to be coming on here every day at the moment! Someone helped me with this code, it works great apart from 1 error, which stops the script. The lines that appear when i select debug are in italic / bold. The error message appearing is:

"Object invoked has disconnect from its client" or something very similar. Please people, can anyone help?


	VB:
	
Dim lngRowPasteTo As Long 
Dim codelive, codeclosed, codeassigned, vLookFor As String 
 
 'Set variables
vLookFor = "Closed" 
codelive = "gio1" 
codeclosed = "gio2" 
codeassigned = "gio3" 
 
Worksheets("Live").Unprotect Password:=codelive 
Worksheets("Closed").Unprotect Password:=codeclosed 
Application.ScreenUpdating = False 
MsgBox ("Moving Claims...") 
 'Ensure the 'Live' tab is selected (active).
Sheets("Live").Select 
 
 'Remove any existing AutoFilters.
ActiveSheet.AutoFilterMode = False 
 
 'Show only (filter) Column AU transactions by "Closed".
Columns("AU").Select 
Selection.AutoFilter Field:=1, Criteria1:="Closed" 
 
 'Hide Row 1 so it's NOT moved.
Rows("1").EntireRow.Hidden = True 
 ' Find the next row to paste to.
lngRowPasteTo = Sheets("Closed").Range("AU65536").End(xlUp).Row + 1 
[I][B] 'Delete all rows that are NOT hidden.
Columns("AU").SpecialCells(xlCellTypeVisible).EntireRow.Copy _ 
Destination:=Sheets("Closed").Range("A" & lngRowPasteTo)[/B][/I] 'Delete all visible (filtered) rows.
Columns("AU").SpecialCells(xlCellTypeVisible).EntireRow.Delete 
 
 'Remove AutoFilters
ActiveSheet.AutoFilterMode = False 
 
 'Unhide Row 1.
Rows("1").EntireRow.Hidden = False 
Application.ScreenUpdating = True 
MsgBox ("Claims Moved!") 
Worksheets("Live").Protect Password:=codelive 
Worksheets("Closed").Protect Password:=codeclosed 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi,

I have a macro workbook that opens two report templates, copies data into
them, creates subtotals and formatting, then saves the two reports using
SaveAs...

Unfortunately every time the code reaches the line:
Workbooks("Rep_Template1").SaveAs sRepName

or

Workbooks("IntRep_Template1").SaveAs sRepName

Surely these are correct references - they've worked fine for 5 years!

I get an error 'Automation Error The object invoked has disconnected from
its clients'

Excel then crashes with a memory leak or something...

This tool has been running finr for 5 years under Excel 97 and Excel 2000
and now I am getting this error.

Has anyone seen this, and how can I fix it...I've tried copying all the
sheets and VBA objects into a new workbook but that has not worked either...

I'd be grateful for any help

Philip

Hi anybody,
Inside a form I have a button with the attached code to gather some information an paste it to a sheet and when I click on update button to gather this information onto current form I recieve the following error!
Runtime Error -2147417848 (80010108)
Automation Error
The object invoked has disconnected from its client.

Has anybody got any idea why it happens.
Thanks for your help.

Folkes, I am trying to solve this problem. Searching this groups seems
to come up with 2 possible problems. Firstly corrupted sheet
(investigated this one and am sure that this is not the problem).
Secondly I am not releasing a reference to something before trying to
access again.

I am copy 2 sheets (containing charts and data with links to another
file) from one workbook to a new workbook, unprotecting each sheet and
copying /paste special values (to remove the links) and then protecting
each sheet in a For/Next loop.

I get the automation error when doing the paste special on the second
run through.

Here is my code....
MyResults(1) = "Tab1"
MyResults(2) = "Tab2"

Sheets(MyResults).Select
Sheets(MyResults(UBound(MyResults))).Activate
Sheets(MyResults).Copy
For Each s In ActiveWorkbook.Sheets
s.Activate
ActiveSheet.Unprotect
ActiveSheet.Cells.Select
Range("AX1").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= False, Transpose:=False
Range("AX1").Select
s.Protect
Next s

Can anybody explain to me where I am going wrong and how to address the
problem please. Thanking you all (again) in advance.
Donna

Hi, as above - this message suddenly started to pop up whenever user has to select a forename from a drop down and then search for it in the existing records.

Private Sub cmdSearch_Click()

    'Procedure level variables
    Dim lCount As Long
    Dim lOccur As Long
    Dim rCell As Range
    Dim rCell2 As Range
    Dim rCell3 As Range
    Dim rCell4 As Range
    Dim rCell5 As Range
    Dim bFound As Boolean
    
    'At least one value, from cboForename must be chosen
    If strForeName & strSurname & strDOB & strCHI & strPHYSIO = vbNullString Then
        MsgBox "No items to find chosen", vbCritical
        Exit Sub 'Go no further
    ElseIf strForeName = vbNullString Then
        MsgBox "A value from " & lblForename.Caption & " must be chosen", vbCritical
        Exit Sub 'Go no further
    End If
    
    lstMasterList.Clear 'Clear any old entries

    
    'If String variable are empty pass the wildcard character
    If strSurname = vbNullString Then strSurname = "*"
    If strDOB = vbNullString Then strDOB = "*"
    If strCHI = vbNullString Then strCHI = "*"
    If strPHYSIO = vbNullString Then strPHYSIO = "*"
    
    lstMasterList.List = ValList(Application.Transpose(Application.Transpose(rngCells)), Array(1, 2, 3, 4, 8),
Array(strForeName, strSurname, strDOB, strCHI, strPHYSIO), Array(1, 2))
    If lstMasterList.ListCount = 0 Then 'No match
        MsgBox "Sorry, no matches", vbOKOnly
    End If
    
End Sub
the lstMasterList line becomes highlighted by debugger.... Any ideas?

Hi,

The below macro should open an invisible Internet Explorer window and then close it. I have 4 computers but the macro only runs on 2 of them.

Sub Test()
    Dim IE As Object
    Set IE = CreateObject("InternetExplorer.Application")
 
    With IE
        .Visible = False
        .Navigate "http://www.google.com"
        Do Until .ReadyState = 4: DoEvents: Loop
        .Quit
    End With
    Debug.Print "Finito"
End Sub
On the computers where the macro doesn't work, a visible Internet Explorer window is opened (even though I've written .Visible = False) and I get an error message at the "Do Until.. " line. The error message reads:

"Automation Error The object invoked has disconnected from its clients"

Do anyone know what can be the problem? All of my computers run Excel 2007, Internet Explorer 8 and Windows Vista Business SP2.

I have previously asked the same question in the Mr. Excel forum, without getting a reply.


No luck finding an answer? You could always try Google.