ASP and Excel

[Anthony1312002]

I found this scipt that uses excel to create a chart. I added a db connection and an sql statement to the form above it. The connection and the sql look like this:


Connection and SQL

'--------------- DB CONNECTION ------------------------------
'-- Declare your variables
Dim Cnt, cmdDC, RSData, RecordSet, SecondMessage, reportType, strSQL
Set Cnt = Server.CreateObject ("ADODB.Connection")
Cnt.Open "ASPPages"
'-- Create object and open database
Set cmdDC = Server.CreateObject("ADODB.Command")
cmdDC.ActiveConnection = Cnt
'-----------------------------Run this SQL Statement for the Daily / MTD Volume Report--------------------------------------------

strSQL = "SELECT WeekDay, SUM(Attempts) AS RAttempts FROM qryDataTableNew " _
& "WHERE VolDate >= #" & Starting & "# and " _
& "VolDate <= #" & Ending & "# " _
& "GROUP BY WeekDay ORDER BY WeekDay"

Dim objRS, count, color

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, Cnt

How can I have the excel chart access the sql statement instead of the it's original source data?

Excel portion
<%
For i = 1 to 12 %>
<TR>
<TD ALIGN="LEFT" VALIGN="TOP">Date: <%= FormatDateTime(i & "/01/99", 2)%></TD>
<TD ALIGN="LEFT" VALIGN="TOP">$ :<INPUT TYPE="text" NAME="v<%= i%>" VALUE="<% If mintPass=1 Then Response.Write Request("v" & i) Else Response.Write Int((100)*Rnd+1) End If %>" SIZE="10" MAXLENGTH="5"></TD>
</TR>
<%
Next %>

</TABLE>
<INPUT TYPE="submit" NAME="cmd" VALUE=" Generate Chart">
</FORM>
</BODY>
</HTML>
<%
Sub HandleRepeatVisit()
Dim xlapp ' Our Excel App
Dim wb ' Our Workbook within the Excel App
Dim ws ' Our Worksheet within the Workbook
Dim crt ' The chart object
Dim SourceRange ' The Source Range for the chart object

Const xlWorkSheet = -4167
Const xlLineMarkers = 65

' -- Create an instance of Excel Application
Set xlapp = Server.CreateObject("Excel.Application")
' -- Create a new workbook
Set wb = xlapp.Workbooks.Add(xlWorksheet)
' -- Grab the first worksheet of the new workbook
Set ws = wb.Worksheets(1)
' -- Insert the data the user requested
' -- First, the title
ws.Range("A1").Value = Request("co") ' -- defaults to "Microsoft"
' -- Then the data in two vertical columns
For i = 1 To 12
ws.Range("A" & i + 1).Value = FormatDateTime(i & "/01/99", 2)
ws.Range("B" & i + 1).Formula = "=" & Request("v" & i)
Next

' -- Set our source range
Set SourceRange = ws.Range("A2:B13")
' -- Create a new Chart Object
Set crt = ws.ChartObjects.Add(20, 20, 300, 200)
' -- Generate the Chart using the ChartWizard
' -- Syntax is:
' -- crt.Chart.ChartWizard Source:=SourceRange, gallery:=xlLine(4), PlotBy:=xlColumns(default), _
' -- categorylabels:=1, serieslabels:=0, HasLegend:=2, Title:="Company Stock Value"

crt.Chart.ChartWizard SourceRange, 4, , 2, 1, 0, 2, Request("co") & " Stock Value"
' -- Configure the Chart
crt.Chart.ChartType = xlLineMarkers
crt.Chart.SeriesCollection(1).Name = "=Sheet1!R1C1"
crt.Chart.HasTitle = True
crt.Chart.Axes(1, 1).HasTitle = True
crt.Chart.Axes(1, 1).AxisTitle.Characters.Text = "Months"
crt.Chart.Axes(2, 1).HasTitle = True
crt.Chart.Axes(2, 1).AxisTitle.Characters.Text = "Stock Price"

' -- Determine the name to save this chart as. Use the current Seconds value, overwriting previous
' -- ones
mstrFileName = "junk" & Second(Now()) & ".jpg"
' -- Save the chart on web server
crt.Chart.Export Server.Mappath("/asppro/junk/") & "\" & mstrFileName, "jpg"
' -- Fool Excel into thinking the Workbook is saved
wb.Saved = True
' -- Set all objects back to nothing
Set crt = Nothing
Set wb = Nothing
' -- Quit Excel to conserve resources
xlapp.Quit
Set xlapp = Nothing

' -- Make sure the Image is not cached but is loaded fresh from the web server
Response.AddHeader "expires","0"
Response.AddHeader "pragma", "no-cache"
Response.AddHeader "cache-control","no-cache"

End Sub
%>

<%
'Clean up our ADO objects

objRS.Close
Set objRS = Nothing

Cnt.Close
Set Cnt = nothing
%>