Interoperation / Office Integration Topics include: COM, P/Invoke (e.g. Win32 API), marshaling, distributed applications, VBA and interacting with office apps

Go Back  Xtreme .NET Talk > .NET > Interoperation / Office Integration > Create new excel file from VB .net app


Closed Thread
 
Thread Tools Display Modes
  #1  
Old 04-21-2003, 03:17 PM
msellery msellery is offline
Newcomer
 
Join Date: Mar 2003
Posts: 17
msellery is on a distinguished road
Default Create new excel file from VB .net app



Here is some sample code I've pieced together from other posts on this site. It takes a file C:\Kk.xls, puts in useful data, and saves it as anything I want.

I don't need there to be a Kk file, I'd like to just create an excel file and then save it. I also don't need any dialog boxes prompting the user, so the below code is appropriate:
Code:
Dim xlApp As Excel.Application
        Dim xlMappe As Excel.Workbook
        Dim xlZelle As Excel.Range

        xlApp = New Excel.Application()
        'xlMappe = New Excel.Workbook()

        xlMappe = xlApp.Workbooks.Open("C:\Kk.xls")
        ' I don't want there to have to be Kk. I'd just like to create a new workbook, not sure how to do this.

        xlZelle = xlMappe.Worksheets(1).Range("A1")
        xlZelle.Value = "whatever data I want"

       
        Dim blah as String
        blah = "C:\Whatever_File_I_want.xls"
        xlMappe.SaveAs(blah)
        xlApp.Quit()
        xlZelle = Nothing
        xlMappe = Nothing
        xlApp = Nothing

Last edited by PlausiblyDamp; 11-26-2005 at 01:25 PM.
  #2  
Old 04-21-2003, 04:18 PM
Robby's Avatar
Robby Robby is offline
Ultimate Contributor
 
Join Date: Nov 2002
Location: Montreal, Ca.
Posts: 3,460
Robby is on a distinguished road
Default

A couple of samples which should do what you want...
Code:
' Declare Excel object variables and create types
        Dim xlApp As Excel.Application
        Dim xlBook As Excel.Workbook
        Dim xlSheet As Excel.Worksheet
        xlApp = CType(CreateObject("Excel.Application"), Excel.Application)
        xlBook = CType(xlApp.Workbooks.Add, Excel.Workbook)
        xlSheet = CType(xlBook.Worksheets(1), Excel.Worksheet)

        ' Insert data
        xlSheet.Cells(1, 2) = 5000
        xlSheet.Cells(2, 2) = 75
        xlSheet.Cells(3, 1) = "Total"
        ' Insert a Sum formula in cell B3
        xlSheet.Range("B3").Formula = "=Sum(R1C2:R2C2)"
        ' Format cell B3 with bold
        xlSheet.Range("B3").Font.Bold = True
        ' Display the sheet
        xlSheet.Application.Visible = True
        ' Save the sheet to c:\vbnetsbs\chap13 folder
        xlSheet.SaveAs("C:\myexcelsheet.xls")
        ' Leave Excel running and sheet open
 
Code:
Dim EXL As New Excel.Application()
        Dim WSheet As New Excel.Worksheet()
        WSheet = EXL.Workbooks.Add.Worksheets.Add
        With WSheet
            .Cells(2, 1).Value = "1st Quarter"
            .Cells(2, 2).Value = "2nd Quarter"
            .Cells(2, 3).Value = "3rd Quarter"
            .Cells(2, 4).Value = "4th Quarter"
            .Cells(2, 5).Value = "Year Total "
            .Cells(3, 1).Value = 123.45
            .Cells(3, 2).Value = 435.56
            .Cells(3, 3).Value = 376.25
            .Cells(3, 4).Value = 425.75
            .Range("A2:E2").Select()
            With EXL.Selection.Font
                .Name = "Verdana"
                .FontStyle = "Bold"
                .Size = 12
            End With
        End With
        WSheet.Range("A2:E2").Select()
        EXL.Selection.Columns.AutoFit()
        WSheet.Range("A2:E2").Select()
        With EXL.Selection
            .HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
        End With
        ' Format numbers
        WSheet.Range("A3:E3").Select()
        With EXL.Selection.Font
            .Name = "Verdana"
            .FontStyle = "Regular"
            .Size = 11
        End With
        WSheet.Cells(3, 5).Value = "=Sum(A3:D3)"

        Dim R As Excel.Range
        R = WSheet.UsedRange
        Dim row, col As Integer
        For row = 1 To R.Rows.Count
            TextBox1.AppendText("ROW " & row & vbCrLf)
            For col = 1 To R.Columns.Count
                TextBox1.AppendText("[" & row & ", " & col & _
                     " : " & vbTab & R.Cells(row, col).value & "]" & vbCrLf)
            Next
            TextBox1.AppendText(vbCrLf)
        Next
        Try
            WSheet.SaveAs("C:\TEST.XLS")
        Catch
        End Try
        Me.Text = "File Created"
        EXL.Workbooks.Close()
        EXL.Quit()
