Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Recordset to export to excel not working..Help

Status
Not open for further replies.
zishan619

zishan619

Programmer
May 28, 2003
284
MX
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(&quot;ADODB.Recordset&quot;)
strSQL= &quot;mysqlstatement(itslong)&quot;
rs.Open strSQL,ADOConn,1,2
%>
<Script language=vbscript>
'Create a randome Filename
nRandom = Int((1000000 - 1 + 1) * Rnd + 1000000)
fileExcel = &quot;t&quot; & CStr(nRandom) & &quot;.xls&quot;
filename= &quot;U:\My Documents\test\new.xls&quot;
'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(&quot;Scripting.FileSystemObject&quot;)
set MyFile=fs.CreateTextFile(filename,true)
'Open the connection and retrieve data from the database


strLine=&quot;&quot; '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=&quot;&quot;

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=&quot;<A HREF=&quot; & fileExcel & &quot;>Open Excel</a>&quot;
Response.write link
rs.Close
Set rs = Nothing
CloseADOConn
</Script>
</BODY>
</HTML>
Thank You
 
Sort by date Sort by votes
The first problem I see is that half your code is executing on the server (stuff in
Code: 
<%
'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
Code: 
%>
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 &quot;Random&quot; 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:
Code: 
'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:
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
I change it now it is telling me that
Microsoft VBScript runtime error '800a004c'

Path not found

On this line:
filename= &quot;U:\My Documents\test\new.xls&quot;


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

 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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:
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

leifoet
  • Locked
  • Question
Code does not run in Microsoft Edge - it does in Google Chrome
Replies
3
Views
958
leifoet
leifoet
leifoet
  • Locked
  • Question
How to send HTML emails with ASP classic?
Replies
0
Views
815
leifoet
leifoet
penguinspeaks
  • Locked
  • Question
Return to current location on page after asp processes.
Replies
2
Views
723
penguinspeaks
penguinspeaks
leifoet
  • Locked
  • Question
How to check if a datatable is empty ?
Replies
0
Views
585
leifoet
leifoet
bslintx2021
  • Locked
  • Question
How to configure IIS 10 server to allow Classic ASP to connect to MSSQL server database on a domain
Replies
1
Views
1K
penguinspeaks
penguinspeaks

Part and Inventory Search

Sponsor

Back
Top