Closing Excel

Hi,

I'm trying to write data to an excel file and after saving that close excel immediatly.

This works, but there stay's an excel process in task manager. So when another part of the program wants to open the same file it will give me an error on opening the file. It works perfectly when I manually close the processes of excel.

Now I read on this forum that is has something to do with the garbage collector. I have tried al the examples given, but I stil have the problem. But I call the same function for the second time it will open another process and when that is finished it will close the old process, that was still open.

Maybe someone has had this problem before.


Public Function FillAndSaveExcel(ByVal beschik1t3(,) As Integer, ByVal beschik4t6(,) As Integer, _
ByVal excelArray1(,) As Integer, ByVal excelArray2(,) As Integer, _
ByVal excelArray3(,) As Integer, ByVal totaalarray(,) As Integer)

Dim saRet(145, 5) As Integer

Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet1 As Excel._Worksheet
Dim objSheet2 As Excel._Worksheet
Dim objSheet3 As Excel._Worksheet
Dim objSheet4 As Excel._Worksheet
Dim range1 As Excel.Range
Dim range2 As Excel.Range
Dim range3 As Excel.Range
Dim range4 As Excel.Range
Dim range5 As Excel.Range
Dim range6 As Excel.Range

' Create a new instance of Excel and start a new workbook.
objApp = New Excel.Application
objApp.Workbooks.Open(PgS.excelTemplate)
objBooks = objApp.Workbooks
objBook = objBooks.Open(PgS.excelTemplate)
objSheets = objBook.Worksheets
objSheet1 = objSheets(1)
objSheet2 = objSheets(2)
objSheet3 = objSheets(3)
objSheet4 = objSheets(4)

'Get the range where the starting cell has the address
'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range1 = objSheet1.Range("B2", Reflection.Missing.Value)
range2 = objSheet2.Range("B2", Reflection.Missing.Value)
range3 = objSheet3.Range("B2", Reflection.Missing.Value)
range4 = objSheet4.Range("F1", Reflection.Missing.Value)
range5 = objSheet4.Range("H2", Reflection.Missing.Value)
range6 = objSheet4.Range("K2", Reflection.Missing.Value)
range1 = range1.Resize(145, 4)
range2 = range2.Resize(145, 6)
range3 = range3.Resize(145, 6)
range4 = range4.Resize(4, 1)
range5 = range5.Resize(3, 1)
range6 = range6.Resize(3, 1)

range1.Value = excelArray1
range2.Value = excelArray2
range3.Value = excelArray3
range4.Value = totaalarray
range5.Value = beschik1t3
range6.Value = beschik4t6


objSheet1.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet3.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet4.Protect()

objApp.DisplayAlerts = False
objBook.SaveAs(PgS.excelBestandsnaam & "_" & DateTime.Today.ToShortDateString.ToString & ".xls")
objApp.DisplayAlerts = True

'Clean up and close
range1 = Nothing
range2 = Nothing
range3 = Nothing
range4 = Nothing
range5 = Nothing
range6 = Nothing
objSheet1 = Nothing
objSheet2 = Nothing
objSheet3 = Nothing
objSheet4 = Nothing
objSheets = Nothing
objBooks.Close()
objBooks = Nothing

'objApp.Workbooks.Close()
objApp.Quit()
objApp = Nothing

GC.Collect()

End Function

Two things that I could spot:

1. You are opening the workbook twice, since you have two Open statements here:objApp.Workbooks.Open(PgS.excelTemplate)
objBooks = objApp.Workbooks
objBook = objBooks.Open(PgS.excelTemplate)Remove the first of these 3 lines.

2. In the code you have posted, you are not setting objBook to Nothing.

Try changing these and see if that helps.

Hi Herilane,

Thanks for this advice.. it resolves the problem for the other functions with excel I have... but with this function theres still 1 excel process active after saved it...

I think this happens because I do a "save as" in this function. So there are 2 excel processes in this function. It works fine, but I can't figure out how to close the second process, because I not really opening it.


objBook = objBooks.Open(PgS.excelTemplate)

.........

objBook.SaveAs(PgS.excelBestandsnaam & "_" & _
DateTime.Today.ToShortDateString.ToString & ".xls")


Thanks for the help on this problem.

Hi MoreLikeIt,

The .SaveAs() Method is not your problem here, I promise... Best I can see, Herilane highlighted what needs fixing. Can you show us your code again, with those two corrections in-place?

