Create new excel file from VB .net app

[msellery]

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:

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

Edited November 26, 2005 by PlausiblyDamp

[Robby]

A couple of samples which should do what you want...

       ' 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

       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()

[msellery]

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?

[Robby]

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.

[Kurt]

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.

[Robby]

Kurt, can you post the relevant code?

[Kurt]

Here is the excel part of it

 

           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...

[tsarna]

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?

[Robby]

Did you read any of the above?

[Neutrino]

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

[Robby]

I haven't used 10.0, I doubt that simple code like this would make any difference. (but I could be wrong)

[Kurt]

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???

[sj1187534]

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

[tsarna]

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

 

--------------------------------------------------------------------

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

:confused:

Edited November 26, 2005 by PlausiblyDamp

[tsarna]

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" ****

   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

Edited November 26, 2005 by PlausiblyDamp

[synakamr]

Try using

'System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp)'

insted of

'xlApp = Nothing'.

Worked for me in the sample below.

 

   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

Edited November 26, 2005 by PlausiblyDamp

[quinkan]

Same issue with VB6, any idea

 

Hello:

 

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

 

Thanks.

 

Quin

[JimU]

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:

Dim xlBooks As Excel.Workbooks
xlBooks = xlApp.Workbooks
xlBook = xlBooks.Open(ExcelTemplate)

And added the following to tidy up:

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.

Edited November 26, 2005 by PlausiblyDamp

[Robby]

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

 

Thanks