VB.Net Creating Excel help 
Author Message
 VB.Net Creating Excel help

Hello,
  I'm having a problem when creating an Excel file,
filling it and quiting the application , when all this is
done in Visual Basic, OR Visual Basic.Net using the
Excel.Application object.

Everything works great and the Excel file is made,
formatted, filled and closed, but when I try to open the
excel file that I've just saved to the desktop, things
start to freeze up, and I think I know why:

  It seems that the Excel object is not properly closing
when I release all instances to it.  I call the .Quit
Method and set the object = nothing, but it doesn't seeem
to matter.  I have found a sort of workaround though:

If I open MS Excel and leave it open while running my
program , I'm able to open my file no problem, after the
application has run.    But I really don't want to have
to do this if I don't have to.  Anyone ever seen this?

Here's an example of my code, opening and closing the
object.  

Dim objExOut As New Excel.Application()
        Dim objWb As Excel.Workbook
        Dim objSheet As New Excel.Worksheet()
        Dim osheet2 As New Excel.Worksheet()

'Closing the object

            objWb.Close()
            objSheet = Nothing
            osheet2 = Nothing
            objWb = Nothing
            objExOut.Quit()

Any help would be greatly appreciated.

Thanks,
McDoogle



Mon, 07 Feb 2005 06:18:11 GMT  
 VB.Net Creating Excel help
I'm more familiar with vb6

'Create the object
Set objExOut = CreateObject("Excel.Application")

'do stuff

'then
objWb.Close()
objExOut.Quit()

'Then, after closing the objects,
set objExOut = nothing
set objWb = nothing
set objSheet  = nothing
set osheet2  = nothing

'don't forget the set

Also, it is better to change the following:

Dim objExOut As New Excel.Application()
Dim objWb As Excel.Workbook
Dim objSheet As New Excel.Worksheet()
Dim osheet2 As New Excel.Worksheet()

to:

Dim objExOut As object
Dim objWb As object
Dim objSheet As object
Dim osheet2 As object

and then

Set objExOut = CreateObject("Excel.Application")

after you've got everything working properly and are ready to compile.

Intellisense makes programming easier and fun, but you will find that
working with the Excel.[objects] (or word or any other ms lib) is a
nightmare without late binding them.  End users may not have the olb you
have referenced in your project (whether it's because of an upgrade to MS or
downgrade for another app) and then when they try to access your program
everything goes boom.  (Not to mention when you upgrade your lib it usually
recompiles with the new lib and that does not mean they will have upgraded
theirs (unless you force a new install every time you recompile, and
again--boom.)

There's a MSkb article that explains my suggestion and also one that
explains why the excel object is not released, but I don't have them
archived and don't know off the top of my head.  Search the MS knowledge
base and you will find what you are looking for.


Quote:
> Hello,
>   I'm having a problem when creating an Excel file,
> filling it and quiting the application , when all this is
> done in Visual Basic, OR Visual Basic.Net using the
> Excel.Application object.

> Everything works great and the Excel file is made,
> formatted, filled and closed, but when I try to open the
> excel file that I've just saved to the desktop, things
> start to freeze up, and I think I know why:

>   It seems that the Excel object is not properly closing
> when I release all instances to it.  I call the .Quit
> Method and set the object = nothing, but it doesn't seeem
> to matter.  I have found a sort of workaround though:

> If I open MS Excel and leave it open while running my
> program , I'm able to open my file no problem, after the
> application has run.    But I really don't want to have
> to do this if I don't have to.  Anyone ever seen this?

> Here's an example of my code, opening and closing the
> object.

> Dim objExOut As New Excel.Application()
>         Dim objWb As Excel.Workbook
>         Dim objSheet As New Excel.Worksheet()
>         Dim osheet2 As New Excel.Worksheet()

> 'Closing the object

>             objWb.Close()
>             objSheet = Nothing
>             osheet2 = Nothing
>             objWb = Nothing
>             objExOut.Quit()

> Any help would be greatly appreciated.

> Thanks,
> McDoogle



Mon, 07 Feb 2005 08:10:38 GMT  
 VB.Net Creating Excel help

Hello,
  I'm having a problem when creating an Excel file,
filling it and quiting the application , when all this is
done in Visual Basic, OR Visual Basic.Net using the
Excel.Application object.

Everything works great and the Excel file is made,
formatted, filled and closed, but when I try to open the
excel file that I've just saved to the desktop, things
start to freeze up, and I think I know why:

  It seems that the Excel object is not properly closing
when I release all instances to it.  I call the .Quit
Method and set the object = nothing, but it doesn't seeem
to matter.  I have found a sort of workaround though:

If I open MS Excel and leave it open while running my
program , I'm able to open my file no problem, after the
application has run.    But I really don't want to have
to do this if I don't have to.  Anyone ever seen this?

Here's an example of my code, opening and closing the
object.  

Dim objExOut As New Excel.Application()
        Dim objWb As Excel.Workbook
        Dim objSheet As New Excel.Worksheet()
        Dim osheet2 As New Excel.Worksheet()


'Closing the object

            objWb.Close()
            objSheet = Nothing
            osheet2 = Nothing
            objWb = Nothing
            objExOut.Quit()

Any help would be greatly appreciated.

Do you reference any other Excel objects in your code? Does this happen only in the IDE or does it
happen in the compiled version of your application as well?

You might want to set objExOut to Nothing after executing the Quit method.


Microsoft MVP (Visual Basic)



Tue, 08 Feb 2005 00:38:06 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. VB.NEt Beginner: Creating a COM+ Object in VB.net

2. Accessing VBIDE.VBE.VBProjects via Excel in VB .NET Causes Excel To Crash When App Terminates

3. Accessing VBIDE.VBE.VBProjects via Excel in VB .NET Causes Excel To Crash When App Terminates

4. Accessing VBIDE.VBE.VBProjects via Excel in VB .NET Causes Excel To Crash When Closed

5. Help > Crash saving Excel through VB.Net

6. Need help doing a Subtotal through Excel automation in vb.net

7. VB.NET and Excel - HELP!!!

8. HELP!: Automating Excel on client PV from VB .NET Web Form:

9. Help with writing Datagrid Contents back to EXCEL via VB.NET

10. what create table using ADO.NET to VB.NET

11. create EXCEL with VB without EXCEL installed

12. Help: How to create shortcuts in VB.NET

 

 
Powered by phpBB® Forum Software