Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Cool Unanswered: Getting Recordset Completely in to Excel

    Hi all,

    Is it possible to write all recordset record into an excel sheet ?
    I dont want to write it Record by Record with Do While rs.eof..Loop statement because of the long duration while reading record.

    Thanks in advance.

    Mehmet

  2. #2
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    There is a command which allows you to specify the format of each row and export a complete recordset as one block. However, it's late and I can't remeber it at this time of night - email, or reply here and I will find it for you in the next couple of days.

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    I will be glad if you post the method that you talked about.

    Thanks in advance.
    Regards

    Mehmet

    Originally posted by rhs98
    There is a command which allows you to specify the format of each row and export a complete recordset as one block. However, it's late and I can't remeber it at this time of night - email, or reply here and I will find it for you in the next couple of days.

  4. #4
    Join Date
    May 2003
    Posts
    23
    one way is to output the results to a table. then give that table an id. then use client side script to port that table into excel. i read it in one of the tech articles a few weeks back.

    i modified the oringinal scripts to my own needs. Below are my modified version.

    Code:
    <script language="vbscript">
    
    '***************************************************************************************************************
    'This script generates an excel worksheet based on the result shown on the client side. It requires the client '
    'to have MS EXCEL installed and ActiveX scripting enabled on the client browser.                               '
    '"MyTable" refers to the table ID                                                                              '          
    '***************************************************************************************************************
    
    Sub ExportToExcel
    On Error Resume Next 'An error will be thrown if ActiveX scripting is disabled.
    		Dim objExcel
    		Set objExcel = CreateObject("Excel.Application")'Creates the object
    		If Len(Err.Description) > 0 Then
    			Dim strErrMsg
    			strErrMsg = strErrmsg & "An Error has occurred in exporting" & vbCrlF
    			strErrMsg = strErrMsg & "Error Description: " & Err.Description & vbCrlf
    			strErrMsg = strErrMsg & "Error Number: " & Err.number & vbCRLF
    			strErrMsg = strErrMsg & "Please contact System Administrator"
    			Msgbox strErrMsg,vbInformation 'contact system admin to enable ActiveX scripting so u can use the feature
    			Exit Sub
    		End If
    		objExcel.Visible = True
    		Set objWB = objExcel.Workbooks.Add()
    		Set objWS = objWB.Worksheets.Add()
    		objWS.Columns(1).ColumnWidth = 25
    		objWS.Columns(2).ColumnWidth = 15
    		Dim MyRow, MyCell
    		RowCount = 0 
    		For Each MyRow in MyTable.rows
    		i=i+1
    			If i Mod 2 = 0 Then 'Alternate rowcolor..white
    				RowCount = RowCount + 1 
    				ColCount = 0
    				For Each MyCell In MyRow.Cells
    					ColCount = ColCount + 1
    					If Trim(MyCell.InnerText) = "" Then
    						MyValue = ""
    					Else
    						MyValue = MyCell.InnerText
    					End If
    					objWS.Cells(RowCount,ColCount) = MyValue
    					If RowCount = 1 Then
    						objWS.Cells(RowCount,ColCount).Font.Bold = True
    						objWS.Cells(RowCount,ColCount).HorizontalAlignment = 1
    						objWS.Cells(RowCount,ColCount).Interior.Color = RGB(192,192,192)			
    					End If
    				Next
    			Else'Alternate rowcolor..grey
    				RowCount = RowCount + 1 
    				ColCount = 0
    				objExcel.rows(rowCount).Interior.Color = RGB(192,192,192)
    				For Each MyCell In MyRow.Cells
    					ColCount = ColCount + 1
    					If Trim(MyCell.InnerText) = "" Then
    						MyValue = ""
    					Else
    						MyValue = MyCell.InnerText
    					End If
    					objWS.Cells(RowCount,ColCount) = Trim(MyValue)
    					If RowCount = 1 Then
    						objWS.Cells(RowCount,ColCount).Font.Bold = True
    						objWS.Cells(RowCount,ColCount).HorizontalAlignment = 1
    						objWS.Cells(RowCount,ColCount).Interior.Color = RGB(192,192,192)			
    					End If
    				Next
    			End If
    		Next
    		objExcel.Cells.Select
    		objExcel.Cells.Font.Name = "Arial"
    		objExcel.Cells.Font.Size = 8
    		objWS.Range("A1",Chr(ColCount + 64) & RowCount).Columns.Autofit
    		objExcel.Selection.HorizontalAlignment = &hFFFFEFDD 'Align Left
    		Set objWB = Nothing		'Cleaning up
    		Set objWS = Nothing		'Cleaning up
    		Set objExcel = Nothing  'Cleaning up
    End Sub
    </script>

  5. #5
    Join Date
    Jan 2004
    Location
    Oslo
    Posts
    45
    Hi Fused...

    Hope you can read this post..

    What i wonder is if the code u've posted requires Excel to be installed on the server ? And.. will it produce much traffic if many people is gonna use this ?

    I got this code which i'm using to display all content to html table..
    How can i use my code included in the one you posted ?
    (PS!.. How can i use a Array for the same thing?)

    --------------------CODE------------------------
    For intRad = 0 to intSisteRad
    Response.Write "<tr class ='litentekst' bgcolor='#F5F5F5'>"
    For intKolonne = 0 to intSisteKolonne
    Response.write "<td height='35'>" & varArray(intKolonne,intRad) & "</td>"
    next
    Response.write "<td><center><input type='text' name='txtMerknad' size='40' border='0'></center></td>"
    Response.Write "</tr>"
    Next
    ---------------------END-------------------------

    Originally posted by fused
    one way is to output the results to a table. then give that table an id. then use client side script to port that table into excel. i read it in one of the tech articles a few weeks back.

    i modified the oringinal scripts to my own needs. Below are my modified version.

    Code:
    <script language="vbscript">
    
    '***************************************************************************************************************
    'This script generates an excel worksheet based on the result shown on the client side. It requires the client '
    'to have MS EXCEL installed and ActiveX scripting enabled on the client browser.                               '
    '"MyTable" refers to the table ID                                                                              '          
    '***************************************************************************************************************
    
    Sub ExportToExcel
    On Error Resume Next 'An error will be thrown if ActiveX scripting is disabled.
    		Dim objExcel
    		Set objExcel = CreateObject("Excel.Application")'Creates the object
    		If Len(Err.Description) > 0 Then
    			Dim strErrMsg
    			strErrMsg = strErrmsg & "An Error has occurred in exporting" & vbCrlF
    			strErrMsg = strErrMsg & "Error Description: " & Err.Description & vbCrlf
    			strErrMsg = strErrMsg & "Error Number: " & Err.number & vbCRLF
    			strErrMsg = strErrMsg & "Please contact System Administrator"
    			Msgbox strErrMsg,vbInformation 'contact system admin to enable ActiveX scripting so u can use the feature
    			Exit Sub
    		End If
    		objExcel.Visible = True
    		Set objWB = objExcel.Workbooks.Add()
    		Set objWS = objWB.Worksheets.Add()
    		objWS.Columns(1).ColumnWidth = 25
    		objWS.Columns(2).ColumnWidth = 15
    		Dim MyRow, MyCell
    		RowCount = 0 
    		For Each MyRow in MyTable.rows
    		i=i+1
    			If i Mod 2 = 0 Then 'Alternate rowcolor..white
    				RowCount = RowCount + 1 
    				ColCount = 0
    				For Each MyCell In MyRow.Cells
    					ColCount = ColCount + 1
    					If Trim(MyCell.InnerText) = "" Then
    						MyValue = ""
    					Else
    						MyValue = MyCell.InnerText
    					End If
    					objWS.Cells(RowCount,ColCount) = MyValue
    					If RowCount = 1 Then
    						objWS.Cells(RowCount,ColCount).Font.Bold = True
    						objWS.Cells(RowCount,ColCount).HorizontalAlignment = 1
    						objWS.Cells(RowCount,ColCount).Interior.Color = RGB(192,192,192)			
    					End If
    				Next
    			Else'Alternate rowcolor..grey
    				RowCount = RowCount + 1 
    				ColCount = 0
    				objExcel.rows(rowCount).Interior.Color = RGB(192,192,192)
    				For Each MyCell In MyRow.Cells
    					ColCount = ColCount + 1
    					If Trim(MyCell.InnerText) = "" Then
    						MyValue = ""
    					Else
    						MyValue = MyCell.InnerText
    					End If
    					objWS.Cells(RowCount,ColCount) = Trim(MyValue)
    					If RowCount = 1 Then
    						objWS.Cells(RowCount,ColCount).Font.Bold = True
    						objWS.Cells(RowCount,ColCount).HorizontalAlignment = 1
    						objWS.Cells(RowCount,ColCount).Interior.Color = RGB(192,192,192)			
    					End If
    				Next
    			End If
    		Next
    		objExcel.Cells.Select
    		objExcel.Cells.Font.Name = "Arial"
    		objExcel.Cells.Font.Size = 8
    		objWS.Range("A1",Chr(ColCount + 64) & RowCount).Columns.Autofit
    		objExcel.Selection.HorizontalAlignment = &hFFFFEFDD 'Align Left
    		Set objWB = Nothing		'Cleaning up
    		Set objWS = Nothing		'Cleaning up
    		Set objExcel = Nothing  'Cleaning up
    End Sub
    </script>
    Last edited by Mirador; 02-09-04 at 07:43.
    Best regards
    Mirador

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •