Excel integration with the ASP.net over VB
 

hi,
I am desiging ASP.net website , in one of the forms I need to import data to an excel work book with a specific name but I need to check first is that work bokk already exist so not recreate but only add a sheet to it . here is segment of the code to chekc:

If FileExists(path & doc_ID) Then
MsgBox("already exists ", MsgBoxStyle.MsgBoxSetForeground)
Else
'Start a new workbook in Excel.
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add
calculation()

oBook.SaveAs(path & doc_ID)

'oSheet = Nothing
oBook = Nothing
oExcel.Quit()
oExcel = Nothing
GC.Collect()
MsgBox("Done")
Response.Redirect("calculateResults.aspx")

End If


the method FileExists is as follows :

Private Function FileExists(ByVal FileFullPath As String) _
As Boolean
'If Trim(FileFullPath) = "" Then Return False
Dim f As New IO.FileInfo(FileFullPath)
Return f.Exists()

End Function

note the does not contain the operation of adding a sheet but only display msg,
The problem is even if a have a file with the same exact name it never shows the msg box

any body can help, I'll appreciate it

Re: Excel integration with the ASP.net over VB
 

Hi,

Regarding MsgBox: MsgBox displays a message on the screen; however, the code is on the server, so the message box would show on the server, which is ignored by it so that the it wouldn't be locked down until a user sat down in front of the server and clicked "OK" on the message box.

I would say a better approach would be to make an OleDb connection to the Excel file, from which you can insert sheets, etc. if the Excel file exists.

Use your code for checking if a file exists; if the file exists, connect to the file using an OleDb connection (reference www.connectionStrings.com for help with constructing an appropriate connection string). Here is example code I wrote which worked, adding a sheet named "test" to the file, test.xsl:

Dim strExcelFile As String = Server.MapPath("~/test.xls") 

Dim strExcelFolder As String = strExcelFile.Substring(0, strExcelFile.LastIndexOf("\")) 



Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFile + ";Extended Properties=Excel 8.0" 



Dim con As New OleDbConnection(connectionString) 

con.Open() 



Dim com As New OleDbCommand("CREATE TABLE [test] ( A INT, B INT )", con) 

com.ExecuteNonQuery() 



con.Close()

Also, make sure that the Excel file is not Read-Only, and that the IUSR_<machine_name> user has read/write access to the file.

~ mellamokb