Edit: Actually, also, what is your 'PgS' object, exactly? And I guess it's safe to assume that 'PgS.excelBestandsnaam' is returning a String? -- Mike]

Yes I can of course


Public Function FillAndSaveExcel(ByVal beschik1t3(,) As Integer, ByVal _
beschik4t6(,) As Integer, ByVal excelArray1(,) As Integer, _
ByVal excelArray2(,) As Integer, ByVal excelArray3(,) As Integer, _
ByVal totaalarray(,) As Integer)

Dim saRet(145, 5) As Integer

Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet1 As Excel._Worksheet
Dim objSheet2 As Excel._Worksheet
Dim objSheet3 As Excel._Worksheet
Dim objSheet4 As Excel._Worksheet
Dim range1 As Excel.Range
Dim range2 As Excel.Range
Dim range3 As Excel.Range
Dim range4 As Excel.Range
Dim range5 As Excel.Range
Dim range6 As Excel.Range

' Create a new instance of Excel and start a new workbook.
objApp = New Excel.Application
'objApp.Workbooks.Open(PgS.excelTemplate)
objBooks = objApp.Workbooks
objBook = objBooks.Open(PgS.excelTemplate)
objSheets = objBook.Worksheets
objSheet1 = objSheets(1)
objSheet2 = objSheets(2)
objSheet3 = objSheets(3)
objSheet4 = objSheets(4)

'Get the range where the starting cell has the address
'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range1 = objSheet1.Range("B2", Reflection.Missing.Value)
range2 = objSheet2.Range("B2", Reflection.Missing.Value)
range3 = objSheet3.Range("B2", Reflection.Missing.Value)
range4 = objSheet4.Range("F1", Reflection.Missing.Value)
range5 = objSheet4.Range("H2", Reflection.Missing.Value)
range6 = objSheet4.Range("K2", Reflection.Missing.Value)
range1 = range1.Resize(145, 4)
range2 = range2.Resize(145, 6)
range3 = range3.Resize(145, 6)
range4 = range4.Resize(4, 1)
range5 = range5.Resize(3, 1)
range6 = range6.Resize(3, 1)

range1.Value = excelArray1
range2.Value = excelArray2
range3.Value = excelArray3
range4.Value = totaalarray
range5.Value = beschik1t3
range6.Value = beschik4t6


objSheet1.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet3.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet4.Protect()

objApp.DisplayAlerts = False
objBook.SaveAs(PgS.excelBestandsnaam & "_" & DateTime.Today.ToShortDateString.ToString & ".xls")
objApp.DisplayAlerts = True

'Clean up and close
range1 = Nothing
range2 = Nothing
range3 = Nothing
range4 = Nothing
range5 = Nothing
range6 = Nothing
objSheet1 = Nothing
objSheet2 = Nothing
objSheet3 = Nothing
objSheet4 = Nothing
objSheets = Nothing

objBook = Nothing

objBooks.Close()
objBooks = Nothing
objApp.Quit()
objApp = Nothing

GC.Collect()

End Function


I really don't understand what the problem could be then. I'm closing
everthing I think.

Sorry I didn't answer the question...

Yes PgS.excelbestandsnaam is returning a string. This is the name that the must get with the date behind it.

The PgS object is just containing some values that are used in the program.

thanks for the help

[MoreLikeIt, would you mind adding Line-Breaks in your Post #5? Sorry, it's just hard to read the thread with so much left-right scrolling.]

Ok, a few more things:

(1) Add 'Option Explicit' to the top of your Document. This is the most important thing you can do. You should actually make this a Project-wide setting. Choose Alt|Project|Properties... Then choose 'Common Properties' and then 'Build'. Within that make sure that Option Explicit is set to 'On'. Then click <Apply> or <OK>.


(2) Adding 'Option Explicit' will flag two issues that you need to correct:

(a) 'objApp = New Excel.Application' will now have to be changed to: 'Dim objApp = New Excel.Application'. Get it? You must declare it first under Option Explicit and it really prevents one from making careless mistakes later. Trust me, this is a big help. Notice my signature, I "never leave home without it..." :)

(b) You need to add the line 'Dim objBook As Excel.Workbook'. You use the objBook, but it is never declared anywhere! Again, use 'Option Strict On' to pick up these kinds of things.


