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

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

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

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