Can't completely close MS Excel 2007...

Hi. I don't know if this is a VB2005 or MS Excel issue so I will start with VB2005.

This is how I open an Excel file in VB2005:

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
objExcel.Visible = True
objWorkbook = objExcel.Workbooks.Open("C:\Users\dvu\Documents\Data.xlsx")
objSheet = objWorkbook.Worksheets(1)

and this is how I close it:
Dim objExcel As New Excel.Application
objExcel.Application.Quit()

The problem is MS Excel always open a Book1.xlsx in addition to my Data.xlsx. As such closing Data.xlsx only closes Data.xlsx but not Book1.xlsx.
Over time, as I continue to develope and debug my program, Task Manager would collect "countless" Book1.xlsx. Wth EXCEL.EXE still opened, I noticed that my VB2005 doesn't work "completely" right.

If this is a VB2005 issue then how do I "completely" close everything that is Excel? If this is a Windows issue, and if you could offer a solution as well, I would truly appreciate any assistance. Thank you.

Quote:

---

and this is how I close it:
Dim objExcel As New Excel.Application
objExcel.Application.Quit()

---

What you are doing with those lines is opening a new instance of Excel, then promptly closing it. That has no impact on the instance you opened earlier. Get rid of the first line of that, because you already have an open instance in objExcel. If you then get a message that objExcel is undeclared, it is because you opened the first instance in a place that the second part can't see. The solution to that is that your objExcel should be declared at class scope, not inside a method.

Hi. So how do I put...

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
objExcel.Visible = True
objWorkbook = objExcel.Workbooks.Open("C:\Users\dvu\Documents\Data.xlsx")
objSheet = objWorkbook.Worksheets(1)

...in a class such that this Excel "setup" is global? currently I am adding the above codes to wherever I needed to Read from/Write to Excel (not good, I know). Thank you.

Shaggy is trying to tell you to do something like this:

Code:

---

Public Class ExcelManager

    Dim objExcel As New Excel.Application

    Public Sub CloseExcel
        objExcel.Application.Quit()
    End Sub

End Class

---

Just add whatever other methods you need.

Hi. I am a VB6 guy therefore VB2005.net is still new for me. I tried adding methods to the class as you have advised but it did not work too well:

Imports Microsoft.Office.Interop
Public Class ExcelManager

Dim objExcel As New Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objSheet As Excel.Worksheet
objWorkbook = objExcel.Workbooks.Open <-- error: not declared("C:\Users\dvu\Documents\Dai_light_Rev0.xlsx")
objSheet = objWorkbook.Worksheets(1) <-- error: not declared

Public Sub CloseExcel()
objExcel.Application.Quit()
End Sub

End Class REM end ExcelManager class

Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
CloseExcel() rem error not declared
REM Call CloseExcel() rem error not declared
End
End Sub

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

For Combo_index = 2 To 100
If (objSheet.Cells(1, Combo_index).Value = "end") Then REM doesn't see objSheet
Exit For
End If
ComboBox1.Items.Add(objSheet.Cells(1, Combo_index).Value) REM doesn't see objSheet
Next
ComboBox1.Text = "Choose Your Light..."
REM OvalShape1.Visible = True
REM OvalShape1.FillStyle = PowerPacks.FillStyle.Solid
ListBox1.Visible = False
Timer1.Enabled = False
CloseExcel() REM must quit Excel after each use else too many Book1.xlsx will be opened.
End Sub
End Class REM end Form1 class

Please use CODE tags to display your code.

What does "it did not work too well" mean?
You need to explain very clearly what it did, what it did not do and what you want it to do.

Hi.

the errors are:

Declaration expected [for this line: objWorkbook = objExcel.Workbooks.Open("C:\Users\dvu\Documents\Dai_light_Rev0.xlsx") ]

Declaration expected [for the line: objWorkbook = objExcel.Workbooks.Add() ]

Name 'CloseExcel' is not declared.
Name 'objSheet' is not declared.

Thank you.

p.s. how do you do code tags?

You can use CODE tags in two ways.

1. Click on the Code keyword in the menu
2. Type them manually

They look like this (without the spaces between the brackets)
[ CODE ] Code goes here [ /CODE ]

The reason you get 'CloseExcel' is not declared. is because you are using it wrong. To use a method of a class you need an instance of that class first (notice how nice [and easier to read] it looks with the code tags)

Code:

---

Dim EM as New ExcelManager
EM.CloseExcel

---

Or what may work better for you is to use a module instead of a class:

Code:

---

Module ExcelManager

---

Then you can call the CloseExcel method the way you already have it.

The reason you can not access objSheet from the form is because it is a member of ExcellManager, not Form1. You could make objSheet public, but a better way would be to make a property:

Code:

---

Property ReadOnly CurrentSheet() as Excel.Worksheet
    Get
        Return objSheet
    End Get
End Property

---

In this thread

http://www.vbforums.com/showthread.p...ighlight=Excel

you will find a class I use for managing data into and out of Excel. It may not be all that useful to you, since it uses late binding, but the general layout as to how I open the objects and close the objects would work for you. Late binding removes the need to explicitly import any one Excel interop, which means that it can work with any version of Excel (at least all the more modern versions). It's a bit odd, though, as everything is an Object.

I would also point out that you are not as unfamiliar with objects as you may think. Notice that a form is also a class. All of your code on a form goes into methods, but you can declare variables that are not inside methods. You can even set those variables to an initial value. However, you are always calling those methods, and accessing those variables, using some fairly strict rules. MS did make some simplifications to the rules, which can muddy the issue, but the rules are there. For instance, every control and method call in a form can be called using Me.SomeMethod and Me.SomeControl.SomeProperty. You don't actually NEED to type that Me., but you CAN. The reason you don't need to type that is largely a convention that has been around long before .NET (or even Windows), but it is always there. What it means is "call the item from this particular instance of the form or class".