(3) Ok, the above is very important, but is not why you are hanging. I believe you are hanging because you are directly invoking the Excel Application's hidden _Global references. The hidden globals such as _Application, or _Worksheet, etc. need to be avoided at all costs. In your case, you have lines that look like the following:Dim objSheet4 As Excel._Worksheet I have a pretty good suspicion that if you change these lines to be: Dim objSheet4 As Excel.Worksheet you'll be in good shape. (That is, just remove the "_" character, and I think you'll be fine...)

Fingers crossed..!

Hi Mike,

Thanks for that option explicit on tip.. That makes some things very clear for me...

But You were right... This is not why I'm hanging... I made the changes you suggested, but there is not any difference in the behaviour of the program.

This morning I have tested the program and went step by step through the function with the Taskbar next to it...

The problem is not that excel is started twice, but that it isn't closed the first time. (It's never closed actually). When function starts for the second time it will start another excel process and this one isn't closed either. But when the function fineshes for second time, the first excel process will be closed... This does not happen after GC.Collect, but after the end function when it returns to the function which is calling the FillandSave function.

With the other functions that I have for excel this happens also... Only then the excel process also will be closed the first time. So I don't have an open excel process running...

I will place my code again maybe you can see what is wrong... I checked 3 times if every object is set to nothing at the end.

I shall also place a function that does work. I think it's a garbage collector problem, but I think it's strange that when I call a function to close or quit excel it fails to do that.

Sorry for the long poste by the way, maybe you can see something. My eyes are tired of staring at the code.

Many thanks


Public Function FillAndSaveExcel(ByVal beschik1t3(,) As Integer, _
ByVal beschik4t6(,) As Integer, ByVal excelArray1(,) As Integer, _
ByVal excelArray2(,) As Integer, ByVal excelArray3(,) As Integer, _
ByVal totaalarray(,) As Integer)

Dim objApp As New Excel.Application
Dim objBook As Excel.Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet1 As Excel.Worksheet
Dim objSheet2 As Excel.Worksheet
Dim objSheet3 As Excel.Worksheet
Dim objSheet4 As Excel.Worksheet
Dim range1 As Excel.Range
Dim range2 As Excel.Range
Dim range3 As Excel.Range
Dim range4 As Excel.Range
Dim range5 As Excel.Range
Dim range6 As Excel.Range

If File.Exists(PgS.excelBestandsnaam & "_" & _
DateTime.Today.ToShortDateString.ToString & ".xls") Then
' Create a new instance of Excel and start a new workbook.
objBooks = objApp.Workbooks
objBook = objBooks.Open(PgS.excelBestandsnaam & "_" & _
DateTime.Today.ToShortDateString.ToString & ".xls")
objSheets = objBook.Worksheets
objSheet1 = objSheets(1)
objSheet2 = objSheets(2)
objSheet3 = objSheets(3)
objSheet4 = objSheets(4)
objSheet4.Unprotect()
Else
' Create a new instance of Excel and start a new workbook.
objBooks = objApp.Workbooks
objBook = objBooks.Open(PgS.excelTemplate)
objSheets = objBook.Worksheets
objSheet1 = objSheets(1)
objSheet2 = objSheets(2)
objSheet3 = objSheets(3)
objSheet4 = objSheets(4)
End If
'Get the range where the starting cell has the address
'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range1 = objSheet1.Range("B2", Reflection.Missing.Value)
range2 = objSheet2.Range("B2", Reflection.Missing.Value)
range3 = objSheet3.Range("B2", Reflection.Missing.Value)
range4 = objSheet4.Range("F1", Reflection.Missing.Value)
range5 = objSheet4.Range("H2", Reflection.Missing.Value)
range6 = objSheet4.Range("K2", Reflection.Missing.Value)
range1 = range1.Resize(145, 4)
range2 = range2.Resize(145, 6)
range3 = range3.Resize(145, 6)
range4 = range4.Resize(4, 1)
range5 = range5.Resize(3, 1)
range6 = range6.Resize(3, 1)

range1.Value = excelArray1
range2.Value = excelArray2
range3.Value = excelArray3
range4.Value = totaalarray
range5.Value = beschik1t3
range6.Value = beschik4t6


objSheet1.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet2.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet3.Visible = Excel.XlSheetVisibility.xlSheetVeryHidden
objSheet4.Protect()

