Link to home
Start Free TrialLog in
Avatar of alam747
alam747

asked on

while save and close a file facing the an error

wb_Source.Close False  ' not to save the changes
wb_Target.Close True   ' save the changes

from the above VBA code while do the save and close an error appeared as below:
"Automation error
The object invoked has disconnected from its client."

I appreciate any advice to fix the problem.
If anyone advice how to fix it.
Thanks in advance for your co-operation and understanding.
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

Do you have something like objExcel.Close in there? If so, it needs to be after the close statements. Are you connecting to a network drive? Please post more of the code. The code you have posted should run just fine. Something else is causing the issue.
Avatar of alam747
alam747

ASKER

Thanks Sage, for your advice...
here is the code .....

Dim xl As Object
Dim wb_Source As Object
Dim wb_Target As Object
Dim ws_Source As Object
Dim ws_Target As Object
Dim LineCounter As Integer
Dim Counter As Integer


Counter = 9
LineCounter = 8
On Error Resume Next
Set xl = GetObject(, "Excel.Application")

If Err.Number <> 0 Then
    Set xl = CreateObject("Excel.Application")
    blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

Set wb_Source = xl.Workbooks.Open("C:\ConvertToExcel\ConvertedExcel.xls")
Set wb_Target = xl.Workbooks.Open("C:\ConvertToExcel\JDE651RFQi_v1.04.08.xls")
Set ws_Source = wb_Source.Sheets("Sheet1")
Set ws_Target = wb_Target.Sheets("JDE710RFQi")

xl.Visible = True
While (ws_Source.Cells(LineCounter, 1) <> "")
    ws_Target.Cells(Counter, 1) = "651ON1TCI"
    ws_Target.Cells(Counter, 2) = "657000"
    ws_Target.Cells(Counter, 3) = "65100001"
    ws_Target.Cells(Counter, 12) = ws_Source.Cells(LineCounter, 2)
    ws_Target.Cells(Counter, 11) = ws_Source.Cells(LineCounter, 4)
    ws_Target.Cells(Counter, 7) = ws_Source.Cells(LineCounter, 5)
    ws_Target.Cells(Counter, 5) = ws_Source.Cells(LineCounter, 6)
    ws_Target.Cells(Counter, 4) = ws_Source.Cells(LineCounter, 8)
    ws_Target.Cells(Counter, 10) = ws_Source.Cells(LineCounter, 9)
    ws_Target.Cells(Counter, 13) = ws_Source.Cells(LineCounter, 10)
    ws_Target.Cells(Counter, 14) = ws_Source.Cells(LineCounter, 11)
    ws_Target.Cells(Counter, 16) = ws_Source.Cells(LineCounter, 12)
    ws_Target.Cells(Counter, 8) = ws_Source.Cells(LineCounter, 13)
    ws_Target.Cells(Counter, 9) = ws_Source.Cells(LineCounter, 14)
    ws_Target.Cells(Counter, 15) = ws_Source.Cells(LineCounter, 15)
    ws_Target.Cells(Counter, 21) = ws_Source.Cells(LineCounter, 16)
    ws_Target.Cells(Counter, 17) = ws_Source.Cells(LineCounter, 17)
    ws_Target.Cells(Counter, 18) = ws_Source.Cells(LineCounter, 18)
    ws_Target.Cells(Counter, 22) = ws_Source.Cells(LineCounter, 19)
    Counter = Counter + 1
    LineCounter = LineCounter + 1    
Wend

wb_Source.Close False  ' not to save the changes
wb_Target.Close True   ' save the changes

wb_Source = Nothing  ' Clean up
wb_Target = Nothing  ' Clean up
xl.Quit
Set xl = Nothing

End Sub

Still not clear whats the reason of the error or I missed..
I appreciate your prompt response....
ASKER CERTIFIED SOLUTION
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of alam747

ASKER

Excellent..... Thanks a lot for your help...