Problem declaring Excel workbook in VS/VB 2010

[EDN Admin]

<br/>
Attention especially Nico Boey
My question centers upon the two approaches, (1) and (2), shown below, to declaring Excel workbooks in .Net.<br/>
The two approaches interact differently with the lines "wkbk10 = app.wkbk10.open()" and later with<br/>
Range references to this workbook,like "rng1Stocks = CType(wkbk10.Sheets(1), Excel.Worksheet).Range("H5:H30")".<br/>

I think the line wkbk10 = app.wkbk10.open() should be placed into a Try-Catch exception handler.<br/>
If I do this,however, using Approach(1) (Dim wkbk10 As Excel.Workbook), the variable has Block scope and therefore when I get to
"rng1Stocks = CType(wkbk10.Sheets(1), Excel.Worksheet).Range("H5:H30")",VS issues a Warning that the variable, wkbk10, is used before
being declared. If I remove the wkbk10 = app.wkbk1.open() from the Try-Catch exception handler there is no problem with the
declaration.
If, on the other hand I declare the variables as in Approach(2) and keep the wkbk10 = app.wkbk10.open() in the Try-Catch,<br/>
there is no problem with variable declaration BUT,the code generates a new workbook called "workbook1"which has nothing in it but
must be dealt with somehow.
(The same problems exist in the case of the other workbook, wkbk40)<br/>
<br/>
I must be not understanding something here about how to declare COM objects in the .Net enironment
Help with this will be greatly appreciated.
Private Sub btnTryCode_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnTryCode.Click
<br/>
What it does:<br/>
Step 1.Opens wkbk10<br/>
..code, code, code to copy, paste, copy
Step 2.Opens wkbk40<br/>
..code, code, code to paste from wkbk1
Step 3. Closes wkbk10 first,then<br/>
Closes wkbk40
Step 4. Quits app<br/>
Step 5 Runs GC() which ends the Excel Process
Illustration of my problem: Two approaches to declaring the workbooks<br/>
Approach (1)<br/>
Dim app As New Excel.Application<br/>
Dim wkbk10 As Excel.Workbook
<br/>
Dim wkbk40 As Excel.Workbook
<br/>
<br/>
Approach (2)<br/>
Dim app As New Excel.Application<br/>
Dim wkbk10 As Excel.Workbook = app.Workbooks.Add()
<br/>
Dim wkbk40 As Excel.Workbook = app.Workbooks.Add()

Dim rng1Stocks As Excel.Range = Nothing <br/>
Dim rng2Stocks As Excel.Range = Nothing
<br/>
Dim rng3Stocks As Excel.Range = Nothing
<br/>
Dim rng4Stocks As Excel.Range = Nothing
<br/>
<br/>
Dim rngBlankCellOne As Excel.Range = Nothing
<br/>
Dim rngBlankCellTwo As Excel.Range = Nothing
app.Visible = True<br/>
app.CutCopyMode = True<br/>
<br/>
Try<br/>
<br/>
wkbk10 = app.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "VB Workbook One.xlsx")<br/>
Catch<br/>
MessageBox.Show("wrong")<br/>
End Try
<br/>
Approach (1)<br/>
rng1Stocks = CType(wkbk10.Sheets(1), Excel.Worksheet).Range("H5:H30")<br/>
rng1Stocks.Copy()
rng2Stocks = CType(wkbk10.Sheets(1), Excel.Worksheet).Range("C5:C30")<br/>
rng2Stocks.PasteSpecial(Excel.XlPasteType.xlPasteValues)
<br/>
app.CutCopyMode = False
<br/>
rng3Stocks = CType(wkbk10.Sheets(1), Excel.Worksheet).Range("B530")<br/>
rng3Stocks.Copy()<br/>
rngBlankCellOne = CType(wkbk10.Sheets(1), Excel.Worksheet).Range("B2:B2")<br/>
rngBlankCellOne.Select()
<br/>
<br/>
Try<br/>
wkbk40 = app.Workbooks.Open(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "VB Workbook Two.xlsx")<br/>
Catch<br/>
End Try
rng4Stocks = CType(wkbk40.Sheets(1), Excel.Worksheet).Range("B730")<br/>
rng4Stocks.PasteSpecial(Excel.XlPasteType.xlPasteValues)
app.CutCopyMode = False<br/>
rngBlankCellTwo = CType(wkbk40.Sheets(1), Excel.Worksheet).Range("B2:B2")<br/>
rngBlankCellTwo.Select()<br/>
<br/>
wkbk10.Close(SaveChanges:=True)<br/>
wkbk40.Close(SaveChanges:=True)<br/>
<br/>
app.Quit()<br/>
<br/>
rng1Stocks = Nothing<br/>
rng2Stocks = Nothing<br/>
rngBlankCellOne = Nothing
rng3Stocks = Nothing<br/>
rng4Stocks = Nothing<br/>
rngBlankCellTwo = Nothing
GC.Collect()<br/>
GC.WaitForPendingFinalizers()<br/>
GC.Collect()<br/>
GC.WaitForPendingFinalizers()
FinalReleaseComObject(wkbk1)<br/>
FinalReleaseComObject(wkbk4)<br/>
FinalReleaseComObject(app)
End Sub<br/>
End Class

View the full article