objApp.DisplayAlerts = False
objBook.SaveAs(PgS.excelBestandsnaam & "_" & _
DateTime.Today.ToShortDateString.ToString & ".xls")
objApp.DisplayAlerts = True

'Clean up and close
objBooks.Close()
objApp.Workbooks.Close()
objApp.Quit()
range1 = Nothing
range2 = Nothing
range3 = Nothing
range4 = Nothing
range5 = Nothing
range6 = Nothing
objSheet1 = Nothing
objSheet2 = Nothing
objSheet3 = Nothing
objSheet4 = Nothing
objSheets = Nothing
objBook = Nothing
objBooks = Nothing
objApp = Nothing

GC.Collect()

End Function


This is the function that works, It does not quit excel but it will be disposed when the function is finished.


Public Function FillExcelArrays(ByRef excelArray1(,) As Integer, _
ByRef excelArray2(,) As Integer, ByRef excelArray3(,) As Integer)
If File.Exists(PgS.excelBestandsnaam & "_" & _
DateTime.Today.ToShortDateString.ToString & ".xls") Then

Dim objApp As New Excel.Application
Dim objBook As Excel.Workbook
Dim objBooks As Excel.Workbooks
Dim objSheets As Excel.Sheets
Dim objSheet1 As Excel.Worksheet
Dim objSheet2 As Excel.Worksheet
Dim objSheet3 As Excel.Worksheet
Dim objSheet4 As Excel.Worksheet
Dim range1 As Excel.Range
Dim range2 As Excel.Range
Dim range3 As Excel.Range
Dim excel1(144, 3) As Object
Dim excel2(144, 5) As Object
Dim excel3(144, 5) As Object

' Create a new instance of Excel and start a new workbook.
objBooks = objApp.Workbooks
objBook = objBooks.Open(PgS.excelBestandsnaam & "_" & _
DateTime.Today.ToShortDateString.ToString & ".xls")
objSheets = objBook.Worksheets
objSheet1 = objSheets(1)
objSheet2 = objSheets(2)
objSheet3 = objSheets(3)
objSheet4 = objSheets(4)

'Get the range where the starting cell has the address
'm_sStartingCell and its dimensions are m_iNumRows x m_iNumCols.
range1 = objSheet1.Range("B2", "E146")
range2 = objSheet2.Range("B2", "G146")
range3 = objSheet3.Range("B2", "G146")

'Set the range value to the array.
excel1 = range1.Value
excel2 = range2.Value
excel3 = range3.Value


Dim i, j As Integer
For i = 0 To excel1.GetUpperBound(1) - 1
For j = 0 To excel1.GetUpperBound(0) - 1
excelArray1(j, i) = CInt(excel1(j + 1, i + 1))
Next
Next

For i = 0 To excel2.GetUpperBound(1) - 1
For j = 0 To excel2.GetUpperBound(0) - 1
excelArray2(j, i) = CInt(excel2(j + 1, i + 1))
Next
Next

For i = 0 To excel3.GetUpperBound(0) - 1
For j = 0 To excel3.GetUpperBound(1) - 1
excelArray3(i, j) = CInt(excel3(i + 1, j + 1))
Next
Next

'Clean up
objBooks.Close()
objApp.Quit()
range1 = Nothing
range2 = Nothing
range3 = Nothing
objSheet1 = Nothing
objSheet2 = Nothing
objSheet3 = Nothing
objSheet4 = Nothing
objSheets = Nothing
objBook = Nothing
objBooks = Nothing
objApp = Nothing

GC.Collect()
End If

End Function

