MS Access to Adobe PageMaker TAGS - By Michael Wright

MS Access to PageMaker (the page make-up software) TAGS - By Michael Wright

The 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, "&#8216;", "'")

strToCheck = Replace(strToCheck, "&#8217;", "'")

strToCheck = Replace(strToCheck, "&#8220;", "'")

strToCheck = Replace(strToCheck, "&#8221;", "'")

strToCheck = Replace(strToCheck, "&#8211;", "-")

strToCheck = Replace(strToCheck, "&#160;", "&")

strToCheck = Replace(strToCheck, "&amp;", " ")

' 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!