rewrite excell files on website

R

Raymondo

Have the following code in my web page that changes a mdb file to excel. I
would like it to rewrite to the same file each time instead of trying to make
a new file. It's probably simple but what changes will I have to make?

<%
' if you've got a large DB, you might want to increase
' the server timeout here

'set up the database/ recordset
Dim strProvider, strSQL, rsLinks
strProvider = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" &
Server.MapPath("databasename.mdb")
strSQL = "SELECT * FROM Membership ORDER BY ID "
set rsLinks = Server.CreateObject("ADODB.Recordset")
rsLinks.Open strSQL, strProvider, 1, 3, adCmdTable

'setup the excel file
Dim objFSO, objExcelFile
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcelFile =
objFSO.CreateTextFile(Server.MapPath("spreadsheetname.xls"))
'Do the header information
objExcelFile.writeline ("<html>")
objExcelFile.writeline ("<table border=1>")
'heading
objExcelFile.writeline ("<tr >")
objExcelFile.writeline (" <td bgcolor=#cccccc> MCNumber</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> MCName</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> MCChristian</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> MCPartner</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKAddress1</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKAddress2</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKTown</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKCounty</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKPostcode</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKTelephone</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> UKMobile</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> Email</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRAddress1</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRAddress2</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRPostcode</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRTown</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRDepartment</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRTelephone</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> FRMobile</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> AltEmail</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> MCRental</td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> Notes</td>")
objExcelFile.writeline ("</tr>")

'loop through the recordsets and fill in the cells
Dim intRowCount ' used for calculation at the end
intRowCount = 1
if not (rsLinks.eof and rsLinks.bof) then
rslinks.movefirst
while not rsLinks.eof
objExcelFile.writeline ("<tr bgcolor=#ffffff>")

objExcelFile.writeline (" <td>" & rsLinks("MCNumber") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("MCName") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("MCChristian") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("MCPartner") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKAddress1") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKAddress2") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKTown") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKCounty") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKPostcode") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKTelephone") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("UKMobile") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("Email") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRAddress1") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRAddress2") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRPostcode") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRTown") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRDepartment") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRTelephone") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("FRMobile") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("AltEmail") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("MCRental") & "</td>")
objExcelFile.writeline (" <td>" & rsLinks("Notes") & "</td>")

objExcelFile.writeline ("</tr>")
intRowCount = intRowCount +1
rslinks.movenext
wend
end if

'do a calculation
objExcelFile.writeline ("<tr>")
objExcelFile.writeline (" <td bgcolor=#cccccc> </td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> <b>Total:</b></td>")
objExcelFile.writeline (" <td bgcolor=#cccccc> =sum(c1:c"& intRowCount &
")</td>")
objExcelFile.writeline ("</tr>")

'close up shop
objExcelFile.writeline ("</table>")
objExcelFile.writeline ("</html>")


'redirect to the download link.
response.redirect ("members list.asp")
%>


<p>You have now updated the MC database and converted it into a Exel file</p>
 
J

Joel

You are creating a textfile each time you run the code. If the file exists
use getfile instead

from
objFSO.CreateTextFile(Server.MapPath("spreadsheetname.xls"))
to
objFSO.GetFile(Server.MapPath("spreadsheetname.xls"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I redirect to a URL? 0
Runtime error 462 help 0
Report Grouping 2
Error message "An error has occured in the script on this page" 1
Force results to print on separate pages 1
run report on company intranet and import to excel 0
calender problem 0
Adding signature to email through access 0

Top