×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Recordset to export to excel not working..Help

Recordset to export to excel not working..Help

Recordset to export to excel not working..Help

(OP)
Hi:
  I have a recordset that I need to export to excel. It is telling me that my recordset is empty. Can someone glance over my code and tell me where I am wrong. Thanks
   <HTML>
   <HEAD>
   <TITLE>Create Tab Delimited Text File</TITLE>
   </HEAD>
   <body>
<%
OpenADOConn

Dim strSQL
Dim rs
set rs = server.CreateObject("ADODB.Recordset")
strSQL= "mysqlstatement(itslong)"
rs.Open strSQL,ADOConn,1,2
%>
<Script language=vbscript>
 'Create a randome Filename
  nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)
    fileExcel = "t" & CStr(nRandom) & ".xls"
    filename= "U:\My Documents\test\new.xls"
         'Create the File with extension .xls using the FileSytemObject
         'If the file does not exist, the TRUE parameter will allow it
         'to be created. Make sure the user* impersonated has write
         'permissions to the directory where the file is being created.

        Set fs = CreateObject("Scripting.FileSystemObject")
        set MyFile=fs.CreateTextFile(filename,true)
         'Open the connection and retrieve data from the database


          strLine="" 'Initialize the variable for storing the filednames

          For each x in rs.fields
            'Separate field names with tab so that these appear in
            'different columns in Excel
            strLine= strLine & x.name & chr(9)
        
          Next
         'Write this string into the file
            MyFile.writeline strLine
            'Response.Write MyFile
         'Retrieve the values from the database and write into the database
          Do while Not rs.EOF
          strLine=""
          
          for each x in rs.Fields
            strLine= strLine & x.value & chr(9)
            Response.Write strLine
          next
          MyFile.writeline strLine
          rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing

       'Show a link to the Excel File.
       link="<A HREF=" & fileExcel & ">Open Excel</a>"
       Response.write link
    rs.Close
    Set rs = Nothing
    CloseADOConn
</Script>
</BODY>
</HTML>
Thank You

RE: Recordset to export to excel not working..Help

The first problem I see is that half your code is executing on the server (stuff in <%'s) and the other half is attempting to execute on the client browser (stuff in script tags).

Try taking out the script tags and placing the %> down at the bottom before the HTML end body tag.

Also, as a sidenote, the Rnd() function isn't truly random (nothing in the computer really is). It actually is calculating a ocmplex function based on a seed number. Unfortunatly VB/VBScript's version of the Rnd function always uses the same seed number unless you tell it to use another, the reulst being that your "Random" number always comes out the same. To correct this all you have to do is call the Randomize() method before making any calls to the Rnd() function. The Randomize() method re-seeds the random function (I believe it uses the millisecond count for system time, which will always be differant) and you will then get a more better random number.

The Randomize function doesn't require any arguments, you can just insert it as is before the nRandom line:
'Create a randome Filename
  Randomize()
  nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)


-T

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website: http://www.tiernok.com/


RE: Recordset to export to excel not working..Help

(OP)
Hi Tarwn...Thank you for the quick response. Basically I did what you ask me and it doesn't create the new.xls spreadsheet onto my path.
It gives me an error on
set MyFile=fs.CreateTextFile(filename,true)
statement.
Microsoft OLE DB Provider for ODBC Drivers error '800a004c'

[Microsoft][ODBC SQL Server Driver]Cursor type changed

/USCC/TimesheetsDEV/TAS_RPT_Prod.asp, line 38
but it does write out the recordset onto the page. So I know it is picking up the rs. I need it to now export it onto the excel spreadsheet.
Thanks

RE: Recordset to export to excel not working..Help

Thats odd, the error doesn't match up right...is that error the one referring to the CreateTextFilel line or did I misunderstand?

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website: http://www.tiernok.com/


RE: Recordset to export to excel not working..Help

(OP)
Yes that refers to the createTextfile line

RE: Recordset to export to excel not working..Help

Wierd, thats an ADO error, it doesn't have anything at all to do with the line in question. For the heck of it, try to change the cursor in your .Open statement.

I can't see anything obviously wrong, but maybe that will shake something loose.

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website: http://www.tiernok.com/


RE: Recordset to export to excel not working..Help

(OP)
I change it now it is telling me that
Microsoft VBScript runtime error '800a004c'

Path not found

On this line:
filename= "U:\My Documents\test\new.xls"
        

Set fs = CreateObject("Scripting.FileSystemObject")
set MyFile = fs.CreateTextFile(filename,true)<==This Line
Thanks

RE: Recordset to export to excel not working..Help

Ok, it's not liking the U: drive apparently. Try another path, like something on the C: drive. Is U: a mapped drive? You may need to change permissions to allow read and write access.

-T

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website: http://www.tiernok.com/


RE: Recordset to export to excel not working..Help

(OP)
I changed it to c: drive and I didn't get the error again but it doesn't create the new.xls file out there.  basically it is not reading the rs.fields. it is empty.
but the when I do response.write strline. I can see the data on the screen. Thanks

RE: Recordset to export to excel not working..Help

Try to open the file in notepad or something like that, I think your outputting tab-delimited format which can be loaded into excel but is not the same as .xls format

-T

01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111
The never-completed website: http://www.tiernok.com/


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close