The method above works and here's an alternative too that goes after the datatable in question
If Not IsNothing(hypExcelIcon.CommandName) Then
'Lets create a datatable to hold the values and then push it out to the user.
Dim dtExcel As New DataTable()
dtExcel.Columns.Add(New DataColumn("Product Family", GetType(String)))
dtExcel.Columns.Add(New DataColumn("PartID", GetType(String)))
dtExcel.Columns.Add(New DataColumn("Description", GetType(String)))
dtExcel.Columns.Add(New DataColumn("Is Pronto Part", GetType(String)))
dtExcel.Columns.Add(New DataColumn("List Price", GetType(String)))
dtExcel.Columns.Add(New DataColumn("Discount", GetType(String)))
dtExcel.Columns.Add(New DataColumn("Your Price", GetType(String)))
'Now lets loop thru the table and get the rows.
For Each drFilterRow As DataRow In drFilterRows
Dim dtTable As DataTable = GETXXXXXX(drFilterRow("COMMODITYID"))
'Now we loop thru the series.
For Each drRow As DataRow In dtTable.Rows
Dim drExcel As DataRow = dtExcel.NewRow()
drExcel("Product Family") = drFilterRow("PRICEBOOK_NAME").ToString()
drExcel("PartID") = drRow("PARTNAME").ToString()
drExcel("Description") = drRow("DESCRIPTION").ToString()
drExcel("Is Pronto Part") = IIf(String.IsNullOrEmpty(drRow("PRONTO_PART").ToString()), "No", "Yes")
drExcel("Discount") = formatPercent(_discountCode / 100)
dtExcel.Rows.Add(drExcel)
Next
Next
'Now lets push this to the client.
Response.Clear()
Response.AddHeader("Content-Disposition", "attachment;filename=" & fileName & ".xls")
Response.Charset() = ""
Response.ContentType = "application/vnd.ms-excel"
Dim tab As String = ""
For Each dc As DataColumn In dtExcel.Columns
Response.Write(tab & dc.ColumnName)
tab = vbTab
Next
Response.Write(vbCr)
'write the rows.
Dim i As Integer = 0
For Each datarow As DataRow In dtExcel.Rows
tab = ""
For i = 0 To dtExcel.Columns.Count - 1
Response.Write(tab & datarow(i).ToString())
tab = vbTab
Next
Response.Write(vbCr)
Next
Response.End()
End If