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 R As Excel.Range R = WSheet.UsedRange Dim row, col AsInteger For row = 1To R.Rows.Count TextBox1.AppendText("ROW " & row & vbCrLf) For col = 1To 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?
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...
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.
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" ****
PrivateSub KillExcelPr() Dim mp As Process() = Process.GetProcessesByName("EXCEL")
Dim p As Process For Each P In mp If P.RespondingThen If p.MainWindowTitle = ""Then p.Kill() EndIf Else p.Kill() EndIf Next p EndSub
Last edited by PlausiblyDamp; 11-26-2005 at 02:27 PM.