Write to Excel from VB.NET web app

[jewly]

Hi everyone!!

I am using this code to write a new excel spreadsheet from VB.NET and populate it with values.However I keep getting the error "cannot create active x component."I am thinking it has something to do with security features but Im just wondering if anyone has any ideas!!



Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

MakeNewFile()

Dim sConn As String

sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\temp\NewExcel.xls; Extended Properties=Excel 8.0;"

Dim objConn As New System.Data.OleDb.OleDbConnection(sConn)

objConn.Open()

Dim objCmd As New System.Data.OleDb.OleDbCommand

objCmd.Connection = objConn

objCmd.CommandText = "Insert into [Sheet1$] (TitleOne, TitleTwo, TitleThree, TitleFour, TitleFive) values ('This', 'is', 'just', 'some', 'text')"

objCmd.ExecuteNonQuery()

objCmd.Dispose()

objConn.Close()

End Sub



Sub MakeNewFile()

Dim oExcel As Object

Dim oBook As Object

Dim oSheet As Object

oExcel = CreateObject("Excel.Application")

oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets(1)

oSheet.Range("A1").value = "TitleOne"

oSheet.Range("B1").value = "TitleTwo"

oSheet.Range("C1").value = "TitleThree"

oSheet.Range("D1").value = "TitleFour"

oSheet.Range("E1").value = "TitleFive"

oBook.SaveAs("c:\temp\NewExcel.xls")

oBook.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

oSheet = Nothing

oBook = Nothing

oExcel = Nothing

GC.Collect()

End Sub

Thank you!

Julie

[Asgorath]

Have you tried

Dim oExcel As new Excel.Application

instead of

oExcel = CreateObject("Excel.Application")

Regards
Jorge

[jewly]

Thank you for your reply!That is also giving me an error but within the code!

[Asgorath]

Try this ...

Sub MakeNewFile()
 
 
dim oExcel as new Excel.Application
Dim oBook As Workbook
Dim oSheet As Worksheet
 
oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)
 
oSheet.Range("A1").value = "TitleOne"
 
oSheet.Range("B1").value = "TitleTwo"
 
oSheet.Range("C1").value = "TitleThree"
 
oSheet.Range("D1").value = "TitleFour"
 
oSheet.Range("E1").value = "TitleFive"
 
oBook.SaveAs("c:\temp\NewExcel.xls")
 
oBook.Close()
 
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
 
oSheet = Nothing
 
oBook = Nothing
 
oExcel = Nothing
 
GC.Collect()
 
End Sub

[jewly]

Dim oExcel As New Excel.Application()


Thank you for your reply!This line is still giving me an error,however,the error is "new cannot be used on an interface"

Thank you for your help!

[Asgorath]

Hi
make sure your project references excel and office.

Regards
Jorge

[jewly]

ok,this is my code and that line is still giving me an error,i imported office 10.0 library,is that the correct one to import?thank you so much for your help!



Imports System
Imports Excel
Imports Office




Public Class WebForm3
Inherits System.Web.UI.Page
Protected WithEvents Button1 As System.Web.UI.WebControls.Button

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
MakeNewFile()

Dim sConn As String

sConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\temp\NewExcel.xls; Extended Properties=Excel 8.0;"

Dim objConn As New System.Data.OleDb.OleDbConnection(sConn)

objConn.Open()

Dim objCmd As New System.Data.OleDb.OleDbCommand()

objCmd.Connection = objConn

objCmd.CommandText = "Insert into [Sheet1$] (TitleOne, TitleTwo, TitleThree, TitleFour, TitleFive) values ('This', 'is', 'just', 'some', 'text')"

objCmd.ExecuteNonQuery()

objCmd.Dispose()

objConn.Close()

End Sub
Sub MakeNewFile()


Dim oExcel As New Excel.Application()
Dim oBook As Workbook
Dim oSheet As Worksheet

oBook = oExcel.Workbooks.Add
oSheet = oBook.Worksheets(1)

oSheet.Range("A1").value = "TitleOne"

oSheet.Range("B1").value = "TitleTwo"

oSheet.Range("C1").value = "TitleThree"

oSheet.Range("D1").value = "TitleFour"

oSheet.Range("E1").value = "TitleFive"

oBook.SaveAs("c:\temp\NewExcel.xls")

oBook.Close()

System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)

oSheet = Nothing

oBook = Nothing

oExcel = Nothing

GC.Collect()

End Sub





End Class

[jwocky]

i do this all the time, accounting people want all of their stuff in excel.
[Highlight=VB]
Imports Microsoft.office.Interop

Dim xl As New Excel.Application
Dim w As New Excel.Worksheet

w = xl.Workbooks.Add.Worksheets.Add
xl.Visible = True

with w
.cells(1,1) = "Add your data here"
end w
[\vbcode]

oh and don't forget to Project > add reference > com > your version of excel

[jewly]

which office do you import to get .interop?Thank you for your help!

[jwocky]

