Solved

Save  asp data to excel

Posted on 2001-07-27
21
539 Views
Last Modified: 2007-11-27
Hi experts,
can I save the info  from one of my asp page to an excel file( on my hard disk ) in certain cells( let me say cell D30)? how?
thanks
Suying
0
Comment
Question by:lyu97
  • 8
  • 6
  • 4
  • +1
21 Comments
 

Expert Comment

by:jsolo
ID: 6327843
lyu97,

This code will take an entire table or query and place it into an Excel file.


<%response.ContentType="application/vnd.ms-excel"%>


<%
               accessdb="Unix_Hosts.mdb"
               myDSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
               myDSN = myDSN & "DATA SOURCE=" & server.mappath(accessDB) & ";"

               SQL = request.querystring("SQL")
               Set conn = Server.CreateObject("ADODB.Connection")
               conn.open myDSN
               set rs=server.createobject("adodb.recordset")
               rs.open SQL,conn,3,3
               totalfields=rs.fields.count

               rowstart="<tr>"
               rowend="</tr>"
               cellstart="<td>"
               cellend="</td>"
               
               response.write "<table border=1>" & rowstart
               For each item in rs.fields
                   response.write cellstart & "<b><font = 'Arial'>" & item.name & "</font></b>" & cellend
               Next
               If not rs.eof then
                   arrAllData=rs.getrows
                   totalrecs=rs.recordcount
               End If    
               

               rs.close
               set rs=nothing
               conn.close
               set conn=nothing
               response.write rowend
               If totalrecs=0 then
                   response.write rowstart & "<td colspan=" & totalfields & ">No data was returned" & cellend & rowend
               Else
                   For rowcount=0 to totalrecs-1
                       response.write rowstart
                       For colcount=0 to totalfields-1
                           response.write cellstart & arrAllData(colcount,rowcount) & cellend
                       Next
                       response.write rowend
                   Next
               End If
               response.write "</table>"
               

%>


Hope this helps!
0
 

Expert Comment

by:jsolo
ID: 6327873
lyu97,

This code will take an entire table or query and place it into an Excel file.


<%response.ContentType="application/vnd.ms-excel"%>


<%
               accessdb="Unix_Hosts.mdb"
               myDSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
               myDSN = myDSN & "DATA SOURCE=" & server.mappath(accessDB) & ";"

               SQL = request.querystring("SQL")
               Set conn = Server.CreateObject("ADODB.Connection")
               conn.open myDSN
               set rs=server.createobject("adodb.recordset")
               rs.open SQL,conn,3,3
               totalfields=rs.fields.count

               rowstart="<tr>"
               rowend="</tr>"
               cellstart="<td>"
               cellend="</td>"
               
               response.write "<table border=1>" & rowstart
               For each item in rs.fields
                   response.write cellstart & "<b><font = 'Arial'>" & item.name & "</font></b>" & cellend
               Next
               If not rs.eof then
                   arrAllData=rs.getrows
                   totalrecs=rs.recordcount
               End If    
               

               rs.close
               set rs=nothing
               conn.close
               set conn=nothing
               response.write rowend
               If totalrecs=0 then
                   response.write rowstart & "<td colspan=" & totalfields & ">No data was returned" & cellend & rowend
               Else
                   For rowcount=0 to totalrecs-1
                       response.write rowstart
                       For colcount=0 to totalfields-1
                           response.write cellstart & arrAllData(colcount,rowcount) & cellend
                       Next
                       response.write rowend
                   Next
               End If
               response.write "</table>"
               

%>


Hope this helps!
0
 

Expert Comment

by:jsolo
ID: 6327883
oops, sorry about that double post!

-jsolo
0
 

Author Comment

by:lyu97
ID: 6327951
Hi jsolo,
I just want to grib some info from one asp page and save it to excel file. not the table or query something from database.let me say: there are three text box on asp page, when people click submit button, I want to save the data in that three text boxes to an excel file.
thanks
Suying
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6327983
You want to save it to a specific .xls file?  If so, you can use the FSO to open and write to the file.

