[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 244
  • Last Modified:

VB.NET 2010 and MS Excel

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
charlieb01
Asked:
charlieb01
  • 3
  • 2
  • 2
1 Solution
 
Fernando SotoCommented:
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
 
MattiCommented:
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
 
charlieb01Author Commented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
Fernando SotoCommented:
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
 
MattiCommented:
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
 
charlieb01Author Commented:
Fernando,
Do you know of anyway I can determine com objects that are running?
0
 
Fernando SotoCommented:
Sorry but I do not.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now