Sample Macro (Word 97)

Subject: Sample Macro (Word 97)
From: JIMCHEVAL -at- AOL -dot- COM
Date: Fri, 5 Sep 1997 15:07:48 -0400

Several people have asked me for sample Word macros. I still need to dig out
a relatively clean version of the 'best seller' - my template which holds a
series of Word 6 macros which rearrange a TOC into an index. I'll probably
just e-mail that directly to individuals, so anyone who wants a copy (don't
be in a hurry, please) let me know.

Meanwhile, I've stripped down one of my Word 97 macros (in Visual Basic) to
create a model macro. Comments say the rest. Copy everything below from
"Sub" to "End Sub" and paste it into a blank macro (Visual Basic code space).
The comments will magically turn green, which should make them easier to
read.

Jim C.
Los Angeles
=======================================================
Visit Chez Jim" Jim Chevallier's Home Page - http://www.gis.net/~jimcheval
=======================================================

Sub JCMacroModel1()
'
' Macro created 09/5/97 by Jim Chevallier
' This is a sample VB macro which shows how to do several things
' - retrieve records directly from an Access database
' (you must of course have an existing database and table)
' - write directly to a low-level output file (text, not Word)
' - create automated HTML
'NOTE: To enable the data access used here, you must check the REFERENCES
list under TOOLS
' (in the Visual Basic Editor, not Word!) and be sure that "Microsoft DAO
3.5 Object Library"
'is checked.
'
' Declare variable for database, recordset (=table) and field
Dim dbsSMP As Database
Dim rstVendors As Recordset

'Open an Access database and assign it to the Database variable
Set dbsSMP = OpenDatabase("C:\My Documents\Access Databases\SMPLE",
dbDriverComplete)
' Point the Recordset variable to the desired table in the database
Set rstVendors = _
dbsSMP.OpenRecordset("Vendors")
'Be sure you're pointing to the right output directory
ChDir "C:\My Documents\HTML\test output"
'
'---------------------------------------------------MAIN PROCESSING LOOP
'This FOR loop runs through every record from 0 (=the first) to the last
' RecordCount here is a property of a RecordSet

For I = 0 To rstVendors.RecordCount - 1

' Note the WITH Statement!
' Once you say you're working 'With' an object, you don't have to add its
name before each property
'(until you end the 'With')
'Everything that follows is done 'with' the declared recordset (table) unless
otherwise specified

With rstVendors

'Assign the COMPANY name from the Access table to a variable

F = (.Fields("COMPANY"))

'-Since F will be used as an output file name in Windows 95, modify any
'illegal' characters

slsh = InStr(F, "/")
While slsh > 0
F = Left(F, slsh - 1) + "_" + Right(F, (Len(F) - slsh))
slsh = InStr(F, "/")
Wend
bslsh = InStr(F, "\")
While bslsh > 0
F = Left(F, bslsh - 1) + "_" + Right(F, (Len(F) - bslsh))
bslsh = InStr(F, "\")
Wend
star = InStr(F, "*")
While star > 0
F = Left(F, star - 1) + "_" + Right(F, (Len(F) - star))
star = InStr(F, "*")
Wend
br = InStr(F, "|")
While br > 0
F = Left(F, br - 1) + "_" + Right(F, (Len(F) - br))
br = InStr(F, "|")
Wend
sc = InStr(F, ":")
While sc > 0
F = Left(F, sc - 1) + "_" + Right(F, (Len(F) - sc))
sc = InStr(F, ":")
Wend
gt = InStr(F, ">")
While gt > 0
F = Left(F, gt - 1) + "_" + Right(F, (Len(F) - gt))
gt = InStr(F, ">")
Wend
lt = InStr(F, "<")
While lt > 0
F = Left(F, lt - 1) + "_" + Right(F, (Len(F) - lt))
lt = InStr(F, "<")
Wend
qt = InStr(F, Chr$(34))
While qt > 0
F = Left(F, qt - 1) + "_" + Right(F, (Len(F) - qt))
qt = InStr(F, Chr$(34))
Wend
qs = InStr(F, "?")
While qs > 0
F = Left(F, qs - 1) + "_" + Right(F, (Len(F) - qs))
qs = InStr(F, "?")
Wend

' Open a low-level file for output, assigning it to #1
'(you can open others at the same time, using other numbers)
'The file name here is the Company name concatenated with ".HTML"

Open F + ".HTML" For Output As #1
'
' Write ("print") each line as a record in the output (text) file

' This code creates the basic framework for an HTML file
Print #1, "<html>" ' Print text to file.
Print #1, "<head>" ' Print text to file.
Print #1, "<title>" + (.Fields("COMPANY")) + "</title>"
Print #1, "</head>"
Print #1, "<body > "
Print #1, ""

'This piece inserts the code for an HTML table
' Use CHR$(34) to insert quotes (no tabs here, but use CHR$(9) when you need
them)
Print #1, "<table border=" + Chr$(34) + "0" + Chr$(34) + " cellpadding=" +
Chr$(34) + "5" + Chr$(34) + " cellspacing=" + Chr$(34) + "0" + Chr$(34) + "
width=" + Chr$(34) + "418" + Chr$(34) + ">"
Print #1, " <tr>"
'-NOTE the IsNull test; you should generally test if there's any value in
each field before using it
If Not (IsNull(.Fields("CITY"))) Then
Print #1, " <td width=" + Chr$(34) + "184" + Chr$(34)
+ "><font color=" + Chr$(34) + "#000000" + Chr$(34) + " size=" + Chr$(34) +
"2" + Chr$(34) + ""
Print #1, " face=" + Chr$(34) + "Times New
Roman" + Chr$(34) + ">" + (.Fields("CITY")) + "</font></td"
End If
Print #1, " </tr>"
Print #1, "</table>"
'end of table code

Print #1, "<p>Put what you want here - all HTML codes can be written out,
line by line</p>"
Print #1, "</body>"
Print #1, "</html>"
' END Html coding

'Close the output file

Close #1

'Move to the next record in the table
.MoveNext

'End the 'With' - any statement after this would have to have the object with
the property
' i.e., - "rstVendors.Fields" - NOT - ".Fields"

End With

'Increment the counter (this does NOT affect record access - the preceding
statement does that)

Next I

End
'---------------------------------END MAIN PROCESSING LOOP

'close the database

dbsSMP.Close

End Sub

TECHWR-L (Technical Communication) List Information: To send a message
to 2500+ readers, e-mail to TECHWR-L -at- LISTSERV -dot- OKSTATE -dot- EDU -dot- Send commands
to LISTSERV -at- LISTSERV -dot- OKSTATE -dot- EDU (e.g. HELP or SIGNOFF TECHWR-L).
Search the archives at http://www.documentation.com/ or search and
browse the archives at http://listserv.okstate.edu/archives/techwr-l.html


Previous by Author: Re: Academic careers with/vs. tech writing
Next by Author: Re: quick question
Previous by Thread: Re: Color Copying
Next by Thread: Help Guide, Corel Quattro Pro7 (Win 95)


What this post helpful? Share it with friends and colleagues:


Sponsored Ads