VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
Go Back  Xtreme Visual Basic Talk > > > VB.NET Excel Program Leaves EXCEL.EXE floating after completion


Reply
 
Thread Tools Display Modes
  #1  
Old 02-24-2012, 03:31 PM
asjohnson asjohnson is offline
Newcomer
 
Join Date: Feb 2012
Posts: 1
Default VB.NET Excel Program Leaves EXCEL.EXE floating after completion


I am writing a program that loops through all of the sheets in an Excel workbook and saves each sheet as its own workbook. It turned out to be a bit trickier than I expected, since the Sheet.Copy method creates a strange object (see here for the MSDN discussion that I believe is relevant: http://msdn.microsoft.com/en-us/library/ms178779.aspx).

Anyway, I found a Stack Overflow post ( http://stackoverflow.com/questions/3...ook-to-another) that got me to where I am, which is essentially complete, outside of one hanging EXCEL.EXE process that is left after the program completes, which I believe is a result of the sheet.copy method creating a new workbook (potentially a new application as well?). The other thing I run into, which I believe is related to this, is that even though I have my application set to visibility off, when I run the program, excel still opens up and is visible going through the steps.

Here is my code:
Code:
    Imports System.Data
    Imports System.IO
    Imports Microsoft.Office.Interop
    Imports Office = Microsoft.Office.Core
    Imports xlNS = Microsoft.Office.Interop.Excel
    Imports System.Runtime.InteropServices

    Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        'Get information from text boxes
        Dim InputLocation As String
        Dim OutputLocation As String

        InputLocation = InputLoc.Text & "\" & FileName.Text

        If OutputLoc.Text = "" Then
            OutputLocation = InputLoc.Text
        Else
            OutputLocation = OutputLoc.Text
        End If

        'Make file to save files in
        ' Get date and time in filename as well
        Dim TLDateTime As String
        Dim TLDay As String
        Dim TLMonth As Integer
        Dim TLYear As Integer
        Dim TLHour As Integer
        Dim TLMinute As Integer
        Dim TLDate As String
        Dim TLTime As String
        Dim TLSecond As Integer

        TLDay = DateTime.Now.Day
        TLMonth = DateTime.Now.Month
        TLYear = DateTime.Now.Year
        TLHour = DateTime.Now.Hour
        TLMinute = DateTime.Now.Minute
        TLSecond = DateTime.Now.Second

        Dim MyDate As New DateTime(TLYear, TLMonth, TLDay, TLHour, TLMinute, TLSecond)
        Dim MyString As String = MyDate.ToString("MMMddyyyy_HHmmss")
        TLDate = TLMonth.ToString + TLDay.ToString + TLYear.ToString
        TLTime = TLHour.ToString + TLMinute.ToString
        TLDateTime = TLDate + "_" + TLTime

        Try
            Directory.CreateDirectory(OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime)
            OutputLocation = OutputLocation & "\" & "Field Sales Report Graphs " & TLDateTime
        Catch
            MsgBox("Trying to create a file that exists, please delete it. If the file does not exist check to make sure your output location exists")
        End Try

        'Open up excel file with information in it

        Dim xlApp1 As Excel.Application
        Dim locs As Excel.Workbook

        Dim exportsheet As Excel.Worksheet
        xlApp1 = New Excel.Application
        xlApp1.Visible = True
        xlApp1.Application.DisplayAlerts = False
        locs = xlApp1.Workbooks.Open(InputLocation)

        'locsws = locs.ActiveSheet
        Dim wkshtcount = locs.Worksheets.Count - 1
        Dim fileNames As New ArrayList

        For counter = 1 To wkshtcount + 1
            'identify and copy sheet to move
            exportsheet = CType(locs.Worksheets(counter), Excel.Worksheet)
            fileNames.Add(exportsheet.Name)
            exportsheet.Copy(Type.Missing, Type.Missing)

            exportsheet = xlApp1.Workbooks("Book" & counter).Sheets(1)

            exportsheet.SaveAs(Filename:=OutputLocation & "\" & fileNames(counter - 1) & ".xlsx")

            'close excel and release com objects
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
            exportsheet = Nothing
            xlApp1.Workbooks(fileNames(counter - 1)).Close(False)


        Next
        'close excel and release com objects
        locs.Close(False)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(locs)
        locs = Nothing
        xlApp1.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1)
        xlApp1 = Nothing


    End Sub
    End Class
Now I think the problem comes from the end of the loop, where I try to close the export file and the new worksheet it creates:
Code:
            'close excel and release com objects
            System.Runtime.InteropServices.Marshal.ReleaseComObject(exportsheet)
            exportsheet = Nothing
            xlApp1.Workbooks(fileNames(counter - 1)).Close(False)
I can't figure out what to do to release the `ComObject` for the new worksheet that is created. I have been trying all sorts of things, but it always throws a COM error when I do it and if I try to define it as nothing (like I do with exportsheet) is says that it is read only by default, so I can't do it. It seems like it should be something as simple as:

System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp1.Workboo ks(fileNames(counter - 1)))

But that is not the case. I have tried a few variants of this and I am thinking it has to do with the MSDN link above, but I can't quite sort out what to do. So the code works for my purposes, outside of leaving one EXCEL.EXE after it is done.

As far as a test file, I am just using an Excel file with 3 sheets and I put some information on each and change the sheet name, so it is easy to see if it is working or not.

Any thoughts would be appreciated, thanks.

Last edited by Flyguy; 02-25-2012 at 02:11 AM.
Reply With Quote
  #2  
Old 02-27-2012, 05:19 PM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

I strongly suggest that You read Mike Rosenblum's excellent article Automating Office Programs with VB.Net / COM Interop

It goes through the cleaning up process and will give You a correct picture of it.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
Reply

Tags
automation, com reference, excel, vb.net


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
 
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
VB.NET Excel Program Leaves EXCEL.EXE floating after completion
 
-->