export data from .ASP page to Excel sheet using VB script

Original message moved by TNO
Reason :
Hi All,
 
Here i am new to VB Script.I want to pull data form .ASP page to Excel sheet using VBScript. any one could ge me some info on this.
 
Thank You,
 
Vaddiparthi

1. Open up an Excel  spreadsheet on your computer.

2. format it the way you want it to look

3. Save the file as an xml spreadsheet

4. now build your vbscript to generate that sample file format

5. once your xml file is built , set the Response of your asp page to this (where XML is the XML string you just built):

 With Response
     .Charset = "UTF-8"
     .Clear
     .ContentType = "excel/ms-excel"
     .AddHeader "Content-Disposition","attachment; filename=export.xls"
     .AddHeader "Content-Length", Len(XML)
     .Write XML
     .Flush
     .End
 End With
 

Hi,
Sorry, I am a beginner too.
I have done upto the 3rd step.
Now, could you please show me a sample vbcript code for this?
And where should I place the xml file?

thanks,

You just build a large string to copy the format of the file you just made. Build the rows and cells in a loop as you append it to the string.

You don't have to place the XML file anywhere, you can do it from the string variable you created.

Thanks for your reply.
The table part of my code is similar to this
<table>
<%For counter = StartCount To endc%>
<tr><td><%=ItemReg%></td>
        <td><%=ItemTitle%></td>
         <td><%=ItemReference%></td>
</tr>
<%next%>
</table>

Sorry, I am a beginner, trying to export the results from a search.
How do I build a large string from that xml file?
Could you please show me some examples?

thanks a milliion

I thought you worked up to the third step? You aren't even building the right table.

