MS Access to PageMaker (the page make-up software) TAGS - By Michael WrightThe script below can be modified to retrieve data from an MS Access database and then output the data to plain text format with PageMaker TAGS. This is very useful when exporting data regularly and quickly from databases to save designers time when they’re creating publications, journals etc. To use the below script I advise you to have a medium level of ASP knowledge and some knowledge of PageMaker. Cut and paste the code in parts, not forgetting the <% %> TAGS where appropriate. Have FUN! First copy the function below which is used for converting database field data to plain text, ridding it of any unwanted HTML codes. Function ChangeChars(strToCheck) ' Replace unsupported characters e.g. HTML code characters strToCheck = Replace(strToCheck, "‘", "'") strToCheck = Replace(strToCheck, "’", "'") strToCheck = Replace(strToCheck, "“", "'") strToCheck = Replace(strToCheck, "”", "'") strToCheck = Replace(strToCheck, "–", "-") strToCheck = Replace(strToCheck, " ", "&") strToCheck = Replace(strToCheck, "&", " ") ' Add any other characters to be replaced here ChangeChars = strToCheck End Function Now we come to the main code. Copy the variable initialization. Dim strconn Dim conn Dim Champtotal Dim strSQL Dim objFSO, objTextFile Dim StartDate Copy the database connection setup and modify for your own database. strconn = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("YOUR DATABASE FULL PATH") set conn = server.createobject("adodb.connection") conn.open strconn set Champtotal = server.createobject("adodb.recordset") Copy the SQL query and modify it to suit your needs. Add the WHERE FIELDNAME = '' to query specific fields. strSQL = "SELECT * FROM YOUR DATABASE TABLE" Copy the open connection statement and also move to the first record of the recordset. Champtotal.open strSQL, conn Champtotal.MoveFirst Copy the code for creating the text file object and text file setup, location, name etc. Set objFSO = CreateObject("Scripting.FileSystemObject") Set objTextFile = objFSO.CreateTextFile(Server.MapPath("YOUR TEXT FILE FULL PATH")) ' E.g. ../output.txt Now copy the code for writing the fields and PageMaker TAGS. First you need to add write the setup TAG below. objTextFile.WriteLine "<PMTags mac 1.0>" Copy the loop below for placing each field you need from the ADO recordset to the plain text file. Each time the loop writes a field to the text file a PageMaker TAG is placed before it. These TAGS are used by PageMaker to automatically format the text. These TAGS should be created in PageMaker to match the ones below which will allow you to preset text to bold, italic, specific font names etc. You can change the names of the PageMaker TAGS to whatever you want e.g. from <p1> to <bold> or <headline>. Please remember you need to create the TAGS in PageMaker. It is not like HTML where you have to close the TAGS, the reader simply uses a TAG until it finds the next one. Do While Not Champtotal.EOF objTextFile.WriteLine "<p1>" + Champtotal.Fields("FIELD1") objTextFile.WriteLine "<p2>" + Champtotal.Fields("FIELD2") ' To add a date field please use the code below objTextFile.Write "<p3>" DateFIELD = FormatDateTime(Champtotal.Fields("DateFIELD"),1) objTextFile.Write DateFIELD objTextFile.Write Chr(13) ' End of adding date field code objTextFile.WriteLine "<p4>" + Champtotal.Fields("FIELD3") ' The code below uses the ChangeChars function. It can be used for all of them if you wish objTextFile.WriteLine "<p5>" + ChangeChars(Champtotal.Fields("FIELD4")) ' Feel free to add further lines of code to add extra TAGS and fields to the text file objTextFile.Write Chr(13) ' This will add a space between the records written to the text file Champtotal.MoveNext Loop Copy the code below to close all objects, recordsets, connections etc ' Close the file. objTextFile.Close ' Release reference to the text file. Set objTextFile = Nothing ' Release reference to the File System Object. Set objFSO = Nothing Champtotal.close set Champtotal = nothing set strconn = nothing set conn = nothing Copy the code below to finally transport the user to a specific page or to the text file created. Response.Redirect("../vetcpd/pagemaker.txt") End of code I hope the above code is of some use to someone. I happen to think it’s very useful and I have never seen anything like it anywhere. Please feel free to send me any comments and if you want to offer me any job vacancies that would be great too - michael_wright@lineone.net! | 
developerWorks - FREE Tools!
