-
Using Excel with VB.net
For all of you that have been having trouble getting VB.net to work right with the Excel interops it can be a bit of a bear so I will post the code I got made to get it to work right. It is a simple example but it may help som people
Code:
Imports Excel = Microsoft.Office.Interop.Excel...
Private Sub getInfo()
'declares the variables
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add(System.Reflection.Missing.Value)
Dim partySheet As Excel.Worksheet = Nothing
'try to open the workbook and a worksheet
Try
excelBook = excelApp.Workbooks.Open("C:\Program Files\DISClient\Templates\TemplateMerge.xls")
partySheet = excelBook.Worksheets("MergeData")
Catch ex As Exception
MsgBox(ex)
Finally
'MAKE SURE TO KILL ALL INSTANCES BEFORE QUITING! if you fail to do this
'The service (excel.exe) will continue to run
NAR(partySheet)
excelBook.Close(False)
NAR(excelBook)
excelApp.Workbooks.Close()
NAR(excelApp.Workbooks)
'quit and dispose app
excelApp.Quit()
NAR(excelApp)
'VERY IMPORTANT
GC.Collect()
End Try
End Sub
Private Sub NAR(ByVal o As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(o)
Catch ex As Exception
Finally
o = Nothing
End Try
End Sub
Thanks to the forums for helping me to get it to work
-
excellent ..i was looking for some code like this a while back
cheers m8
-
Please help
Please help me release excel from memory in this simple example.
objExcel = New Excel.Application
bExcelOpen = True
objExcel.DisplayAlerts = False
objExcel.AlertBeforeOverwriting = False
objWorkbook = objExcel.Workbooks.Add(My.Application.Info.DirectoryPath & "\Templates\TestTemplate.xls")
System.GC.Collect()
System.GC.WaitForPendingFinalizers()
objWorkbook.Close()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel.Workbooks)
objWorkbook = Nothing
objExcel.Quit()
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(objExcel)
objExcel = Nothing
-
Please help
The program I submitted releases excel if I close my application.
My problem is that the application is a 24x7 and may not close in months.
Any help/suggestion will be highly appreciable.
Thanks
-
Gurpreet, your code works fine for me. Created a simple form with your code run from a button. Code runs, excel opens, code completes. There is no instance of excel running. Could you be instantiating it somewhere else in your application?
...joe
-
Great Post!
Brilliant and simplistic! Every other coder on the planet wants to make it more complicated. Thanks!
-
Unless you really know what you are doing, do not call the garbage collector (the lines of code calling System.GC) in your applications.
The garbage collector is a complex thing and calling it can have effects you did not want.
First of all, it is used to control managed objects (.NET objects). Excel being an ActiveX object, it is not cleared from memory when you call GC.Collect. In fact, doing so can have a detrimental effect. Because GC does not know the references held by or to Excel, in calling GC on the interop you risk closing a copy of Excel that is used by other applications or manually by the user.
It's been my experience that the garbage collector is global in the .NET environment, and does not work per application. In calling it, you temporarily freeze all the running .NET applications while the GC clean up the memory. And temporarily can be quite a long time as far as the user is concerned.
Jacques Bourgeois
JBFI
http://www3.sympatico.ca/jbfi/homeus.htm
-
Excel not closing
Thought I would post an addition to whoever was trying to get this to work as it was very frustrating and I spent hours on these forums and msdn researching.
This is a really basic example but the keys to me were to ensure you had the loop for releasing the com objects and also that you used Excel.Workbooks in addition to Excel.Workbook.
I got this to work without calling the Garbage collector, as the jury is still out on whether or not that is recommended. Best of luck!
Check out this msdn link as well:
http://support.microsoft.com/kb/317109
Code:
Dim xlApp As Excel.Application 'Excel Template Application
Dim xlWbs As Excel.Workbooks 'Excel Application workbooks
Dim wbTemplate As Excel.Workbook 'Template Workbook
Dim wsTemplateSheet As Excel.Worksheet 'Template Sheet
Dim wsRange As Excel.Range
public sub XLMain()
dim strReportPath as string = "C:\Some Directory\Excel is in\and junk.xls"
xlApp = New Excel.Application
xlWbs = xlApp.Workbooks
wbTemplate = xlWbs.Open(strReportPath, , True)
'Now Excel is open, do stuff
'My code used references to template and named ranges
'Close Excel:
closeExcel 'Call Close Excel Sub
End Sub
'Functions/Subs
Public Sub RCO(ByVal obj As Object)
Try
Dim intRel As Integer = 0
Do
intRel = System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
Loop While intRel > 0
msgbox("Final Released obj # " & intRel)
Catch ex As Exception
msgbox("Error releasing object" & ex.ToString)
obj = Nothing
End Try
End Sub
Private Sub CloseExcel()
'Sub to ensure Excel has been properly closed out
'Close out Range
Try
RCO(wsRange)
wsRange = Nothing
Catch
End Try
'Close out Worksheet
Try
RCO(wsTemplateSheet)
wsTemplateSheet = Nothing
Catch
End Try
'Close out Workbook
Try
'wbTemplate.Close(False)
RCO(wbTemplate)
wbTemplate = Nothing
Catch
End Try
'Close out Workbooks
Try
xlWbs.Close()
RCO(xlWbs)
xlWbs = Nothing
Catch
End Try
'Close out Excel App
Try
xlApp.Quit()
RCO(xlApp)
xlApp = Nothing
Catch
End Try
'Could call GC.Collect at this point if needed....
End Sub
-
The opposite scenario
Hi kbord_jockey,
Thanks for yr post, it really does the job when the object is to be released after having closed it from the application.
What I actually have is a slightly different scenario:
I pass some data to an Excel workbook and I need to keep Excel open as the user operates the exported data at his own.
The application that sends data to Excel may remain running 24/7 but the user often quits Excel upon data are handled as saved.
Still with this scenario Excel would keep staying in memory even after beeing closed by the user unless the .net aplication is closed.
To have it coded:
Code:
Private Sub XlSendData()
Dim locExcel As Excel.Application = New Excel.Application
Dim locWorkbook As Excel.Workbook = locExcel.Workbooks.Add
Dim locWorksheet As Excel.Worksheet = locWorkbook.Worksheets(1)
' Start sending data to Excel
'
' Finish sending data to Excel
' Set Excel visible so that the user can work with it
locExcel.Visible = True
' At this point Excel must be running but the sub is to release the references
Try
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorksheet)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locWorkbook)
System.Runtime.InteropServices.Marshal.FinalReleaseComObject(locExcel)
Catch ex As Exception
locWorksheet = Nothing
locWorkbook = Nothing
locExcel = Nothing
End Try
' The user is still working with Excel
' This application will keep running for many more hours / days
' When the user closes Excel it remains in memory until this application is closed
End Sub
Have you got an idea on how to let Excel release the memory in this scenario?
Rgrds
Viaceslav
 Originally Posted by kbord_jockey