right now i use Project > add reference > com > Microsoft Excel 11.0 object library. thats with office 2003. when i used office xp i think it was microsoft excel 10, and so on

[jewly]

ok i have imported that but it still wont let me declare Imports Microsoft.office.Interop,do you use that code for web based applications?

[Asgorath]

Hi Julie
I tried this sample code on a web project and its working, Added the following refrences : Microsoft Excel 10.0 Object Library and Microsoft Office 10.0 Object Library

Option Explicit On 
Option Strict On
 
Public Class WebForm1
    Inherits System.Web.UI.Page
 
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
    Protected WithEvents Button1 As System.Web.UI.WebControls.Button
 
    'NOTE: The following placeholder declaration is required by the Web Form Designer.
    'Do not delete or move it.
    Private designerPlaceholderDeclaration As System.Object
 
    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
 
#End Region
 
    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Put user code to initialize the page here
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
 
        Dim oExcel As New Excel.Application
        Dim oBook As Excel.Workbook = oExcel.Workbooks.Add
        Dim oSheet As Excel.Worksheet = DirectCast(oExcel.ActiveSheet, Excel.Worksheet)
 
 
        oSheet.Range("A1").Value = "TitleOne"
 
        oSheet.Range("B1").Value = "TitleTwo"
 
        oSheet.Range("C1").Value = "TitleThree"
 
        oSheet.Range("D1").Value = "TitleFour"
 
        oSheet.Range("E1").Value = "TitleFive"
 
        oBook.SaveAs("c:\NewExcel.xls")
 
        oBook.Close()
 
        oExcel.Quit()
        System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
 
        oSheet = Nothing
 
        oBook = Nothing
 
        oExcel = Nothing
 
        GC.Collect()
 
    End Sub
End Class

[jewly]

Hi!thank you for your continued help!!i have imported everything and used your code but it is still giving me an error with this line

Dim oExcel As New Excel.Application() - saying; new cannot be used on an interface

I really dont understand this as the code worked perfectly for you!

[Asgorath]

I don't know what else to suggest, i have uploaded the project to my homepage try it on your PC.

http://justanotherhomepage.net/WebApplication4.zip

Regards
Jorge

[jewly]

Thank you once again for your very kind help!......alas the plot thickens!I imported your interop.excel dll from winzip and that error disappeared....now i am getting this error!

COM object with CLSID {00024500-0000-0000-C000-000000000046} is either not valid or not registered.

This link seems to explain my problem - i am running office 2002,however and i have Excel and Office 10.0 in my references but these dont seem to work with that code!

http://www.error-bank.com/microsoft....11_Thread.aspx

[Asgorath]

I am using office XP here at work try using Excel 9.0 and office 9.0.

Regards
Jorghe

[manishk]

Hi jewly

Here is the code which i used for writing in the EXCEL sheet !
Hope this will help u !
and still if this does't help u , i will send u the whold source code !
just let me know !

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
'Create an array with 3 columns and 100 rows.
Dim DataArray(obj.WorkDs.Tables("One").Rows.Count - 1, obj.WorkDs.Tables("One").Columns.Count - 1) As Object
Dim r, c As Integer
For r = 0 To obj.WorkDs.Tables("One").Rows.Count - 1
For c = 0 To obj.WorkDs.Tables("One").Columns.Count - 1
DataArray(r, c) = obj.WorkDs.Tables("One").Rows(r)(c)
Next
Next
'Add headers to the worksheet on row 1.
oSheet = oBook.Worksheets(1)
For r = 0 To obj.WorkDs.Tables("One").Columns.Count - 1
oSheet.Range(Chr(r + 65) & "1").Value = obj.WorkDs.Tables("One").Columns(r).ToString
Next
'Transfer the array to the worksheet starting at cell A2.
oSheet.Range("A2").Resize(obj.WorkDs.Tables("One").Rows.Count, obj.WorkDs.Tables("One").Columns.Count).Value = DataArray
'Save the Workbook and quit Excel.
oBook.Saveas("c:\manish.xls")
MsgBox("This list is successfully saved in c:\manish.xls")
oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
DataArray = Nothing
GC.Collect()


* Here "One" is the name of the table

[jewly]

Hi,

Thank you for your continued help!!!I tried this on another computer but now Im getting an access is denied error. I tried giving full access to the asp user account within Excel but it still will not work and Im still getting the access is denied error. Would anyone have any other ideas on how to give Excel write access to VB.NET.

Thank you

Julie

[manishk]

Hi Julie

if u don;t mind, tell me your email di i will zip and send you the whole source code ! or is there any way to do the same in the forum only, so that i can send u the zip file !!!

[manishk]

Hi Julie

I had sent u the code as attachement on your email id !
Here its not possible to attach !

[bm-g]

I am getting exactly the same problem. My situation differs slightly as my project was working fine on my old laptop and now on a new one it fails.

Access Denied at objExcel = New Excel.Application

Did anyone find the final solution as I have been on this problem for 3 days now and there seems to be no solution offered anywhere .........