?
Solved

VB.NET 2010 and MS Excel

Posted on 2014-03-19
7
Medium Priority
?
240 Views
Last Modified: 2014-03-19
I have a VB.NET 2010 app that creates a new test report based on a template XLSX file. When I run my program and get back to my main form, if I go to the Task Manager I can see that Excel.exe process is still running. If I exit my program then the Excel process disappears from the task manager.
I have shown my code below. Is there some other command I am missing that will stop the Excel process?

Thanks,
Charlie

CODE:
Public Sub CreateReportA()

        If appExcel Is Nothing Then
            appExcel = New Excel.Application
        End If

        appExcel.Visible = False

        Dim myPath As String = Application.StartupPath
        Dim mynewfilename As String
        Dim myDate = Format(Now, "yyyy_MM_dd_hh_mm_ss")

        mynewfilename = myPath & "\" & "TestReports\" & rpt_Serial_Number_A & "_" & myDate & ".xlsx"

        reportFileNameSysA = mynewfilename

        oWB = appExcel.Workbooks.Open(myPath & "\" & "TestReportFinal.xlsx", True, True)
        oSheet = oWB.Sheets(1)
        oSheet.Range("rpt_Serial_Number").Value = rpt_Serial_Number_A
        oSheet.Range("rpt_Part_Number").Value = rpt_Part_Number_A
        oSheet.Range("rpt_ATP_Version").Value = rpt_ATP_Version_A
        oSheet.Range("rpt_Internal_Order").Value = rpt_Internal_Order_A
        oSheet.Range("rpt_Technician").Value = rpt_Technician_A
        oSheet.Range("rpt_Test_Date").Value = rpt_Test_Date_A

        oWB.SaveAs(mynewfilename)

        oSheet = Nothing
        oWB.Close()
        oWB = Nothing
        appExcel.Quit()
        appExcel = Nothing

    End Sub
0
Comment
Question by:charlieb01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 39939732
Hi charlieb01;

I believe what may be happening is that the com objects are not being released which causes Excel to remain running. try using the ReleaseComObject as shown below.

System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet)
oSheet = Nothing
oWB.Close()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB)
oWB = Nothing
appExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(appExcel)
appExcel = Nothing

Open in new window

0
 
LVL 14

Expert Comment

by:Matti
ID: 39939807
This is a unkonwn syntax Dim myDate = Format(Now, "yyyy_MM_dd_hh_mm_ss")
Now is for Date and Time is for specific timing Format returns a String in defined format

Matti
0
 

Author Comment

by:charlieb01
ID: 39939880
Hi Fernando,
I tried your suggestion but the Excel process is still running (as seen in Task Manager) and does not stop until I end my program. Any other ideas? I am really stumped by this.

Thanks in advance


Matti:
By creating the variable 'myDate' formatted as I did in my program and then appending that to the end of the new filename, I am assured that the new spreadsheet filename will be unique. It also allows my user to see when the test was run simply by looking at the entire filename.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39939900
If you have any other com objects associated with the Excel object they too need to be released. Com objects will not end until all the objects reference counts goes to zero.
0
 
LVL 14

Expert Comment

by:Matti
ID: 39939904
Well I think there is no 64bit solution Win32 API would have but this is a VBA/64bit app issue, so that you run someting interopp that's 32 bit port to 64 bit code. As you run this in Visual Studio try 32bit compilation, so the compile options from VS.
0
 

Author Comment

by:charlieb01
ID: 39940379
Fernando,
Do you know of anyway I can determine com objects that are running?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 39940638
Sorry but I do not.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Navigation is an important part of web design from a usability perspective. But it is often a pain when it comes to a developer’s perspective. By navigation, it often means menuing. This is less theory and more practical of how to get a specific gro…
There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The viewer will learn how to pass data into a function in C++. This is one step further in using functions. Instead of only printing text onto the console, the function will be able to perform calculations with argumentents given by the user.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question