I am working on clearing some tables in 3 excel sheets.
My code was working fine with Excel 2007, as i switched to excel 2010 i started getting this runtime error Automation error The object invoked has disconnected from its clients, error number -2147417848 and then excel freezes.
the error is showing after running this function 2 times on the line
Sheets("Risk Details").Range("A3" & ":BV" & (count + 1)).Delete Shift:=xlUp
can anyone help me in finding out why this is happening and how to work around it?
below are the full code for the function
Function clearData(Optional refresh As Boolean) As Boolean
Application.ScreenUpdating = False
Sheets("Single Risk Register").Unprotect myPass
Sheets("Single Risk Register").Activate
Cells.FormatConditions.Delete
Sheets("Risk Details").Unprotect myPass
Sheets("Risks").Unprotect myPass
Dim currentrange As Range
Sheets("Risks").Activate
Set currentrange = Sheets("Risks").Range("RisksTable")
currentrange.ClearContents
Dim count As Integer
count = Sheets("Risks").ListObjects("RisksTable").ListRows.count
If count > 1 Then
Sheets("Risks").Range("A3" & ":C" & (count + 1)).Delete Shift:=xlUp
End If
Sheets("Risk Details").Activate
Set currentrange = Sheets("Risk Details").Range("RiskDetails")
currentrange.ClearContents
count = Sheets("Risk Details").ListObjects("RiskDetails").ListRows.count
If count > 1 Then
Sheets("Risk Details").Range("A3" & ":BV" & (count + 1)).Delete Shift:=xlUp
End If
Sheets("Single Risk Register").Activate
count = CInt(Range("ActionsCount"))
Range("ActionsCount") = 1
Dim tableLastRow As Integer
tableLastRow = getLastTableRow()
If (tableLastRow >= 48) Then
Sheets("Single Risk Register").Range("B48" & ":K" & tableLastRow).Delete Shift:=xlUp
End If
Range("ActionsTable[[Action Description]:[Action Commentary]]").Select
Application.CutCopyMode = False
Selection.ClearContents
Application.ScreenUpdating = False
Dim DataRange As Range
Dim o As Shape
Dim tC As Variant
Dim propertiesCount As Integer
Dim i As Integer
tC = Sheets("TableColumns").ListObjects("TableColumns").DataBodyRange
propertiesCount = GetTemplatePropertiesCount(templates(0)) - 1
ReDim properties(0 To propertiesCount - 1, 0 To 4)
If (properitesColl.Item(i + 1)(propertiesCount, 2) = templates(0)) Then
properties = properitesColl.Item(i + 1)
End If
For count = 0 To propertiesCount - 1
If ((properties(count, 4) <> "C25") And (properties(count, 4) <> "C26") And (properties(count, 4) <> "C27") And (properties(count, 4) <> "C28") And (properties(count, 4) <> "C38") And (properties(count, 4) <> "C39") And (properties(count, 4) <> "C40") And (properties(count, 4) <> "C41"))
Then
Sheets("Single Risk Register").Range(properties(count, 4)) = ""
End If
Next
Dim columnCount As Integer
columnCount = GetColumnCount()
count = 0
For i = 1 To columnCount
If tC(i, 4) <> "Action" Then
Set currentrange = Range(tC(i, 4))
Else
Set currentrange = Range("ActionsTable[" & tC(i, 1) & "]")
End If
If ((tC(i, 4) = "C25") Or (tC(i, 4) = "C26") Or (tC(i, 4) = "C27") Or (tC(i, 4) = "C28") Or (tC(i, 4) = "C38") Or (tC(i, 4) = "C39") Or (tC(i, 4) = "C40") Or (tC(i, 4) = "C41"))
Then
With currentrange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 15654866
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
With currentrange.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = 0.799981688894314
.PatternTintAndShade = 0
End With
End If
Next
Sheets("Single Risk Register").Unprotect myPass
If (Range("C3").Value = "") Then
Range("ActionsTable[[Action Description]:[" & addTitle & "]]").Select
Selection.Locked = True
Rows("45:47").Select
Selection.EntireRow.Hidden = True
End If
Sheets("Single Risk Register").Range(Range("TitleCell")).Value = Range("OldTitle") + " (" + Range("Project_Title") + ")"
Sheets("Single Risk Register").Protect myPass, AllowFiltering:=True
Sheets("Risk Details").Protect myPass, AllowFiltering:=True
Sheets("Risks").Protect myPass, AllowFiltering:=True
doNotRun = False
Range("C3").Select
Application.ScreenUpdating = True
Exit Function
err:
Sheets("Risk Details").Protect myPass
Sheets("Risks").Protect myPass
MsgBox err.Description, vbCritical, "Error"
- Remove From My Forums
-
Question
-
Good day all,
I just read lots of articles bout this error but could not handle how to solve it. the latest article was «INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic» so I considered i need to do qualifying
my code but i do not know how?!here is the codes which I wrote on a excel vba userform:
Private Sub CommandButton1_Click() Application.Calculation = False Worksheets("Data Base").Activate MSG1 = MsgBox("Do you want to submit the form?", vbYesNo) If MSG1 = vbYes Then Dim ssheet As Worksheet Set ssheet = ThisWorkbook.Sheets("Data Base") nr = ssheet.Cells(Rows.Count, 2).End(xlUp).Row + 1 ssheet.Cells(nr, 2) = Me.TextStockNo ssheet.Cells(nr, 3) = Me.ComboRepName ssheet.Cells(nr, 5) = Me.DTPickerDate ssheet.Cells(nr, 6) = Me.TextYearModel ssheet.Cells(nr, 7) = Me.ComboVehicleMake ssheet.Cells(nr, 8) = Me.TextDescription ssheet.Cells(nr, 9) = Me.TextRegNo ssheet.Cells(nr, 9) = Me.TextMileage ssheet.Cells(nr, 11) = Me.ComboColour ssheet.Cells(nr, 12) = Me.TextBought ssheet.Cells(nr, 13) = Me.TextSold ssheet.Cells(nr, 15) = Me.ComboDealer ssheet.Cells(nr, 16) = Me.ComboAdvert ssheet.Cells(nr, 17) = Me.TextAddComments ssheet.Cells(nr, 18) = Me.DTPickerInvoiceDate ssheet.Cells(nr, 19) = Me.TextInvoiceNo MSG2 = MsgBox("Do you want to input new field?", vbYesNo) If MSG2 = vbYes Then Unload Me Worksheets("Data Base").Activate userform1.Hide userform1.Show Else Unload Me End If Else Unload Me Worksheets("Data Base").Activate userform1.Hide userform1.Show End If Application.Calculation = True End Sub Private Sub CommandButton2_Click() Unload Me End Sub Private Sub CommandButton3_Click() Unload Me Worksheets("Data Base").Activate userform1.Hide userform1.Show End Sub Sub Open_Form() Worksheets("Data Base").Activate userform1.Show End SubIt is a data entry form which gives the user options to reset the form, submit or reload it. I it works fine at same time but as long as I submit for first time and reload it to submit next item it gives me that error.
I checked it on another pc and it works fine, I am not sure what is wrong with my pc.
-
Moved by
Monday, December 16, 2013 8:51 AM
(from vb) more related
-
Moved by
Answers
-
Hello SJafary,
For example, the
INFO: Error or Unexpected Behavior with Office Automation When You Use Early Binding in Visual Basic article provides the following sample:MsgBox "Number of workbooks: " & Workbooks.Count, vbMsgBoxSetForeground
where the Workbooks object is a shortcut to the Application.Workbooks property. To resolve problems, you must fully qualify the Workbooks object that you reference for the count, as follows:
MsgBox "Number of workbooks: " & oXL.Workbooks.Count, vbMsgBoxSetForeground
Now the Workbooks property is specified explicitly. The article suggests avoiding shortcuts and use properties and methods that comes from the Application class (not global properties).
-
Edited by
Eugene Astafiev
Monday, December 16, 2013 9:30 AM -
Marked as answer by
Marvin_Guo
Wednesday, December 25, 2013 9:41 AM
-
Edited by
Troubleshooting
Problem
On one machine, the following error is returned when running any query in SQLTerm/SQL Helper: [
‘-2147417848 (80010108)’: automation error the object invoked has disconnected from its client
]
This closes the catalog. Running the same query on a different machine, connected to the same catalog works successfully.
Resolving The Problem
The problem machine was re-booted, and now the problem no
longer occurs.
[{«Product»:{«code»:»SSTQPQ»,»label»:»IBM Cognos Series 7 PowerPlay»},»Business Unit»:{«code»:»BU053″,»label»:»Cloud & Data Platform»},»Component»:»DecisionStream»,»Platform»:[{«code»:»PF033″,»label»:»Windows»}],»Version»:»DecisionStream 6.5;Planning — Analyst 7.3;Planning — Contributor 7.3″,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}},{«Product»:{«code»:»SSPN2D»,»label»:»Cognos Planning»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Analyst»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}},{«Product»:{«code»:»SSPN2D»,»label»:»Cognos Planning»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Contributor»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB10″,»label»:»Data and AI»}}]
Historical Number
1006063
Проблема
Нажатие кнопки Nastran In-CAD приводит к появлению
ошибки Run-time -2147417848 (80010108)
Ошибка Automation
При вызове объекта не было связи с его клиентами.
Решение
Попробуйте выполнить следующие действия.
1. Попробуйте устранить проблему в HSMWorks. Объект, вызываемый, отключен от своих клиентов.
2)
Повторно зарегистрировать или переустановить Nastran In-CAD
Запустите следующий пакетный файл от администратора с закрытием Inventor.
C: Program Files Autodesk Nastran In-CAD 2018 System RegNEiInCAD.bat
- Обратите внимание, что в поле имени папки будет отображаться версия установленной папки.
- Чтобы запустить пакетный файл от имени администратора, щелкните правой кнопкой мыши файл RegNEiInCAD.bat и выберите «Запуск от имени администратора».
- После регистрации каждого файла появится диалоговое окно с подтверждением. Проверьте, какие файлы преуспевают или сбой. Файл NEiSW.dll (используется только в In-CAD 2018.0 и более ранних версиях) не может быть загружен. Это не так.
Если регистр файлов успешно
Запустите Inventor и откройте модель. Теперь в ленте теперь должна появиться In-CAD, и дальнейшие шаги не требуются.
Если файлы не регистрируются
Если какой-либо из файлов не удается успешно зарегистрировать (кроме файла NEiSW.dll), удалите и переустановите In-CAD. Если установка выполнена успешно, надстройка In-CAD должна отобразиться на ленте сейчас, и дальнейшие действия не требуются
3) Конфликт программного обеспечения.Отключите все приложения, не относящиеся к Windows и службам Autodesk, и остановите запуск всех приложений сторонних разработчиков (не Autodesk)
4) Если это может быть проблема с Windows, то это может также заархивировать компьютер. Может потребоваться чистую установку ОС.
http://vrhardware.com/runtime-error-80010108-disconnected-object-problems-anyone/
Продукты
Изобретатель Настрань;
|
vukula  Пользователь Сообщений: 4 |
#1 23.11.2015 12:09:26 Excel 2010 Если возникает, то дальнейший дебаг и запуск дает Помогите пжл. В каком направлении искать. Спасибо.
|
||
|
CAHO  Пользователь Сообщений: 2183 |
А она возникает скорее всего, потому что макрос у Вас из модуля выполняется и обращается к конкретным ячейкам листам и диапазонам через Select. Мастерство программиста не в том, чтобы писать программы, работающие без ошибок. |
|
vukula Пользователь Сообщений: 4 |
#3 23.11.2015 12:43:54
Что Вы подразумеваете под «нормальными путями»? |
||
|
justirus  Пользователь Сообщений: 295 |
#4 23.11.2015 12:49:46
Ну например: Изменено: justirus — 03.12.2015 01:06:25 |
||
|
CAHO Пользователь Сообщений: 2183 |
#5 23.11.2015 12:57:10 Например что-то вроде такого
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок. |
||
|
vukula Пользователь Сообщений: 4 |
Спасибо за участие. Изменено: vukula — 23.11.2015 17:20:59 |
|
vukula Пользователь Сообщений: 4 |
вот что говорит микрософт
правда мне это не помогло Изменено: vukula — 24.11.2015 09:09:47 |
|
hyperion3330  Пользователь Сообщений: 37 |
#8 13.07.2021 18:32:31 Недавно тоже столкнулся с такой проблемой.. |
|
|
[RESOLVED] Automation Error -2147417848 The object invoked has disconnected from its clients
Originally Posted by Joacim Andersson
-
04-15-2015, 03:24 AM
#1
Registered User
Automation Error -2147417848 The object invoked has disconnected from its clients
Hi, this code was doing its job fine over the past 2 days. I didn’t change anything. Now all of the sudden I keep getting this error. I’ve tried to find a solution to people with similar problem but it hasn’t been successful….Please help!
Last edited by soranz; 04-16-2015 at 03:35 AM.
-
04-15-2015, 03:30 AM
#2
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
It’s working fine for me
Is there any worksheet change event runs in the back ground?
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as SolvedIf the suggestion helps you, then
Click *below to Add Reputation
-
04-15-2015, 04:37 AM
#3
Registered User
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
Ok that is strange. It is working for me now too…
To populate «New Data», I’ve been manually copying a chunk of data from a second workbook into the first workbook’s «New Data» sheet.
None of my macro buttons were working either. I did close the workbooks down a couple of times and re-opened and it had that error. Then, I manually added the data. After all of that I saved, checked here, then tried again and now it works.
Could it be the flash drive connection that stored a backup that I might have had a tmp open in the background?
Also, is there a way to qualify that line before I call it?
-
04-15-2015, 04:48 AM
#4
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
I requested for all members help for this thread. So surely someone will fix the root cause of this error
-
04-15-2015, 08:36 AM
#5
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
- Arrays _ _ _ Dictionaries
- Avoid using VBA-code you do not understand.
-
04-15-2015, 03:23 PM
#6
Registered User
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
Thank you Sixthsense
Thank you snb!
Quick questions:
What is the data type of sn, sp and st? Seems like they should be ranges but I get errors so I have them as variants.
How can I also rearrange the formatting of the columns along with the data (each column category has a specific color)?
-
04-16-2015, 04:16 AM
#7
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
As long as you do not declare them they are variants by default. In this case (arrays) they also have to. If you comment out ‘Option Explicit’ it runs smoothly.
The formatting is quite another story:You could add:
-
04-17-2015, 03:56 AM
#8
Registered User
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
Hmm
Looks like the loop should work. The columns should line up and the colors are copied in the right direction.For some reason when I run it the colors are very mixed up (data is arranged fine). When I retry they match up in the same messed up way. Some colors I’m not even using in the Main sheet. I have that loop at the very end of the sub.
Any ideas?
-
04-17-2015, 04:43 AM
#9
Re: Automation Error -2147417848 The object invoked has disconnected from its clients
It may be better if you use .Color for .ColorIndex
- Please remember to mark threads Solved with Thread Tools link at top of page.
- Please use code tags when posting code: [code]Place your code here[/code]
- Please read Forum Rules
|
Невозможно скопировать лист — Excel вылетает/закрывается |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
