Create new excel file from VB .net app

Status
Not open for further replies.
M

msellery

Member
Joined
Mar 19, 2003
Messages
17
Here is some sample code Ive 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 dont need there to be a Kk file, Id like to just create an excel file and then save it. I also dont 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 dont want there to have to be Kk. Id 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 a moderator:
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()
 
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?
 
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.
 
The Quit methode doesnt seem to work for me. After the execution of this statement, excel.exe is still in the job list of the task manager.
 
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("Couldnt 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 doesnt 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...
 
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?
 
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
 
I havent used 10.0, I doubt that simple code like this would make any difference. (but I could be wrong)
 
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???
 
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("Couldnt 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
:confused:
 
Last edited by a moderator:
After posting this, I saw a work around posted by "Melegant" that will kill Excel Processes that are not active by the end user. Ive 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 a moderator:
Try using
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)
insted of
xlApp = Nothing.
Worked for me in the sample below.

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
Click to expand...
 
Last edited by a moderator:
Same issue with VB6, any idea

Hello:

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

Thanks.

Quin
 
Ive searched everywhere for a solution to this and now Ive 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 didnt 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.aspx?scid=kb;en-us;317109&Product=vbNET

My application works fine now, I hope it helps someone else.
 
Last edited by a moderator:
Status
Not open for further replies.
Back
Top