Basically, if you look at what you do with a form, you will gain some understanding of how classes work, as well....and some of it will actually be right. Unfortunately, MS added a variety of tricks to VB to make it somewhat more familiar to VB6 users, such as the default instances of forms and the way they handle modules. Those additions look like exceptions to the rules, and can lead people astray, but they also do simplify some things.

Hi. One more question and that should be it...

How do I make objSheet public?

Like this:

Code:

---

Public objSheet As Excel.Worksheet

---

But it is better to make it a property. Using it like that can cause hard to find bugs.

I do not work much with Excel outside of a library called Aspose Cells but remember having issues with instances of Excel remaining open. The following has not been tested with Excel 2007 but I think it should work.

Change the name of the file to open, keep TaskManager open and run the code. Excel will show up then disappear.

Code:

---

  Sub KillExcelWhenDone()
      Dim myExcel As Microsoft.Office.Interop.Excel.Application
      myExcel = New Microsoft.Office.Interop.Excel.Application
      Dim aWorkbook As Microsoft.Office.Interop.Excel.Workbook
      Dim aWorkSheet As Microsoft.Office.Interop.Excel.Worksheet

      aWorkbook = myExcel.Workbooks.Open("C:\Book1.xls")
      aWorkSheet = CType(aWorkbook.Sheets("Sheet1"),  _
          Microsoft.Office.Interop.Excel.Worksheet)

      aWorkSheet.Activate()
      aWorkSheet.Range("A1").Value = "Demoing Excel Automation in .NET"

      With aWorkSheet.Range("A1").Font
        .Bold = True
      End With

      aWorkbook.Save()

      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(aWorkSheet)
      aWorkSheet = Nothing
      System.Runtime.InteropServices.Marshal.FinalReleaseComObject(aWorkbook)
      aWorkbook = Nothing
      myExcel.Quit()
      myExcel = Nothing

      GC.Collect()
      GC.WaitForPendingFinalizers()
  End Sub

---

Hi. Same error as before...

objSheet and objWorkbook not define...

Code:

---

Public Class ExcelManager
    Dim objExcel As New Excel.Application
    public objWorkbook As Excel.Workbook
    public objSheet As Excel.Worksheet

    Public Sub CloseExcel()
        objExcel.Quit()
    End Sub
End Class

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        objWorkbook = objExcel.Workbooks.Open("C:\Users\dvu\Documents\Data.xlsx")
        objSheet = objWorkbook.Worksheets(1)
        For Combo_index = 2 To 100
            If (objSheet.Cells(1, Combo_index).Value = "end") Then
                Exit For
            End If
            ComboBox1.Items.Add(objSheet.Cells(1, Combo_index).Value)
        Next
        ComboBox1.Text = "Choose Your Light..."
        ListBox1.Visible = False
        Timer1.Enabled = False
        Quit_Excel.CloseExcel()
    End Sub

---

Oh well. You have been very patient and helpful. I thank you for your help. Maybe one day I will understand all this .NET stuff. For now, I have a working solution, not a pretty solution, but a working one. Thanks, again.

Quote:

---

Originally Posted by dtvonly

Hi. Same error as before...

objSheet and objWorkbook not define...

[CODE]
Public Class ExcelManager
Dim objExcel As New Excel.Application
public objWorkbook As Excel.Workbook
public objSheet As Excel.Worksheet

---

Do you have Option Strict On?

When you get the message that the objects are not defined there should be an red error indicator which might shed some light on the issue as in you do not have the proper names space to the objects

See the following post http://www.vbforums.com/showpost.php...80&postcount=2 from yesterday on a similar issue

We want to help, and you are trying which is good. But you seem to be missing out on scope. You are referencing your variables out of scope. Using properties will help you. Or you can fully qualify your names, which you aren't doing either. You can not use variables that way. A tutorial will help you.
And the scoping rules are not new to .NET, they are the same in the way you are using your variables, if that helps you at all.

Quote:

---

Originally Posted by kevininstructor

I do not work much with Excel outside of a library called Aspose Cells

---

What is that? I have worked with adipose cells, but not Aspose Cells. I would expect that they are somewhat different.;)

Quote:

---

Originally Posted by Shaggy Hiker

What is that? I have worked with adipose cells, but not Aspose Cells. I would expect that they are somewhat different.;)

---

Aspose cells is an Excel library where Excel need not be installed on the user’s machine to generate Excel files. One of the cool features is known as designer files. You create a template where in the template you data bind cells to a data source say from a DataTable then pass the datasource to Aspose to populate the Excel file. Evaluation versions are not crippled just add their copyright notice.

Quick non-designer code example

Code:

---

Dim Book As Workbook = New Workbook()
Dim Sheet As Worksheet

Book.Open(AsposeSupport.DesignerFile("MainReport"))
Sheet = Book.Worksheets(0)
Sheet.Cells("B4").PutValue(“assign data”)

---

Copy cell

Code:

---

Sheet.Cells.CopyRow(TemplateSheet.Cells, 0, Row)

---

Save

Code:

---

Book.Save(OutputFile, FileFormatType.Excel2003)

---

Set a document property

Code:

---

Dim doc As DocumentProperty = wb.Worksheets.BuiltInDocumentProperties("Author")
doc.Value = "Kevin Gallagher"

---

Insert value and resize cell

Code:

---

ws.Cells("A1").PutValue("Hello World This is a test")
ws.AutoFitColumn(ws.Cells("A1").Column, 0, 0)

---