Here's code from what I've done before:

 <%
 Function ISODate()
   Dim y: y = Year(Now)
   Dim m: m = Month(Now)
   Dim d: d = Day(Now)
   Dim h: h = Hour(Now)
   Dim n: n = Minute(Now)
   Dim s: s = Second(Now)
 
   If m < 10 Then m="0" & m
   If d < 10 Then d="0" & d
   If h < 10 then h="0" & h
   If n < 10 Then n="0" & n
   If s < 10 Then s="0" & s
 
   ISODate = y & "-" & m & "-" & d & "T" & h & ":" & n & ":" & s & "Z"
 End Function
 
 
 Dim iid : iid = Request("iid")
 
 If Len(iid) > 10 Then
   Response.End
 End If
 
 Dim strSQL : strSQL = "SELECT tblFormResults.* FROM tblFormResults " & _
                         "WHERE FormID = "& CInt(iid) & _
                         " Order by tblFormResults.RespondedOn"
 
 Dim XML : XML = "<?xml version='1.0'?>" & _
               "<?mso-application progid='Excel.Sheet'?>" & _
               "<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'>" & _
               " <DocumentProperties xmlns='urn:schemas-microsoft-com:office:office'>" & _
               "    <Author>Some Dude</Author>" & _
               "    <LastAuthor>Some Guy</LastAuthor>" & _
               "    <Created>" & ISODate & "</Created>" & _
               "    <Company>Some Place</Company>" & _
               "    <Version>12.00</Version>" & _
               "</DocumentProperties>" & _
               "<OfficeDocumentSettings xmlns='urn:schemas-microsoft-com:office:office'>" & _
               "    <DownloadComponents/>" & _
               "</OfficeDocumentSettings>" & _
               "<ExcelWorkbook xmlns='urn:schemas-microsoft-com:office:excel'>" & _
               "    <WindowHeight>10425</WindowHeight>" & _
               "    <WindowWidth>18015</WindowWidth>" & _
               "    <WindowTopX>240</WindowTopX>" & _
               "    <WindowTopY>60</WindowTopY>" & _
               "    <ProtectStructure>False</ProtectStructure>" & _
               "    <ProtectWindows>False</ProtectWindows>" & _
               "</ExcelWorkbook>" & _
               "<Styles>" & _
               "    <Style ss:ID='Default' ss:Name='Normal'>" & _
               "        <Alignment ss:Vertical='Bottom'/>" & _
               "        <Borders/>" & _
               "        <Font ss:FontName='Calibri' x:Family='Swiss' ss:Size='11' ss:Color='#000000'/>" & _
               "        <Interior/>" & _
               "        <NumberFormat/>" & _
               "        <Protection/>" & _
               "    </Style>" & _
               "</Styles>" & _
               "<Worksheet ss:Name='Sheet1'>" & _
               "    <Table x:FullColumns='1' " & _
               "      x:FullRows='1' ss:DefaultRowHeight='15'>"
 
 Dim Result : Set Result = Server.CreateObject("ADODB.Recordset") 
 Result.Open strSQL, obj_conn,3,3
 Dim AnswerRS
 
 If Not Result.EOF Then
   'header row
   XML = XML & "<Row>" & _
                   "<Cell><Data ss:Type='String'>First Name</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Last Name</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Address</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Address 2</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>City</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>State</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Zip</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Country</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Phone</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Fax</Data></Cell>" & _
                   "<Cell><Data ss:Type='String'>Email</Data></Cell>"
                   
   'Check for custom fields
   strSQL = "SELECT * FROM tblFormElement WHERE FormID = " & iid & " ORDER BY SortORder, FormElementID"
   Dim ElementRS : Set ElementRS = CreateObject("ADODB.Recordset")
   ElementRS.open strSQL, obj_conn, 3,3
 
   If Not ElementRS.EOF Then
       While Not ElementRS.EOF
           XML = XML & "<Cell><Data ss:Type='String'>" & ElementRS("Caption") & "</Data></Cell>"
           ElementRS.MoveNext
       Wend
   End If
 
   XML = XML & "</Row>"
   
   ElementRS.Close
   Set elementRS = Nothing
   
   Do While Not (Result.Eof)
       XML = XML & "<Row>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdFName") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdLName") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdAddress1") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdAddress2") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdCity") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdState") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdZip") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdCountry") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdPhone") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdFax") & "</Data></Cell>" & _
                       "<Cell><Data ss:Type='String'>" & Result("StdEmail") & "</Data></Cell>"
 
       'Check for custom fields
       strSQL = "SELECT * FROM tblFormElement WHERE FormID = " & iid & " ORDER BY SortORder, FormElementID"
       Set ElementRS = CreateObject("ADODB.Recordset")
       ElementRS.open strSQL, obj_conn, 3,3
 
       If Not ElementRS.EOF Then
           Do While Not ElementRS.EOF
               strSQL = "SELECT * FROM tblFormAnswer WHERE ResultsID = " & Result("FormResultsID") & " AND ElementID = " & ElementRS("FormElementID")
               Set AnswerRS = CreateObject("ADODB.Recordset")
               AnswerRS.open strSQL, obj_conn, 3,3
 
               If Not AnswerRS.eof Then
                   XML = XML & "<Cell><Data ss:Type='String'>" & AnswerRS("AnswerValue") & "</Data></Cell>"
               Else
                   XML = XML & "<Cell><Data ss:Type='String'></Data></Cell>"
               End If
 
               ElementRS.MoveNext
           Loop
       End If
       ElementRS.Close
       Set elementRS = Nothing
 
       XML = XML & "</Row>"
 
       Result.MoveNext
   Loop
 End If
 
 closeDbConnection()
 Set Result = Nothing
 
 XML = XML & "</Table>" & _
           "<WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>" & _
           "    <PageSetup>" & _
           "        <Header x:Margin='0.3'/>" & _
           "        <Footer x:Margin='0.3'/>" & _
           "        <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>" & _
           "    </PageSetup>" & _
           "    <Selected/>" & _
           "    <Panes>" & _
           "        <Pane>" & _
           "           <Number>3</Number>" & _
           "           <ActiveRow>16</ActiveRow>" & _
           "           <ActiveCol>8</ActiveCol>" & _
           "       </Pane>" & _
           "    </Panes>" & _
           "    <ProtectObjects>False</ProtectObjects>" & _
           "    <ProtectScenarios>False</ProtectScenarios>" & _
           "</WorksheetOptions>" & _
           "</Worksheet>" & _
           "<Worksheet ss:Name='Sheet2'>" & _
           "<Table ss:ExpandedColumnCount='1' ss:ExpandedRowCount='1' x:FullColumns='1'" & _
           "   x:FullRows='1' ss:DefaultRowHeight='15'>" & _
           "</Table>" & _
           "<WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>" & _
           "   <PageSetup>" & _
           "       <Header x:Margin='0.3'/>" & _
           "       <Footer x:Margin='0.3'/>" & _
           "       <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>" & _
           "    </PageSetup>" & _
           "   <ProtectObjects>False</ProtectObjects>" & _
           "   <ProtectScenarios>False</ProtectScenarios>" & _
           "  </WorksheetOptions>" & _
           " </Worksheet>" & _
           " <Worksheet ss:Name='Sheet3'>" & _
           "  <Table ss:ExpandedColumnCount='1' ss:ExpandedRowCount='1' x:FullColumns='1'" & _
           "   x:FullRows='1' ss:DefaultRowHeight='15'>" & _
           "  </Table>" & _
           "  <WorksheetOptions xmlns='urn:schemas-microsoft-com:office:excel'>" & _
           "   <PageSetup>" & _
           "    <Header x:Margin='0.3'/>" & _
           "    <Footer x:Margin='0.3'/>" & _
           "    <PageMargins x:Bottom='0.75' x:Left='0.7' x:Right='0.7' x:Top='0.75'/>" & _
           "   </PageSetup>" & _
           "   <ProtectObjects>False</ProtectObjects>" & _
           "   <ProtectScenarios>False</ProtectScenarios>" & _
           "  </WorksheetOptions>" & _
           " </Worksheet>" & _
           "</Workbook>"
 
 With Response
   .Charset = "UTF-8"
   .Clear
   .ContentType = "excel/ms-excel"
   .AddHeader "Content-Disposition","attachment; filename=export.xls"
   .AddHeader "Content-Length", Len(XML)
   .Write XML
   .Flush
   .End
 End With
 %>