ASP to Excel

[emzipoo4u]

Hi,

I have a table in an MSSQL database with the following layout

partnumber group description barcode date

I want a report to export the data into excel by date, but, I need each seperate product group on a seperate tab in excel.

I have the following code but its not working!

Any ideas?

<%
Response.ContentType = "application/vnd.ms-excel"
Response.AddHeader "Content-Disposition", "attachment;filename=file01.xls"
%>
<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<DownloadComponents/>
<LocationOfComponents HRef="file:///\\"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>12525</WindowHeight>
<WindowWidth>15195</WindowWidth>
<WindowTopX>480</WindowTopX>
<WindowTopY>120</WindowTopY>
<ActiveSheet>2</ActiveSheet>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>

<% 
		'NOW CONNECTS TO DISPLAY DATA FROM TABLE VINDEX
Dim adoCon             
Dim SQL
Dim Recordset2
Dim oddsevens

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "Provider=SQLOLEDB; Data Source=\SQLEXPRESS; Initial Catalog=; User ID=; Password="

SQLSTRING = "SELECT Partdevelopment.*, Supplierprice.*, Supplierprice.primesupplier FROM Partdevelopment LEFT OUTER JOIN Supplierprice ON Partdevelopment.partnumber = Supplierprice.partnumber WHERE (Supplierprice.primesupplier = N'Yes') ORDER BY productgroup, Partnumber"

SQL= (sqlstring)

Set recordset2 = adoCon.Execute(SQL)

If recordset2.eof then
	no_rows = True
          End If
		  
If no_rows Then

Else    

groupx = 0
  
do while not recordset2.eof 

if groupx <> recordset2.fields("productgroup") then%>
<Worksheet ss:Name="1">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1" x:FullRows="1">
<% End If %>

<Row>
<Cell><Data ss:Type="Text">1</Data></Cell>
</Row>

<Row>
<Cell><Data ss:Type="Text">2</Data></Cell>
</Row>

<%if groupx <> recordset2.fields("productgroup") then%>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<% end if %>

<% groupx = recordset2.fields("productgroup") 

response.flush
recordset2.MoveNext
Loop
End If%>

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>1</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>
<%adoCon.close
Set AdoCon = nothing
Set Recordset2 = nothing%>

[SouthwaterDave]

I think your connection string is wrong. It should be .\sqlexpress or machineName\sqlexpress.