__________________
Visit...Bassic Software
  #3  
Old 04-22-2003, 06:31 PM
msellery msellery is offline
Newcomer
 
Join Date: Mar 2003
Posts: 17
msellery is on a distinguished road
Default

Thanks for your help, but there is a new small issue.

Unless I use the "End" command to terminate my program, EXCEL.EXE will still be running after I execute your code. The result is that you can not open the Excel file because Excel is in some weird "locked" state. Exiting my VB application will unlock Excel though, and everything works fine. How do I completely exit and unload Excel from memory after executing your code?
  #4  
Old 04-22-2003, 08:07 PM
Robby's Avatar
Robby Robby is offline
Ultimate Contributor
 
Join Date: Nov 2002
Location: Montreal, Ca.
Posts: 3,460
Robby is on a distinguished road
Default

If you look at the second example I posted, EXL.Quit() will terminate Excel, to verify this, open your Task Manager and look out for Excel.exe.

I did not use Quit() in the first sample only to demonstrate that you can allow Excel to stay open.
__________________
Visit...Bassic Software
  #5  
Old 05-02-2003, 05:50 AM
Kurt Kurt is offline
Regular

Preferred language:
VB.NET
 
Join Date: Feb 2003
Location: Copenhagen
Posts: 99
Kurt is on a distinguished road
Default

The Quit methode doesn't seem to work for me. After the execution of this statement, excel.exe is still in the job list of the task manager.
__________________
qrt
  #6  
Old 05-02-2003, 11:56 AM
Robby's Avatar
Robby Robby is offline
Ultimate Contributor
 
Join Date: Nov 2002
Location: Montreal, Ca.
Posts: 3,460
Robby is on a distinguished road
Default

Kurt, can you post the relevant code?
__________________
Visit...Bassic Software
  #7  
Old 05-08-2003, 04:15 AM
Kurt Kurt is offline
Regular

Preferred language:
VB.NET
 
Join Date: Feb 2003
Location: Copenhagen
Posts: 99
Kurt is on a distinguished road
Default

Here is the excel part of it

Code:
Dim XL As New Excel.Application()
            Dim XlSheet As New Excel.Worksheet()
            If XL Is Nothing Then
                MsgBox("Cant create excel object.")
                Exit Function
            End If
            Try
                XlSheet = XL.Workbooks.Open(strXlFile).Worksheets("ToCollect")
            Catch
                MsgBox("Couldn't open or find :" & strXlFile & ". Error message = " & Err.Description & _
                            ". Error number = " & Err.Number)
                XlSheet = Nothing
                XL = Nothing
                Return LocalArrayToSend
                Exit Function
            End Try
            strActivePhonenumber = XlSheet.Cells(4, 3).value

            If XlSheet.Cells(8, 3).value = "v" Then
                blCollectAnal1 = True
            Else
                blCollectAnal1 = False
            End If

            If XlSheet.Cells(9, 3).value = "v" Then
                blCollectAnal2 = True
            Else
                blCollectAnal2 = False
            End If

'''''''       .....
'''''''

            If XlSheet.Cells(64, 3).value = "v" Then
                blCollectRemainingActualValuesNotLogged = True
            Else
                blCollectRemainingActualValuesNotLogged = False
            End If
            XL.Workbooks.Close()
            XL.Quit()
            XlSheet = Nothing
            XL = Nothing

