Closing Excel

[ridawg]

Hey,

I have some code where I open up Excel then loop through several cases to
update several workbooks.

Basically something like this (not showing all the code just basic structure).
Try
appExcel = New Excel Application
Do While Counter <=2
Select Case Counter
Case 1
eTemplate = "Some Excel File1"
RName = "New Version of File1"
Case 2
eTemplate = "Some Excel File2"
RName = "New Version of File2"
End select
wb = .Workbooks.Open(RTemplate)
With wb
.RefreshAll()
.SaveAs(RName)
.Close()
End With

'Increment Counter
Counter += 1

Loop
Catch ex As Exception
'Report Error
MessageBox.Show((ex.ToString))
Finally
If Not appExcel Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()

If Not wb Is Nothing Then
wb.Close(SaveChanges:=False)
Marshal.FinalReleaseComObject(fr.wb)
wb = Nothing
End If

appExcel.Quit()
Marshal.FinalReleaseComObject(fr.appExcel)
appExcel = Nothing
End If

End Try

I'm getting an error on the close statement in the Finally block. The error
states that the object doesn't exist (disconnected). Which makes sense if
everything executes correctly because I have a close statement prior to this
statement (in the loop code). But I'm not sure why I'm getting the error
because the if statement is checking for nothing.

My assumption is I need the early close statement so I can correctly loop
through and open then close the various excel files.

I also thought by checking in the Finally block if the wb object is nothing
would be good in case anything went wrong and a wb instance was left open. I
guess I don't understand why I'm getting this error when everything does
close properly because I would think the if statement in the finally block
would evaluate to nothing and not execute but instead I get an object
disconnected error.

Any help is greatly appreciated. I'm very new to VB.NET. Thanks

--
-ridawg

--
-ridawg

[Norman Yuan]

Why do you close the workbook again in "Finally" block? the workbook getting
closed in "Try" block does not mean variable "wb" becomes Nothing, it still
points to the momory where workbook object was hosted there, although the
workbook object has been closed. Variable "wb" will hold that value until it
is out of scope, or you set it to Nothing. Thus, simply because a pointer is
not Nothing, it does not mean you can close an object twice the pointer
pointed to, if the said object has been closed previously. Therefore, you
got the error.

You might only need

Try
...
Catch
....
Finally

If Not appExcel Is Nothing Then

wb = Nothing
appExcel.Quit()
Marshal.FinalReleaseComObject(fr.appExcel)
appExcel = Nothing

End If
End Try
"ridawg" <ri****@discussions.microsoft.comwrote in message
news:46**********************************@microsof t.com...

Hey,

I have some code where I open up Excel then loop through several cases to
update several workbooks.

Basically something like this (not showing all the code just basic
structure).
Try
appExcel = New Excel Application
Do While Counter <=2
Select Case Counter
Case 1
eTemplate = "Some Excel File1"
RName = "New Version of File1"
Case 2
eTemplate = "Some Excel File2"
RName = "New Version of File2"
End select
wb = .Workbooks.Open(RTemplate)
With wb
.RefreshAll()
.SaveAs(RName)
.Close()
End With

'Increment Counter
Counter += 1

Loop
Catch ex As Exception
'Report Error
MessageBox.Show((ex.ToString))
Finally
If Not appExcel Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()

If Not wb Is Nothing Then
wb.Close(SaveChanges:=False)
Marshal.FinalReleaseComObject(fr.wb)
wb = Nothing
End If

appExcel.Quit()
Marshal.FinalReleaseComObject(fr.appExcel)
appExcel = Nothing
End If

End Try

I'm getting an error on the close statement in the Finally block. The
error
states that the object doesn't exist (disconnected). Which makes sense if
everything executes correctly because I have a close statement prior to
this
statement (in the loop code). But I'm not sure why I'm getting the error
because the if statement is checking for nothing.

My assumption is I need the early close statement so I can correctly loop
through and open then close the various excel files.

I also thought by checking in the Finally block if the wb object is
nothing
would be good in case anything went wrong and a wb instance was left open.
I
guess I don't understand why I'm getting this error when everything does
close properly because I would think the if statement in the finally block
would evaluate to nothing and not execute but instead I get an object
disconnected error.

Any help is greatly appreciated. I'm very new to VB.NET. Thanks

--
-ridawg

--
-ridawg

[ridawg]

My thought or concern is that if something happens before the first close
statement is executed and wb is left open then I would catch it in the
Finally block and close it there.

My guess is that doesn't make sense and I should change my code to what you
posted. I'm pretty new to VB.NET and I'm trying to convert a bunch code from
VBA. So I'm learning on the fly. Thanks

--
-ridawg
"Norman Yuan" wrote:

Why do you close the workbook again in "Finally" block? the workbook getting
closed in "Try" block does not mean variable "wb" becomes Nothing, it still
points to the momory where workbook object was hosted there, although the
workbook object has been closed. Variable "wb" will hold that value until it
is out of scope, or you set it to Nothing. Thus, simply because a pointer is
not Nothing, it does not mean you can close an object twice the pointer
pointed to, if the said object has been closed previously. Therefore, you
got the error.

You might only need

Try
...
Catch
....
Finally

If Not appExcel Is Nothing Then

wb = Nothing
appExcel.Quit()
Marshal.FinalReleaseComObject(fr.appExcel)
appExcel = Nothing

End If
End Try
"ridawg" <ri****@discussions.microsoft.comwrote in message
news:46**********************************@microsof t.com...

Hey,

I have some code where I open up Excel then loop through several cases to
update several workbooks.

Basically something like this (not showing all the code just basic
structure).
Try
appExcel = New Excel Application
Do While Counter <=2
Select Case Counter
Case 1
eTemplate = "Some Excel File1"
RName = "New Version of File1"
Case 2
eTemplate = "Some Excel File2"
RName = "New Version of File2"
End select
wb = .Workbooks.Open(RTemplate)
With wb
.RefreshAll()
.SaveAs(RName)
.Close()
End With

'Increment Counter
Counter += 1

Loop
Catch ex As Exception
'Report Error
MessageBox.Show((ex.ToString))
Finally
If Not appExcel Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()

If Not wb Is Nothing Then
wb.Close(SaveChanges:=False)
Marshal.FinalReleaseComObject(fr.wb)
wb = Nothing
End If

appExcel.Quit()
Marshal.FinalReleaseComObject(fr.appExcel)
appExcel = Nothing
End If

End Try

I'm getting an error on the close statement in the Finally block. The
error
states that the object doesn't exist (disconnected). Which makes sense if
everything executes correctly because I have a close statement prior to
this
statement (in the loop code). But I'm not sure why I'm getting the error
because the if statement is checking for nothing.

My assumption is I need the early close statement so I can correctly loop
through and open then close the various excel files.

I also thought by checking in the Finally block if the wb object is
nothing
would be good in case anything went wrong and a wb instance was left open.
I
guess I don't understand why I'm getting this error when everything does
close properly because I would think the if statement in the finally block
would evaluate to nothing and not execute but instead I get an object
disconnected error.

Any help is greatly appreciated. I'm very new to VB.NET. Thanks

--
-ridawg

--
-ridawg