button to export to excel

D

DC Gringo

I have a simple button that should open another window and export a datagrid
to an Excel file. I'm getting: "Name 'window' is not declared."

What do I need to declare or import?


<INPUT ID="Button5" ONCLICK="Button5_Click" NAME="Button5" TYPE="button"
VALUE="Export to Excel">

Sub Button5_Click()
Dim sHTML
sHTML = window.Form1.children("dgCommunities").outerhtml()
Dim oXL, oBook
oXL = CreateObject("Excel.Application")
oBook = oXL.Workbooks.Add
oBook.HTMLProject.HTMLProjectItems("Sheet1").Text = sHTML
oBook.HTMLProject.RefreshDocument()
oXL.Visible = True
oXL.UserControl = True
End Sub
 
D

DC Gringo

Steve,

This worked great with Excel installed. May I ask what needs to change when
using Microsoft Office Web Components?

_____
DC G
 
S

Steve C. Orr [MVP, MCSD]

Here are some examples using Office Web Components for Office 2003:

-http://msdn.microsoft.com/office/understanding/owc/default.aspx

And here are some examples using Office Web Components for earlier versions:

-http://msdn.microsoft.com/office/previous/components/default.aspx
 
D

DC Gringo

Steve,

Yes, I've seen that, but I'm looking for a bit more guidance than such a
huge reference.

Plain and simple, I've got Excel installed on my workstation and my app
works just fine. I upload it to our hosting provider and it doesn't.
They've installed OWC latest edition, but to no avail. Here's my code:

Public Sub btnCommunitiesExcel_OnClick(ByVal sender As System.Object, ByVal
e As System.EventArgs)

Dim oExcel As New Excel.Application
Dim oBooks As Excel.Workbooks, oBook As Excel.Workbook
Dim oSheets As Excel.Sheets, oSheet As Excel.Worksheet
Dim oCells As Excel.Range
Dim sFile As String, sTemplate As String
Dim ds As New DataSet
Dim da As New SqlDataAdapter(Session("savedCommunitiesSql"),
connection1.conString)
da.Fill(ds, "CommunitiesExcel")
Dim dt As DataTable = ds.Tables("CommunitiesExcel")

'Insert your code that runs under the security context of the authenticating
user here.
sFile = Server.MapPath(Request.ApplicationPath) & _
"\advanced\ExcelExports\Communities.xls"
sTemplate = Server.MapPath(Request.ApplicationPath) & _
"\advanced\ExcelExports\CommunitiesTemplate.xls"
oExcel.Visible = False : oExcel.DisplayAlerts = False

'Start a new workbook
oBooks = oExcel.Workbooks
oBooks.Open(Server.MapPath(Request.ApplicationPath) & _
"\advanced\ExcelExports\CommunitiesTemplate.xls")

'Load colorful template with chart
oBook = oBooks.Item(1)
oSheets = oBook.Worksheets
oSheet = CType(oSheets.Item(1), Excel.Worksheet)
oSheet.Name = "First Sheet"
oCells = oSheet.Cells
DumpData(dt, oCells) 'Fill in the data
oSheet.SaveAs(sFile) 'Save in a temporary file
oBook.Close()

'Quit Excel and thoroughly deallocate everything
oExcel.Quit()
ReleaseComObject(oCells) : ReleaseComObject(oSheet)
ReleaseComObject(oSheets) : ReleaseComObject(oBook)
ReleaseComObject(oBooks) : ReleaseComObject(oExcel)
oExcel = Nothing : oBooks = Nothing : oBook = Nothing
oSheets = Nothing : oSheet = Nothing : oCells = Nothing
System.GC.Collect()
Response.Redirect(sFile) 'Send the user to the file

End Sub



Private Function DumpData(ByVal _
dt As DataTable, ByVal oCells As 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.Count - 1
oCells(2, iCol + 1) = dt.Columns(iCol).ToString
Next

'Output Data
For iRow = 0 To dt.Rows.Count - 1
dr = dt.Rows.Item(iRow)
ary = dr.ItemArray
For iCol = 0 To UBound(ary)
oCells(iRow + 3, iCol + 1) = ary(iCol).ToString
Response.Write(ary(iCol).ToString & vbTab)
Next
Next
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,754
Messages
2,569,520
Members
44,996
Latest member
rainocode

Latest Threads

Top