• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

Save asp data to excel

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
lyu97
Asked:
lyu97
  • 8
  • 6
  • 4
  • +1
1 Solution
 
jsoloCommented:
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
 
jsoloCommented:
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
 
jsoloCommented:
oops, sorry about that double post!

-jsolo
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lyu97Author Commented:
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
 
mgfranzCommented:
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
 
Dave_GreeneCommented:
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
 
lyu97Author Commented:
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
 
mgfranzCommented:
Look at Dave Greens code, it looks good.
0
 
lyu97Author Commented:
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
 
Dave_GreeneCommented:
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
 
lyu97Author Commented:
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
 
Dave_GreeneCommented:
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
 
Dave_GreeneCommented:
Note: If your workbook doesn't have a sheet3 you will get an error...
0
 
lyu97Author Commented:
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
 
Dave_GreeneCommented:
Yes... you can just use

Excel.SaveWorkspace "c:\test\test.xls"
0
 
lyu97Author Commented:
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
 
lyu97Author Commented:
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
 
Dave_GreeneCommented:
<<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
 
mgfranzCommented:
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
 
lyu97Author Commented:
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
 
mgfranzCommented:
Sorry... bad info, belay my last.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now