Thought I would post an addition to whoever was trying to get this to work as it was very frustrating and I spent hours on these forums and msdn researching.
This is a really basic example but the keys to me were to ensure you had the loop for releasing the com objects and also that you used Excel.Workbooks in addition to Excel.Workbook.
I got this to work without calling the Garbage collector, as the jury is still out on whether or not that is recommended. Best of luck!
Check out this msdn link as well:
http://support.microsoft.com/kb/317109
-
Excel not closing - process kill
Hey dymarski,
So basically you are letting your user have the version of Excel you opened running and you want to make sure it gets closed out properly after they are done? My scenario was a bit different as I knew when Excel needed to be closed out. One comment on your code would be that I recommend you use the Excel Workbooks object as well as the Excel Workbook, and are you looping through to release all the objects? Did you add GC.collect where recommended as well?
That being said I did have some trouble with one application making sure that Excel was closed out properly so I added in code to kill the Excel process if the process was still hanging around after I closed everything out properly. Doing this also helped showed bugs in my code I had missed as when I killed the Excel process (before releasing everything properly) I saw Excel errors in the Office Event log.
HTML Code:
intXLPid = stored Excel PID
pXL = Excel Process
If IsNothing(Process.GetProcessById(intXLPid)) = False Then
pXL.Kill()
End If
You can add more to your code like checking to see if the process has exited and if the Process Main Window Title is = "" then you know the Excel process has its visibility set to false or the user has closed Excel.
Getting the Excel process ID is somewhat tricky if you are starting excel and its visibility is set to false (you cant tell which version of Excel is yours by looking at title if multiple versions are running) but I wrote a function that I call before starting excel and immediately after which I can use to determine which Excel process is mine:
Code:
Private Sub getPIDs(ByRef strPID() As String)
intXLPid = 0
Dim pXL As Process = Nothing
Try
'Find my process ID
Dim processes As Process() = Process.GetProcessesByName("Excel")
If processes.GetUpperBound(0) >= 0 Then
ReDim strPID(processes.GetUpperBound(0))
For i As Integer = 0 To processes.GetUpperBound(0)
'
strPID(i) = processes(i).Id
Next
Else
ReDim strPID(0)
strPID(0) = ""
End If
Catch
ReDim strPID(0)
strPID(0) = ""
End Try
End Sub
Anyway I just wanted to add this as I should have probably added in original post.
In your situation I would recommend either having your program monitor for the Excel program to close and then shut it down properly or have Excel notify it asynchronously (MSMQ or other method) when the user closes. Another thought would be to automate Excel, close and save, and then just open for the user it with a shell type command without any of the COM interfacing.
Hope this helps...
-
writing to an existing Excel file in VB.net
I am trying to do something similar... but am having a heck of a time getting it to work properly.
Steps:
1) Collect data in another form and pass it to variables.
2) Pass variables to a procedure that checks to see if the excel file exists. If no, create file. If yes, or new file created, then write to the file.
3) Periodically, I want to send new data to open rows in the excel file.
The issue is updating the Excel file. I want to be able to call a function from another form that writes to the open file.
I tried passing excel.application, excel.workbook, and excel.worksheet to the functions below, but I was throwing exceptions... and have probably been staring at this for too long 
Any help is greatly appreciated.
Here is my code:
Code:
Imports System
Imports System.Reflection ' For Missing.Value and BindingFlags
Imports System.Runtime.InteropServices ' For COMException
Imports Excel
Imports System.IO
Public Class Form1
Dim strPathname As String = "c:\"
Dim strFileName As String = "test1.xls"
Dim blnFileOpen As String = False
Dim blnFileExists As String = False
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim intGroupNum As Integer = 1
Dim intGroupNumMax As Integer = 10
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Try
'Check to see if File exists
If IO.File.Exists(strPathname & strFileName) = True Then
blnFileExists = True
'Check to see if existing file is open
blnFileOpen = fnFileOpen(strPathname & strFileName)
'File exists, but is not open. Open file.
If blnFileOpen = False Then
fnOpenExcelFile() 'Open Excel File
End If
fnWriteToExcel() 'Write to excel
'Else file does not exist
Else
blnFileExists = False
fnNewExcelFile() 'Create and format Excel workbook
fnWriteToExcel() 'Write to Excel
End If
Catch GenErr As Exception 'General Error
MsgBox(GenErr.ToString) 'Display error
Finally
releaseObject(xlApp) 'Release Excel Application
releaseObject(xlWorkBook) 'Release Excel workbook
releaseObject(xlWorkSheet) 'Release Excel Worksheet
End Try
End Sub
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
Private Function fnOpenExcelFile() As Boolean
'Function opens excel file
Try
xlApp = New Application() 'Create new Excel application
xlWorkBook = xlApp.Workbooks.Open(strPathname & strFileName) 'Open file
xlWorkBook.Application.Visible = True 'Make visible
xlWorkBook.Activate() '
Catch GenErr As Exception 'General Error
MsgBox(GenErr.ToString) 'Display error
End Try
End Function
Private Function fnNewExcelFile() As Boolean
Dim i As Integer = 1
xlApp = New Application() 'Create new Excel application
xlWorkbook = xlApp.Workbooks.Add() 'Add new workbook
xlWorkbook.SaveAs(strPathName & strFileName) 'Save new workbook
'xlWorkBook = xlApp.Workbooks.Add("c:\test.xls")
xlWorkbook = xlApp.Workbooks.Open(strPathName & strFileName) 'Open file
xlWorkbook.Application.Visible = True
xlWorkbook.Activate()
'Format Excel Workbook
'Delete two of the default worksheets
For i = 1 To 2
xlWorkbook.Sheets(1).Delete()
Next
i = 1 'Reset counter
'Rename existing worksheet as rightmost (last) group
xlWorksheet = xlWorkbook.Sheets(1)
xlWorksheet.Name = "Group " & intGroupNumMax
xlWorksheet.Cells(1, 1) = "Worksheet" & intGroupNumMax
'xlWorkSheet.Name = "Group " & i
'xlWorkSheet.Cells(1, 1) = "Worksheet" & i
'Create enough worksheets to cover max group number
Try
For i = 1 To (intGroupNumMax - 1)
xlWorksheet = xlWorkbook.Worksheets.Add
Next
'Name tabs
For i = intGroupNumMax To 1 Step -1
xlWorksheet = xlWorkbook.Sheets(i)
xlWorksheet.Name = "Group " & i
xlWorksheet.Cells(1, 1) = "Worksheet" & i
Next
xlWorkbook.SaveAs(strPathName & strFileName) 'Save changes to workbook
Catch GenErr As Exception 'General Error
MsgBox("The function fnNewExcelFile() failed." & vbCrLf & vbCrLf & _
"The 'write to Excel Checkbox' has been selected." _
& vbCrLf & "This requires Excel to be installed on this computer" _
& vbCrLf & "Make sure Excel is installed or deselect the checkbox") 'Display error
End Try
End Function
Private Function fnFileOpen(ByVal FilePath As String) As Boolean
'This function checks to see if the given file is open
Dim fs As System.IO.FileStream
Dim blnFileOpen As Boolean = True
Try
' If Open() succeeds, then we know the file is not currently in use.
fs = System.IO.File.Open(FilePath, FileMode.Open, FileAccess.Read, FileShare.None)
blnFileOpen = False
fs.Close()
Catch ex As Exception
' any "file already in use" code here
End Try
fnFileOpen = blnFileOpen
End Function
Private Function fnWriteToExcel()
'Function writes to specified Excel worksheet
Dim i As Integer = 1
Try
'Look through open workbooks for matching file
For j As Integer = 1 To xlApp.Workbooks.Count '
If xlApp.Workbooks(j).Name = strFileName Then '
xlWorkBook = xlApp.Workbooks(j)
End If
Next
'Write to worksheet
For i = intGroupNumMax To 1 Step -1
xlWorkSheet = xlWorkBook.Sheets(i)
xlWorkSheet.Cells(2, 1) = "Writing new stuff Here " & i
Next
Catch ex As Exception
MsgBox(ex.ToString)
MsgBox("The function fnWriteToExcel failed." & vbCrLf & vbCrLf & "Check to make sure that Excel Workbook " _
& strFileName & " is available and open.")
End Try
End Function
End Class
Last edited by Hack; 05-19-2010 at 07:19 AM.
Reason: Added Code Tags
-
infundibulum1
I tried passing excel.application, excel.workbook, and excel.worksheet to the functions below, but I was throwing exceptions
What type of exception, what message, and if a COMException, what was the error code?
On wich line do you have a problem?
I see a few problems that can arise from the following
Code:
Finally
releaseObject(xlApp) 'Release Excel Application
releaseObject(xlWorkBook) 'Release Excel workbook
releaseObject(xlWorkSheet) 'Release Excel Worksheet
End Try
You are releasing objects in the improper order. Always release the content before the container. They should be in the reverse order.
If there was an error, one of the objects might not have been created. I am not sure that releaseObject works correctly with an object set to Nothing.
As for the general discussion in this thread, a lot of it is useless considerations.
I have been working with Excel and Word extensively since I started working in .NET, in the first Beta of Visual Studio 2002, and I never had to use GC.Collect or ReleaseComObject.
Most people do not understand what the GC does, and it shows in the discussion.
Calling Collect many times in a row as a few pieces of code in this discussion do is an even worst idea. You could release all your objects, and call Collect at the end, that would be more efficient.
But anyway, calling GC.Collect is usually a bad idea. The role of the garbage collector is to optimize memory usage and reclamation. When you call Collect, you override that optimisation and slow down the system for nothing.
As for ReleaseComObject, read the documentation: "You should use this method to free the underlying COM object that holds references to resources." Resources mean files, timers, database connections and a few others that are not under the control of managed code.
If you are careful to always close the Excel or Word file before getting rid of your objects, you will have released the resources, and ReleaseComObject is useless. Simply call Quit on the Application, you will never have memory leaks. The application will have released the files and the objects associated with it in unmanaged memory, your object variables will be set to Nothing automatically at the end of the method, the garbage collector will reuse available memory as necessary.
Jacques Bourgeois
JBFI
http://www3.sympatico.ca/jbfi/homeus.htm
-
Thank you for the fast response, JBourgeois. Your patience and insight are both greatly appreciated. I am new to .NET and this project has been causing some headaches.
Perhaps I should take a step back and describe what I am trying to do and the questions I have for setting this up.
I want to create a set of public functions that allow me to check if an excel file exists, open it, if it does, format it, update it, and finally clean up any loose ends when the overall process is finished.
The code I sent in earlier works in a single form that I built. This is a multi-form project, and my first .NET program.. so, I wanted to build each part, test it, and then integrate them together at the end.
When I run the project as a Class (Form 1 in this case) and dimension all of the variables (strFilename, strPathname, xlApp, etc..) in the class, all of my functions operate as expected.. except for the release of the resources which you mentioned. I want to do that correctly, but I also need to figure this out.
Since I want to take this form class and be able to call it's functions from outside the class, I commented all of the variables out, built another form (Form 2) with a button, and added the function calls to button_click actions of Form 2.
In one example I built, I converted the functions in form 1 to Public, but when I try to call them in form 2, each of the function names is "undeclared" and I get the squiggly blue underline.
In another, I tried to pass the application name (xlApp above), workbook name (xlWorkbook above), and worksheet name (xlWorksheet above) to the functions. Each time the function returned to the procedure that called it, the values for each became Nothing. Even though I was only passing ByVal and not ByRef.
I feel like I know (or have found) enough to be dangerous.. but, only to myself 
My gut tells me I am making this more difficult than it really is.
Thanks again.
-
Lot of stuff. Hope I can clarify a few things.
------
A form is a class. When you declare a Public function in a class (thus in a form), it becomes a method of that class. You cannot call it directly.
In order to be able to call a function in Form2 from Form1, Form1 needs to have a reference to Form2.
You would have code like this one in Form1:
Dim frm As New Form2
frm.YourPublicFunctionInForm2()
------
Looks as if you want to call the code from a button in Form2 from Form1? Not a good thing.
If to use the same function in many different forms. In this case, the best solution is often to put the function in a Module. A function stored in a module is available everywhere in the application, without having to create an object (variable) to call it.
So by putting the function in a module, Form1 can call it, Form2.button_click can call it, any form in your application or dll can call it.
------
[QUOTE]I tried to pass the application name (xlApp above), workbook name (xlWorkbook above), and worksheet name (xlWorksheet above) to the functions.[QUOTE]These variables are not the names, they are the application, the workbook and the worksheet itself.
An application does not have a name, it is a copy of Excel.
For the Workbook and the Worksheet, if you want to pass the name, you should pass xlWorkbook.Name, xlSheet.Name.
------
A very very very common misconception about ByVal vs ByRef.
They are significant only for value objects, small objects made from the basic types (Date, Integer, String, etc.) or structures (Size, Point, the type of objects that have an icon showing 3 blocks glued together in the lists provided by Intellisense in the code when you are declaring a variable).
Any object built on a class (the type of objects that have an icon showing 3 blocks linked with lines) is a reference object.
Explained simply, a variable that holds a value object holds the value itself. A variable that holds a reference object is not the object, it is a pointer to the object, the address of its location in memory.
When you pass a value object ByVal, you pass a copy of the value.
When you pass a reference object ByVal, you pass a copy of the pointer, a copy if the address where the object resides in memory. When the called method works with the parameter, it is working at the same address in memory, thus working with the original object, not a copy of the object.
Put simply, ByVal and ByRef makes not difference for objects that are built on a class.
Since Excel.Application, Excel.Workbook and Excel.Worksheet are all classes, it makes not difference if you pass those ByVal or ByRef. Although underneath the system does not pass the paramater the same way, the result in your code is the same as if you had passed the object ByRef.
This is quite normal, because normally, if you passe an Excel sheet to a procedure, it is because you want to work with that Excel sheet.
So if you send a Worksheet or a Workbook to a procedure and that procedure releases the object (sets it to Nothing), the object you passed will be set to Nothing when you come back from the call.
------
Hope this clears things a bit. You are getting yourself into something quite hard for a first project.
Excel is a COM application (also called an ActiveX application), not a .NET application. COM was the standard structure of applications using objects in Windows before .NET.
.NET and COM are 2 different worlds, they are not handled the same way in the computer memory. You must sometimes understand both in order to make them work together, not counting the fact that a .NET application does not talk directly to a COM application. It's hidden from you, but between your .NET application and Excel, there is a special dll called an interop, that is used as a bridge between the 2 worlds.
The interop handles the disprecancies between the structure of both types of applications, as well as some conversions.
For instance, a date in COM is stored as a Double that counts the days since January 31, 1899, while in .NET a date is stored as a Long that counts the number of ticks (a small fraction of a second) since January 1, 0001. When you pass a date to Excel from .NET, it needs to be converted, and the reverse the other way around.
This "translation" mechanism sometimes makes things harder than working only in .NET or only in COM, such as the
System.Runtime.InteropServices.Marshal.FinalReleaseComObject and ReleaseComObject that are, in my opinion, not necessary in the situations presented in this thread.
------
Good luck
Jacques Bourgeois
JBFI
http://www3.sympatico.ca/jbfi/homeus.htm
-
Thank you for taking the time to explain things so well.
I am learning very much... and it makes me feel better when a professional says "This is a difficult first application". Maybe I am not as slow as I thought.
I'll take a look at your information in more detail. I have a few ideas.. I think I can reconfigure things such that all of the calls are in the same "Run" form for the data collection. That may make things easier.
Thanks again.
Back to the code!
Similar Threads
-
By Pliroforikarios in forum .NET
Replies: 1
Last Post: 01-12-2006, 07:05 PM
-
Replies: 0
Last Post: 07-08-2002, 11:37 AM
-
By Dave Doknjas in forum .NET
Replies: 0
Last Post: 05-26-2002, 11:17 PM
-
By Depechie in forum .NET
Replies: 1
Last Post: 03-22-2002, 09:51 AM
-
Replies: 1
Last Post: 10-15-2001, 10:40 AM
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|