The problem is not that excel is started twice, but that it isn't closed the first time. (It's never closed actually). Yes, I know the behavior exactly. :( This is tougher to solve in .Net than it is under VB6 or VBA. But I'm not giving up yet!

Let's try one more change. Let's make your "Cleanup and Close" section look like this: 'Clean up and close
range1 = Nothing
range2 = Nothing
range3 = Nothing
range4 = Nothing
range5 = Nothing
range6 = Nothing
objSheet1 = Nothing
objSheet2 = Nothing
objSheet3 = Nothing
objSheet4 = Nothing
objSheets = Nothing

objBook.Close()
objBook = Nothing
objBooks = Nothing

objApp.Quit()
objApp = Nothing

GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
Sorry that this has been so hard. Give this new version a try and let us know... Fingers crossed!

-- Mike

Hello ppl,

My friend found the solution, he thinks.

objExcel.Workbooks(1).Close (0)
objExcel.Quit
Set objExcel = Nothing

Rgds

Hi Guys,

Many many thanks for all the help you give me... but the problem is still there. I also tried you're trick aLwEiS, but it didn't help either. The behaviour of the program doesn't change a bit.

Now I did discover that the process that stays running will be closed when the program is closed. That means that the dispose function of .Net itself is killing the process when I quit the program. This makes the problem a lot less dangerous for the memory.

I don't know what I can do now, so I let it for this for a while. Maybe I can think of something next week.

When I find the solution I will let you all know of course.

see you around here..

I originally posted this solution in another forum. This solution was written to close a particular Excel instance and leave the rest undisturbed.

FYI... I am running Windows 2000 Professional, Visual Studio 2003, Office 2000 Professional. After trying for a day or two to get the instance to close I just wrote around the problem using the following procedure

1. Loop the through the open processes (before I create my instance of Excel) and add all of the process ids together (they are integers) that are associated with running Excel applications (on my machine) . I usualy have more than one open.
2. Create the instance of Excel
3. Loop through the open processes again and total process ids for open excel applications
4. subtract (1) from (3) and I now have the process id for the Excel instance that I am intereseted in.
5. When I'm done using the instance (and after I have released all objects) I use the kill method to terminate the process.

Works great!

Code segements follow:

'Add up the IDs before creating
For Each proc In Process.GetProcesses
If proc.ProcessName.ToLower = "excel" Then
t1procID = t1procID + proc.Id
End If
Next

'Create the instance
objXLApp = CType(CreateObject("Excel.Application"), Excel.Application)

'add the existing ids after excel creation
For Each proc In Process.GetProcesses
If proc.ProcessName.ToLower = "excel" Then
t2procID = t2procID + proc.Id
End If
Next
'Subtract the two and the result is the proc.id of the Excel instance that we will close when we're through
intprocID = t2procID - t1procID

'DO THINGS and then release objects

'Kill it
For Each proc In Process.GetProcesses
If proc.Id = intprocID Then
proc.Kill()
End If
Next

This is not pretty but it works.

Yes, if willing to be brutal about it, Process.Kill() can definately do the trick. It should be the last resort, and one should be as careful as possible when using it.

But your approach is the right idea... I think we can simplify your code a bit, though, to something like this:For Each proc As Process In Process.GetProcesses("excel")
Try
proc.Kill()
Catch
' Ignore errors
End Try
Next proc However, this closes all Excel applications, including any that the User might have open running separately from your process, so closing them all can really be a big problem. It's pretty similar to a crash; the User would lose all his/her work. :(

One could call Process.CloseMainWindow() instead of Process.Kill(). This simulates hitting the Big Red [X] button and so allows warnings such as "do you want to save all your work". Another possibilty is to look at the Process.Responding property. If = True, then Process.Kill() should not be necessary (this is probably another Excel instance that the User is currently using) and so don't! So maybe our loop should look like this:For Each proc As Process In Process.GetProcessesByName("excel")
If Not proc.Responding() Then
Try
proc.Kill()
Catch
' Ignore errors
End Try
End If
Next proc The best idea to identify which, specific Excel instance you want is via the Caption, which can be picked up via the Process.MainWindowTitle property. If the Excel instance is not Visible, then it's a pretty good bet that the User is not using it, and the .MainWindowTitle will return an empty String ("").

So the best overall plan might be to close all non-responding, non-visible Excel processes:For Each proc As Process In Process.GetProcessesByName("excel")
If Not proc.Responding() AndAlso Len(proc.MainWindowTitle) = 0 Then
Try
proc.Kill()
Catch
' Ignore errors
End Try
End If
Next proc Make sense?

But the point is to be as careful as possible when using this... And also to use it only if all other traditional means of closing the Application have failed.

-- Mike

Actually, my code closes only the instance that I created. This is by design. I have a number of users who have multple instances of Excel open and wouldn't appreciate it if I closed some of there workbooks in mid update. And I only wrote this after all other methods were exhausted.

When I have a problem like this I like to grab it by the short hairs :)

But, I ALWAYS use this as a last resort.

Ah! Ok, very nice! :)

I got a bit lost in all the addition and subtraction going on and missed the point. And it's a big point! That's pretty darn clever.

Ok, I'm going to paraphrase your code here, if that's ok? Dim priorSum As Integer = 0
Dim newSum As Integer = 0
Dim xlProcID As Integer = 0