<%
fileExcel = "theFile.xls"
filePath = Server.mapPath("dirOfFile")
filename = filePath & "\" & fileExcel

Set fs = Server.CreateObject("Scripting.FileSystemObject")
Set MyFile = fs.CreateTextFile(filename, 2, True)
Do while Not rs.EOF
            str = rs.Fields("Name")&chr(9) &_
rs.Fields("FirstName")&chr(9) &_
rs.Fields("Middle")&chr(9) &_
rs.Fields("LastName")&chr(9)
strLine = str
          MyFile.writeline strLine
       rs.MoveNext
       Loop

       'Clean up
       MyFile.Close
       Set MyFile=Nothing
       Set fs=Nothing
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328007
Dim Excel, oSheet, oRng,oBook

Set Excel = CreateObject("Excel.Application")
 
Excel.Workbooks.Add
Set oSheet = Excel.ActiveSheet
Set oBook = Excel.ActiveWorkbook

With oBook
   .Worksheets(1).Cells(1, 1) = "Hello World"
End With

Set oRng = oSheet.Range("A1", "Z1")
oRng.EntireColumn.AutoFit
Excel.ActiveWorkbook.RefreshAll
 
Excel.ActiveWorkbook.SaveAs "c:\test.xls", FileFormat:=xlNormal
Excel.Quit
0
 

Author Comment

by:lyu97
ID: 6328023
Hi mgfranz,
what if the excel file is on the client machine?
actually the excel file is saved in Oracle database. the user can download it to their hard disk, do some change and save back to the database. I just want to put some data in certain cells before he/she save back the file.
the code you gave to me seems like save the info to excel file(I haven't try it, maybe I'm wrong), but I want to save the info to specific cells in that excel file(there are list of people name in that excel file, after I got the people's name from my asp page, I want to save that  name to excel file in his/her spot)
thanks
Suying
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6328055
Look at Dave Greens code, it looks good.
0
 

Author Comment

by:lyu97
ID: 6328242
Hi Dave Greene,
when I ran the code you give me, I got error:
Microsoft VBScript compilation error '800a0400'

Expected statement

/TestAR/Savesignature.asp, line 175

Excel.ActiveWorkbook.SaveAs "D:\Misc\test.xls", FileFormat:=xlNormal

(which is posted on = sign)
I'm new to use excel file in asp, could you tell me what to change from you code since I have an excel file aready.(what to do with:
Set Excel = CreateObject("Excel.Application")

Excel.Workbooks.Add
you gave )

Thank you so much.
Suying
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328298
May need to be in Dbl Quotes in ASP

Dim Excel, oSheet, oRng,oBook

Set Excel = CreateObject("Excel.Application")

Excel.Workbooks.Open "C:\your.xls"

Set oSheet = Excel.ActiveSheet
Set oBook = Excel.ActiveWorkbook

With oBook
  .Worksheets(1).Cells(1, 1) = "Hello World"
End With

Set oRng = oSheet.Range("A1", "Z1")
oRng.EntireColumn.AutoFit
Excel.ActiveWorkbook.RefreshAll

Excel.ActiveWorkbook.SaveAs "c:\Your.xls", "FileFormat:=xlNormal"
Excel.Quit


If this line gives you problems...
Excel.ActiveWorkbook.SaveAs "D:\your.xls", "FileFormat:=xlNormal"

Change TO