As you can see, I also tried to take the reference to the excel object away by putting the variables to Nothing.... But this doesn't do no good. Should I leave the last two statements out? When the quit methode is invoked, excel.exe remains in the job list from the task manager...
__________________
qrt
  #8  
Old 06-18-2003, 09:44 PM
tsarna tsarna is offline
Newcomer
 
Join Date: Jun 2003
Posts: 3
tsarna is on a distinguished road
Default

I am also having this same problem with "excel" remaining in the task manager processes. This happens when leaving Excel open or closing it.

Anyone have any ideas on how to get around tis problem?
  #9  
Old 06-19-2003, 05:17 PM
Robby's Avatar
Robby Robby is offline
Ultimate Contributor
 
Join Date: Nov 2002
Location: Montreal, Ca.
Posts: 3,460
Robby is on a distinguished road
Default

Did you read any of the above?
__________________
Visit...Bassic Software
  #10  
Old 06-21-2003, 10:39 AM
Neutrino Neutrino is offline
Newcomer
 
Join Date: Jun 2003
Posts: 21
Neutrino is on a distinguished road
Default

Hi Robby,

I wanted to know whether u added a reference in your project to MS Excel 9.0 component or 10.0 comp for the above code. Is there any difference in the coding for these two components?

Amicalement,
Neutrino
  #11  
Old 06-21-2003, 10:49 AM
Robby's Avatar
Robby Robby is offline
Ultimate Contributor
 
Join Date: Nov 2002
Location: Montreal, Ca.
Posts: 3,460
Robby is on a distinguished road
Default

I haven't used 10.0, I doubt that simple code like this would make any difference. (but I could be wrong)
__________________
Visit...Bassic Software
  #12  
Old 06-24-2003, 08:17 AM
Kurt Kurt is offline
Regular

Preferred language:
VB.NET
 
Join Date: Feb 2003
Location: Copenhagen
Posts: 99
Kurt is on a distinguished road
Default

Robby, can you see the problem with the piece of code above, in which excel.exe remains in the taskmanager even though I call the quit-methode???
__________________
qrt
  #13  
Old 06-25-2003, 10:28 AM
sj1187534's Avatar
sj1187534 sj1187534 is offline
Centurion

Preferred language:
VB.NET
 
Join Date: Jun 2003
Location: Dallas, Houston, etc.etc.
Posts: 108
sj1187534 is on a distinguished road
Default

Hi....Can anyone tell me which namespace we have to import to use the Excel.Application....My code is saying that Excel.Application is not defined....

Thanks,
SJ
  #14  
Old 06-27-2003, 09:01 AM
tsarna tsarna is offline
Newcomer
 
Join Date: Jun 2003
Posts: 3
tsarna is on a distinguished road
Default

I have slightly modified the above code using the Microsoft Excel 9.0 Object Library, and it still leaves EXCEL.EXE sticking around in the Task Manager until the program is closed.

Robby (or anyone else), could you test the below code and see if you have the same problem.

You should be able to easily recreate the example using the following code and pasting it into an empty Windows Form Program with a button. You will also need to create a reference to the the COM Excel 9.0 Library...

Make sure to point the strXLFile to a test Excel file on your computer.

Thanks

--------------------------------------------------------------------
Code:
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim XL As New Excel.Application()
        Dim XlSheet As New Excel.Worksheet()

        Dim strXLFile = "D:\Temp\Book1.xls"

        If XL Is Nothing Then
            MsgBox("Cant create excel object.")
            Exit Sub
        End If
        Try
            XlSheet = XL.Workbooks.Open(strXLFile).Worksheets("Sheet1")
        Catch
            MsgBox("Couldn't open or find :" & strXlFile & ". Error message = " & Err.Description & _
                        ". Error number = " & Err.Number)
            XlSheet = Nothing
            XL = Nothing
            Exit Sub
        End Try

        XlSheet.Cells(4, 3).value = "111-222-3344"
        XlSheet.Cells(8, 3).value = "v"
        XL.Workbooks.Close()
        XL.Quit()
        XlSheet = Nothing
        XL = Nothing

    End Sub
End Class

Last edited by PlausiblyDamp; 11-26-2005 at 01:27 PM.
  #15  
