|
rewrite excell files on website
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("spreadsheetn ame.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 <bTotal:</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") % <pYou have now updated the MC database and converted it into a Exel file</p |
rewrite excell files on website
You are creating a textfile each time you run the code. If the file exists
use getfile instead from objFSO.CreateTextFile(Server.MapPath("spreadsheetn ame.xls")) to objFSO.GetFile(Server.MapPath("spreadsheetname.xls ")) "Raymondo" wrote: 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("spreadsheetn ame.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 <bTotal:</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") % <pYou have now updated the MC database and converted it into a Exel file</p |
| All times are GMT +1. The time now is 03:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com