Click here to Skip to main content
15,561,125 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi I'm working with a vb .net web application project using Visual Studio 2010.I used the following code to generate an excel workbook, worksheet and store it in a specified location. What I need is that the generated excel sheet has to be opened in a new window allowing the user to make some changes. How could I do this? Please help me.
Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Try
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Columns.ColumnWidth = 20
        xlWorkSheet.Cells(1, 1) = "TEMPLATE NAME"
        xlWorkSheet.Cells(1, 2) = "PROFILE ID"
        xlWorkSheet.Cells(1, 3) = "LANGUAGE_CODE"
        Dim i As Integer = 4
        If ds.Tables.Count > 0 Then
                If ds.Tables(0).Rows.Count > 0 Then
                    For Each drow As DataRow In ds.Tables(0).Rows
                        xlWorkSheet.Cells(1, i) = UCase(drow(0).ToString())
                        i = i + 1
                    Next
                End If
        End If
            xlWorkBook.Close()
            xlApp.Quit()
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
            xlWorkSheet.SaveAs("C:\mlrp\InputTemplate1.xls")

releaseOject method is implemented as
Private Sub releaseObject(ByVal obj As Object)
       Try
           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
           obj = Nothing
       Catch ex As Exception
           obj = Nothing
       Finally
           GC.Collect()
       End Try
   End Sub

Thanks in advance,
Vanathi
Posted

This can be done by using the Process object. i have code in C# for the same, you can convert this into VB.net and use that.
System.Diagnostics.Process proc = new System.Diagnostics.Process();
            proc.EnableRaisingEvents = false;
            proc.StartInfo.FileName =<<Specify excel palth here>>;
            proc.Start();


Ram
 
Share this answer
 
v2
Comments
Manfred Rudolf Bihy 2-Dec-10 4:47am    
Good call!
Vanathi.A 2-Dec-10 5:13am    
Thank you!! I'm getting the exception "Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80080005 Server execution failed (Exception from HRESULT: 0x80080005 (CO_E_SERVER_EXEC_FAILURE))." at the line <pre>xlApp = New Excel.Application </pre>
I've tried giving permissions by running "dcomcnfg". I don't know why I'm getting this exception. My application was working fine in the morning. Please help me!
Did you try Googling 'Open Excel file using early or late binding VB.NET'?
 
Share this answer
 
If you want to show the generated excel sheet then the easiest way would be use a xlApp.Visible = True in place of xlApp.Quit(). This will show the excel sheet in a new window. the excel application will be closed itself when user closes the excel sheet.
 
Share this answer
 
Comments
Vanathi.A 2-Dec-10 8:13am    
Thank u!! I wanna read the contents of the worksheet cell by cell.

Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
xlApp = New Excel.Application
xlWorkBook = xlApp.Workbooks.Open("C:\InputTemplate1.xls")
xlWorkSheet = xlWorkBook.Sheets("sheet1")
Dim strCommand As String = xlWorkSheet.Range("D1").Value.ToString()

This works fine.
but i wanna read the contents over the range D1 to G1
How can i do this?
Tarun.K.S 3-Dec-10 0:46am    
dim strCommand as string=xlWorkSheet.Range("D1:G1").Value.ToString()

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900