Excel.ActiveWorkbook.SaveAs "D:\your.xls"
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:lyu97
ID: 6328401
Hi Dave Greene,
I just got on this web site(I've tried too many time)and pasted your code to my asp and just change the code:
With oBook
 .Worksheets(1).Cells(1, 1) = "Hello World"
End With
 to:
With oBook
 .Worksheets(3).Cells(B43) = "Hello World"
End With
and got the error:
Microsoft VBScript runtime error '800a03ec'

Unknown runtime error

/TestAR/Savesignature.asp, line 171
which is:
.Worksheets(3).Cells(B43) = "Hello World"

any clue?
thanks
suying
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328437
So you are trying to go to the third page and print some text?

Try this...
.Worksheets(3).Cells(2, 43) = "Hello World"


I believe you can also reference the worksheet name like so
.Worksheets("Sheet3").Cells(2, 43) = "Hello World"

0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328442
Note: If your workbook doesn't have a sheet3 you will get an error...
0
 

Author Comment

by:lyu97
ID: 6328556
Hi Dave Greene,
another thing want to ask is:why you use the Saveas in the line:Excel.ActiveWorkbook.SaveAs "D:\your.xls"
can I save back to the original file?
thanks
Suying
0
 
LVL 8

Expert Comment

by:Dave_Greene
ID: 6328754
Yes... you can just use

Excel.SaveWorkspace "c:\test\test.xls"
0
 

Author Comment

by:lyu97
ID: 6328856
Hi Dave Greene,
I tried the code several times(died in middle,so I shout down serveral time )and is kind of slow and I haven't get result yet. maybe I will give you the answer Monday(if it work I will close this question).
here I want to ask you another question(if possible I will post it on EE, so you can answer me from there, not in this question, I can't do this).
can I add a button in one of this Excel file sheet, after user finish change infomation,he/she can click this button to upload this Excel file to the server?
actually I have all the code in asp page to do this, but my boss doesn't like this to let the user first save this file to her/his disk and then click the browser in  my uploadform.htm file and click upload....he said I can create some Macros and call my asp page to upload file to the server and save it to database, I don't know how. if you think this is possible, just add a comment for this question, so I ask this question on EE on Monday.
(I think there is something wrong, my program still run about 6 min)
thanks.

Suying
0
 

Author Comment

by:lyu97
ID: 6329185
Hi Dave Greene,
here is the code in asp page:
<%
Dim excel, oSheet, oRng,oBook
Set excel =server.CreateObject("Excel.Application")

excel.Workbooks.Open"C:\Book1.xls"
Set oSheet = excel.ActiveSheet
Set oBook =excel.ActiveWorkbook
 oBook.Worksheets(1).Cells(6, 1) = "Hello World"
 Set oRng = oSheet.Range("A1","Z1")
 oRng.EntireColumn.AutoFit
 excel.ActiveWorkbook.RefreshAll
excel.SaveWorkspace "C:\Book1.xls"
excel.Quit
Response.Write "thank you"
%>
 when I ran get the error:
error 'ASP 0113'
Script timed out...
I think I can't put the above code in asp because the excel file is on client's machine, not on the server.maybe I need write a function...
any idea.

thanks
suying

0
 
LVL 8

Accepted Solution

by:
Dave_Greene earned 30 total points
ID: 6329794
<<I think I can't put the above code in asp because the excel file is on client's machine, not on the
server.maybe I need write a function...
any idea.>>

If the file is on the client machine, you will need to upload it to the server before you can perform processing against it.
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6329807
Or run the script client-side... IE only and Excel must be loaded on the client.

<html>
<head>
<script language="vbscript">

Function WriteExcel() {

Dim excel, oSheet, oRng,oBook
Set excel =server.CreateObject("Excel.Application")

excel.Workbooks.Open"C:\Book1.xls"
Set oSheet = excel.ActiveSheet
Set oBook =excel.ActiveWorkbook
oBook.Worksheets(1).Cells(6, 1) = "Hello World"
Set oRng = oSheet.Range("A1","Z1")
oRng.EntireColumn.AutoFit
excel.ActiveWorkbook.RefreshAll
excel.SaveWorkspace "C:\Book1.xls"
excel.Quit
Response.Write "thank you"
}
</script>
<body onLoad="WriteExcel()">
...

UNTESTED
0
 

Author Comment

by:lyu97
ID: 6330746
hi mfgranz,
if I run the script client-side, I can't use the code:
Set excel =server.CreateObject("Excel.Application")
what I should change with this line of code?
thanks
suying
0
 
LVL 18

Expert Comment

by:mgfranz
ID: 6330765
Sorry... bad info, belay my last.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now