Access MDB data into OWC Spreadsheet - HELP?

[BarryMVS]

Hi,

I am doing this using ASP.

I have an Access database and I need to do an SQL search on this database and then transfer the data into an Excel spreadsheet.

I have got OWC11 installed and have worked out how to create a spreadsheet.

The problem I have now is getting the data from the SQL look up into the spreadsheet.

Does anyone have any idea how to do this?

I know you can import data into the OWC spreadsheet object using XML, but I don't know how to 'save' or point the SQL lookup data to the XML feed.

Below is my asp page:

<% Language=VBScript %>
<!--#include virtual="dbcon.asp"-->
<%
    Response.Buffer = True
    Response.ContentType = "application/vnd.ms-excel"

	dim filename, startdate, enddate, groupid
	filename = "test"
	startdate = "01/09/2004"
	enddate = "05/11/2004"
	groupid = "22"

	Dim conn
    Dim oSS
    Dim oOrdersSheet
    Dim oRange
    Dim c

	set conn = Server.CreateObject("ADODB.Connection")
	conn.Open DBCon

    Set oSS = CreateObject("OWC11.Spreadsheet")
    Set c = oSS.Constants

    'Rename Sheet1 and remove Sheet2 & Sheet3
    Set oOrdersSheet = oSS.Worksheets(1)
    oOrdersSheet.Name = "CR"
	oSS.Worksheets(2).Delete
    'oSS.Worksheets(3).Delete

    '=== Build the Worksheet ==============================================

  
    Dim Data
	Dim StrSql, rsExport
	StrSql = "SELECT Customers.[Their Branch Ref], Customers.[Address Line 3], [Service Records].[Date Call Placed], [Service Records].[Problem Description], [Service Records].[Service Record ID], Employees.Initials, [Service Records].[Last Action], Customers.[Last Site Visit], [Status Codes].Description AS Status FROM ((Customers RIGHT JOIN [Service Records] ON Customers.[Customer ID] = [Service Records].[Customer ID]) LEFT JOIN Employees ON [Service Records].[Employee ID] = Employees.[Employee ID]) LEFT JOIN [Status Codes] ON [Service Records].Status = [Status Codes].Status WHERE ((([Service Records].[Date Call Placed])>= #" & startdate & "# And ([Service Records].[Date Call Placed])<= #" & enddate &"#) AND ((Customers.Group)=" & groupid &")) ORDER BY [Service Records].[Service Record ID];" ' copy SQL from SAMS mdb for report look up.
    Set rsExport = conn.Execute (StrSql) 
	
	
	Do While Not rsExport.EOF
	rs.Export.Fields("Their Branch Ref").Value	
	rsExport.MoveNext
	Loop

    'Apply window settings for the Orders worksheet
    oOrdersSheet.Activate   'Makes the Orders sheet active
    oSS.Windows(1).ViewableRange = oOrdersSheet.UsedRange.Address
    oSS.Windows(1).DisplayRowHeadings = False
    oSS.Windows(1).DisplayColumnHeadings = False
    oSS.Windows(1).FreezePanes = True
    oSS.Windows(1).DisplayGridlines = False

    '=== Setup for final presentation ==================================================

    oSS.DisplayToolbar = False
    oSS.AutoFit = True
    oOrdersSheet.Activate

    Response.Write oSS.XMLData
    Response.End

%>

Any advise or ideas are most welcomed.

Thanks,


Barry

ICT Network Administrator
IT Services Manager