Old 06-27-2003, 01:42 PM
tsarna tsarna is offline
Newcomer
 
Join Date: Jun 2003
Posts: 3
tsarna is on a distinguished road
Default

After posting this, I saw a work around posted by "Melegant" that will kill Excel Processes that are not active by the end user. I've appended it to this message in case others are following this thread like I was.

It does solve the problem, but I do question what the actual underlying problem is?

**** FROM posting by "Melegant" ****
Code:
Private Sub KillExcelPr()
        Dim mp As Process() = Process.GetProcessesByName("EXCEL")

        Dim p As Process
        For Each P In mp
            If P.Responding Then
                If p.MainWindowTitle = "" Then
                    p.Kill()
                End If
            Else
                p.Kill()
            End If
        Next p
    End Sub

Last edited by PlausiblyDamp; 11-26-2005 at 01:27 PM.
  #16  
Old 08-18-2003, 07:21 PM
synakamr synakamr is offline
Newcomer

Preferred language:
VB.NET
 
Join Date: Aug 2003
Posts: 2
synakamr is on a distinguished road
Default

Try using
'System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)'
insted of
'xlApp = Nothing'.
Worked for me in the sample below.

Quote:
Code:
Public Function Test(ByVal FileName As String)
        Dim xlApp As New Excel.Application

        Dim books As Excel.Workbooks = xlApp.Workbooks
        Dim book As Excel.Workbook = books.Open(FileName)

        xlApp.DisplayAlerts = False

        Dim sheets As Excel.Sheets = book.Worksheets
        Dim sheet As Excel.Worksheet = CType(sheets(1), Excel.Worksheet)

        Dim range As Excel.Range = sheet.Cells
        range(2, 1) = "success!!"
        range(2, 2) = "success?"

        book.Save()
        book.Close(False)

        xlApp.DisplayAlerts = True

        xlApp.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(range)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheets)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(books)
        System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)

        xlApp = Nothing
    End Function

Last edited by PlausiblyDamp; 11-26-2005 at 01:27 PM.
  #17  
Old 09-30-2003, 01:21 PM
quinkan quinkan is offline
Newcomer
 
Join Date: Sep 2003
Posts: 1
quinkan is on a distinguished road
Default Same issue with VB6, any idea

Hello:

I have the same issue, but with VB6. Any idea on how to resolve it?

Thanks.

Quin
  #18  
Old 12-17-2003, 10:57 AM
JimU JimU is offline
Newcomer
 
Join Date: Dec 2003
Location: Suffolk, UK
Posts: 1
JimU is on a distinguished road
Default

I've searched everywhere for a solution to this and now I've got one I thought it was worth posting it here to save someone else alot of hastle.

My problem was with the following code (this is just a snippet):
xlBook = xlApp.Workbooks.Open(ExcelTemplate)
When this was running the EXCEL.EXE process didn't end until the VB app. was ended.

I changed the code to this:
Code:
Dim xlBooks As Excel.Workbooks
xlBooks = xlApp.Workbooks
xlBook = xlBooks.Open(ExcelTemplate)
And added the following to tidy up:
Code:
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlBooks)
xlBooks = Nothing
I found out about it at http://support.microsoft.com/default...&Product=vbNET

My application works fine now, I hope it helps someone else.

Last edited by PlausiblyDamp; 11-26-2005 at 01:28 PM.
  #19  
Old 01-16-2004, 01:39 PM
Robby's Avatar
Robby Robby is offline
Ultimate Contributor
 
Join Date: Nov 2002
Location: Montreal, Ca.
Posts: 3,460
Robby is on a distinguished road
Default

This thread is no longer accepting posts, please start a new thread.

Thanks
__________________
Visit...Bassic Software
Closed Thread

Bookmarks

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 On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create Excel App in VB.NET hophead3 Interoperation / Office Integration 3 04-22-2006 08:33 AM
Create new excel file from VB .net app hloos Interoperation / Office Integration 0 01-16-2004 01:08 PM
Create new excel file from VB .net app cyates Interoperation / Office Integration 1 09-30-2003 07:04 PM
create or saveas new Excel file nd4spdrace Interoperation / Office Integration 0 03-03-2003 04:43 PM

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