' (1) Save the Sum of the Existing "Excel" Processes:
For Each proc As Process In Process.GetProcessesByName("excel")
priorSum += proc.Id
Next proc

' (2) Create the New "Excel" Process:
Dim xlApp As New Excel.Application

' (3) Determine the 'newSum' of the Existing "Excel" Processs:
For Each proc As Process In Process.GetProcessesByName("excel")
newSum += proc.Id
Next proc

' (4) Subtract the difference to detarmine the 'xlProcID'
xlProcID = newSum - priorSum
From this point onward one can utilize the 'xlApp' reference to manipulate Excel, and when done, if Excel fails to close via normal means it could be closed by making use of the 'xlProcID':Try
Dim proc As Process = Process.GetProcessById(xlProcID)
proc.Kill()
Catch
' On error do nothing.
End Try This is a very nice approach, well done. :)

Thanks BellShape!

And just for kicks, here's a HashTable implimentation of the same:Dim xlProcID As Integer
Dim hash As New Hashtable

For Each proc As Process In Process.GetProcessesByName("excel")
hash.Add(proc.Id.ToString, proc.Id)
Next proc

Dim xlApp As New Excel.Application

For Each proc As Process In Process.GetProcessesByName("excel")
If Not hash.ContainsKey(proc.Id.ToString) Then
xlProcID = proc.Id
Exit For
End If
Next proc The HashTable has the advantage of picking up more than one Excel instance if by some unbelievable fluke occurrence that another Excel.Application is created while your code is creating it's instance... Ok, well, I have to admit that that would be all-but-impossible, even if theoretically possible. :p

Anyway, it's worth learning about the HashTable anyway, it's a nice little class.

-- Mike

Thanks for the feedback on my humble solution. I am always glad to pick up a little bit more knowledge. I hope the original poster finds this discussion useful. I know I have.

Me too. :)

Hi,

This is of course very usefull, also the last resort to get to I think.

bellshapedhead and Mike_R... many thanks for this solution.

greetingzz

MLI

We may all need this more often than we might like to admit... The Excel.Application object can hang if the sub-objects like Range and Worksheet are not disposed before Workbook and then the Excel.Application is last... The problem is that .Net uses non-deterministic finalization, so you can't really control this.

I find that my Excel.Application releases cleanly if done "right", but there is absolutely no guarantee of that... This would seem to be the only way that is absolutely sure. Last resort, yes, but I now use it routinely after calling GC.Collect() and GC.WaitForPendingFinalizers(). With error handling in place, it absolutely cannot hurt... and you just never know if/when it will actually be needed.

Thanks again Bell-Shaped! :)

Hi,

Have you tried
objApp.Workbooks.Add(PgS.excelTemplate) instead of objApp.Workbooks.Open(PgS.excelTemplate)? I had a similar problem and I could not figure out why the Excel Object would not close. I Stumbled upon Mike_R's excellent tutorial on Automating Office Programs with VB.Net / COM Interop (http://www.xtremevbtalk.com/showthread.php?t=160433) but when I replaced the
Dim oWB As Excel.Workbook = oApp.Workbooks.Add with
Dim oWB As Excel.Workbook = oApp.Workbooks.Open("E:\Excel Test database\janv2005.xls"), the Excel object is not released, but changing the code to
Dim oWB As Excel.Workbook = oApp.Workbooks.Add("E:\Excel Test database\janv2005.xls") works fine.

Not sure if this will work for you, but it's worth a try.

Yu-Lin

Hi Yu-Lin,

Welcome to the Forum. (And thanks for the compliments :))

We'll have to keep your advice in mind regarding Workbooks.Add() vs. Workbooks.Open(). I don't really see why .Open() would be a problem unless one forgot to call .Close() before attempting Application.Quit(), but every bit of information helps...

Also one has to keep in mind, of course, that using Workbooks.Add(Template:=""E:\Excel Test database\janv2005.xls") is not quite the same as Workbooks.Open(FileName:="E:\Excel Test database\janv2005.xls"), as using .Open() opens the Workbook with a reservation, while using .Add() is actually opening the Workbook as a copy of the original.

Also, the technique described by BellShapedHead in this thread has now been included in that tutorial itself, within Post #3 Closing your App -- Extreme Measures (http://www.xtremevbtalk.com/showthread.php?p=956122#post956122).

:),
Mike