Hi There
I have been having a play around with the following code to display a
datagrid in Excel (all from Steve Orr's site):
Private Sub btnTechServAccr ed_Click(ByVal sender As System.Object, ByVal e
As System.EventArg s) Handles btnTechServAccr ed.Click
Dim AcredDT As DataTable = UserSession.Cur rent.AcredTable
Dim aExcel As New Microsoft.Offic e.Interop.Excel .Application
Dim aBooks As Microsoft.Offic e.Interop.Excel .Workbooks
Dim aBook As Microsoft.Offic e.Interop.Excel .Workbook
Dim aSheets As Microsoft.Offic e.Interop.Excel .Sheets
Dim aSheet3 As Microsoft.Offic e.Interop.Excel .Worksheet
Dim aSheet1 As Microsoft.Offic e.Interop.Excel .Worksheet
Dim aCells As Microsoft.Offic e.Interop.Excel .Range
Dim tFile As String, tTemplate As String
tFile = "C:\Accreditati ons\Excel.xls"
tTemplate = "C:\Accreditati ons\Template.xl s"
aExcel.Visible = False : aExcel.DisplayA lerts = False
aBooks = aExcel.Workbook s
aBooks.Open("C: \Accreditations \Template.xls")
aBook = aBooks.Item(1)
aSheets = aBook.Worksheet s
aSheet3 = CType(aSheets.I tem(3),
Microsoft.Offic e.Interop.Excel .Worksheet)
aSheet1 = CType(aSheets.I tem(1),
Microsoft.Offic e.Interop.Excel .Worksheet)
aSheet3.Name = "Raw Data"
aCells = aSheet3.Cells
DumpData(AcredD T, aCells)
aSheet1.SaveAs( tFile)
aBook.Close()
aExcel.Quit()
ReleaseComObjec t(aCells) : ReleaseComObjec t(aSheet3)
ReleaseComObjec t(aSheets) : ReleaseComObjec t(aBook)
ReleaseComObjec t(aBooks) : ReleaseComObjec t(aExcel)
aExcel = Nothing : aBooks = Nothing : aBook = Nothing
aSheets = Nothing : aSheet3 = Nothing : aCells = Nothing
System.GC.Colle ct()
Response.Redire ct(tFile)
End Sub
Private Function DumpData(ByVal _
dt As DataTable, ByVal oCells As Microsoft.Offic e.Interop.Excel .Range)
As String
Dim dr As DataRow, ary() As Object
Dim iRow As Integer, iCol As Integer
'Output Column Headers
For iCol = 0 To dt.Columns.Coun t - 1
oCells(2, iCol + 1) = dt.Columns(iCol ).ToString
Next
'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iR ow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToStr ing
Response.Write( ary(iCol).ToStr ing & vbTab)
Next
Next
End Function
This all works beautifully if I run the page from within Visual Studio 2003
- but as soon as I browse to the page using http://localhost/blah-de-blah it
fails at the point you would expect to see the message from the browser
asking if you want to open or save the file. Browser simply comes back with:
"The Page Cannot Be Displayed"
I get the impression that it is failing when it tries to overwrite the
"MyExcel" temporary document - and I have no doubt I am doing something daft
or have forgotten something obvious... any help would be appreciated.
I have set impersonation "Yes" and the IUSR account has permission to write
to the relevant excel files on the C: drive
Thanks
