System Error &H80010108 (-2147417848) object invoked disconnected client

**xclrInTraining** · 01-06-2010, 05:22 PM

Hi all,

Hoping you can help, as I am absolutely stumped. I have created workbooks for four health regions to collect data, which then needs to be resubmitted via FTP, but with de-identified data. The end users have little to no excel experience, thus had to make it simple as possible. Created a button that runs the macro (below) to deidentify (clear) all cells where there could possibly be personal identifying information. Runs fine on my cpu, but one user (so far) has received the error noted in the title. Both running excel 2003 with SP3 (I have version 11.8169.8172, user has 11.8316.8221), I have XP professional SP2, user has XP professional SP3. Spent hours searching and reading suggestions online, to no avail. Any simple solutions anyone can come up with would be truly appreciated.

Thank you in advance!

Sub garblednewfile()

ActiveWorkbook.Save
Sheets("Find a patient").Select

ChDir Range("B54 ")
    ActiveWorkbook.SaveAs Filename:=Range("B55") & Range("B56") & Range("B57 ").Value & Format(Date, " mmmyyyy") & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
        , CreateBackup:=False
Sheets("data").Select
    Range("B4").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B4").Select
    Selection.Copy
    Range("B4:F65536").Select
    ActiveCell.FormulaR1C1 = ""
    Application.CutCopyMode = False
    
    Sheets("followup").Select
    Range("B4").Select
    ActiveCell.FormulaR1C1 = ""
    Range("B4").Select
    Selection.Copy
    Range("B2:F65536").Select
    ActiveSheet.Paste
    Range("C13").Select
    Application.CutCopyMode = False
    
    Sheets("Find a patient").Select
    Range("I14").Select
    ActiveCell.FormulaR1C1 = ""
    Sheets("Find a patient").Select
    Range("I23").Select
    ActiveCell.FormulaR1C1 = ""
     Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close


End Sub

**broro183** · 01-06-2010, 06:08 PM

hi xlclr,

I'm not sure why I had the page open but by some coincidence, I had the below KB page open when I saw this thread - it may be of interest (I haven't read it all yet)...
http://support.microsoft.com/default...b;en-us;319832

Here's a shortened code which I think does the same as your original code:

Sub Modified_garblednewfile()
Dim PatientSht As Worksheet
    With ActiveWorkbook
        .Save
        Set PatientSht = .Worksheets("Find a patient")
        With PatientSht
            ChDir .Range("B54").Value2
            ActiveWorkbook.SaveAs Filename:=.Range("B55").Value2 & .Range("B56").Value2 & .Range("B57").Value2 & Format(Date, " mmmyyyy") & ".xls", FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        End With
        .Worksheets("data").Range("B4:F65536").FormulaR1C1 = ""
        .Worksheets("followup").Range("B2:F65536").FormulaR1C1 = ""
        .Worksheets("Find a patient").Range("I14").FormulaR1C1 = ""
        With PatientSht
            .Select
            .Range("I23").FormulaR1C1 = ""
        End With
    End With
    Set PatientSht = Nothing
    ActiveWorkbook.Close savechanges:=True
End Sub

hth
Rob

**xclrInTraining** · 01-07-2010, 05:18 PM

Your solution works beautifully, many thanks. Wish you were a Kiwi hiding in my office as opposed to the UK.

I've read the article in the link you provided, which wasn't quite as useful, more than likely due to my limited understanding of vba.

More for my own understanding and knowledge, where exactly was I guilty of "early binding" or an "unqualified reference".

Again, thanks... as far as grades go, 100% for sure. Well, 99%, it did take you over 40 minutes to post a response ;~p

**broro183** · 01-07-2010, 09:20 PM

Thanks for marking the post as solved

lol, be careful what you wish for - anything's possible!
Where did you say your office was...?

Ummm, Every reference to a range without a preceding workbook & worksheet (& in some cases a parent Excel App) can be considered "unqualified" which is why I've tied everything in using With statements. When Range statements are not "explicitly qualified" they default to the active sheet. My "ummm" is because I'm not sure why this would cause a problem in your situation where everything is done in one file & the focus is not changed during the macro...?
Perhaps it has something to do with the FTP, I'm not exactly sure of the context or of FTP, so I'm sorry I can't provide a clear answer.

On glancing at the code again, I would make a few changes as below & have included stream of consciousness comments too.

Sub Modified_garblednewfile_V2()
Dim PatientSht As Worksheet
Dim RowsInSht As Long
Dim FileNameForSaving As String
    With ActiveWorkbook
        'I've left the following save, but can't see value in it being here - unless _
         the file has been previously populated with data?
        .Save    ' is this needed?
        Set PatientSht = .Worksheets("Find a patient")
        With PatientSht
            ChDir .Range("B54").Value2
            FileNameForSaving = .Range("B55").Value2 & .Range("B56").Value2 & .Range("B57").Value2 & Format(Date, " mmmyyyy") & ".xls"
            RowsInSht = .Rows.Count    'this makes the below ranges more flexible (than 65536) for a future move to Excel 2007 & beyond
        End With
'edit:I typed the below comments before moving the Saveas to the end of the macro - so the below comments are redundant. end edit.
        'RB: while looking for possible reasons, I considered that the "object invoked" may "disconnect" during the save as, so it _
         may be better to use two "With Activeworkbook clauses rather than one. To test this, uncomment the next two lines.
    'end with
    'With ActiveWorkbook
    'RB: I've changed the code to ".clearcontents" but for the next two lines of code, would it be okay to use ".entirerow.delete"
    'this may prevent "bloat" in some files which have fewer rows of data, but are the same file size, _
    because the formatted range extends beyond the last row of data due to previous file sizes... (just a thought)
        .Worksheets("data").Range("B4:F" & RowsInSht).ClearContents
        .Worksheets("followup").Range("B2:F" & RowsInSht).ClearContents
        .Worksheets("Find a patient").Range("I14").ClearContents
        With PatientSht
            .Select
            .Range("I23").ClearContents
        End With
        Set PatientSht = Nothing
        .SaveAs Filename:=FileNameForSaving, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
        .Close savechanges:=False
        ''RB: potentially the above two lines could be shortened to the below BUT (I'm not sure?), it may not _
         choose the other parameters as they are defined in the ".saveas" line (check out the Help files)
        '.Close savechanges:=False, Filename:=FileNameForSaving
    End With
End Sub

hth
Rob

**sudarshan1026** · 09-29-2013, 10:50 PM

Hi All,

I am using the below code, to copy data generated out of a web query. I have values in Col A which is the variable and generates data from web query on input.

i am getting the same error as in this thread except the undisconnected client.

Sub Bulk_Working_Time()

    Dim lng As Long
   
    With Worksheets("Sheet1")
        For lng = 1 To .Cells(Rows.Count, "A").End(xlUp).Row
            If .Range("A" & lng).Value <> "" Then
                .Range("G2").Value = .Range("A" & lng).Value
                .Range("E4").ClearContents
                ThisWorkbook.RefreshAll
                While IsEmpty(.Range("E4"))
                Wend
                Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2).Resize(2, 7).Value = .Range("G2:N3").Value
            End If
        Next lng
    End With
   
End Sub

Any help or guidance would be much appreciated please.

Sudi

**arlu1201** · 09-30-2013, 10:14 AM

Sudi,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.