Open Access Newsletter

Newsletter of the Australian Open Access User Group

October, 2000

Australian OA User Group Homepage CHANGED TO: Number 141

The Next Meeting and Christmas Party





Tuesday, 12th December, 2000

6pm to 9pm


AOAUG Meeting held 10 October, 2000

This Meeting was held at Judy Jeffery’s house.

We discussed the GST and some of the awkward accounting methods needed to cope with transition issues. For example, Telstra bills have included back-dated GST amounts so the GST charged exceeds 10% and other bills span the changeover, and therefore the GST charged is less than 10%.

Other issues discussed were viruses, Excel techniques and conversion of old data files.

Website Address Change

Our apologies but the address of the User Group's website has changed due to a reorganisation of servers by our Internet Service Provider TPGI. We are endeavouring to have the old URL work as well as the new, so that links will still work.

See the top of this page for the new address.



Microsoft Embarrassed

Microsoft should have read our August Newsletter. It turns out that their much-publicised security breach was probably caused by the QAZ Worm virus! See New Scientist 30 October 2000 (link on our website).

Excel 97/2000

Macros and Visual Basic

by Michael Paine

I had my first dabble with Excel 97 macros and Visual Basic programming the other day. It went remarkably well. It is like a cross between Open Access macros and macro editor and Open Access Programmer, with the extra power of Visual Basic. I found it much easier to use than the cumbersome Visual Basic system that comes with MS Access.

A Refresher on Open Access Macros

Recall that anywhere within Open Access you can start recording a macro (keystrokes) by pressing [Alt F8] and selecting CREATE. When you have finished the sequences of keystrokes press [Alt F8] again and select SAVE. You can then replay your macro at any time by pressing [Alt F8] and selecting EXECUTE. Better still you can repeat the macro numerous times by selecting REPEAT.

You can also edit Open Access macros by using the Macro Converter. Press [F8] and select Macro Converter. Select Macro to Text to generate a text file version of your macro. Then press [F8] to open the text file in Notepad. You can then edit the macro code (not an easy task), save the text file and convert it back to a macro.

A great facility and a nuisance that a similar facility is not available throughout Windows (Win3.1 had the very limited Recorder, but it was dropped when WIN95 was released).


Excel Macros

Macros can be created within Excel 97/2000 in a similar way. First you need to enable the Visual Basic Toolbar (VIEW/TOOLBARS and check the Visual Basic box). Next place the cursor in the appropriate starting position then click on the New Macro button - as usual with Microsoft logic this is the filled-in circle

Give your new macro a name and click OK. The circle changes to a square! Now start your sequence of keystrokes. Mouse selections are also recorded, as are menu selections. When you have finished click on the button with the square. Your new macro can now be accessed from the Run Macro button (filled-in triangle!).

Editing the new macro is much easier than with Open Access. Simply select the macro from the list presented when you click on the Run Macro button and select Edit. The Visual Basic editing window pops up and your macro is display in (fairly) plain English.

Visual Basic

The Visual Basic with Excel is quite easy to use, but there are some odd things to remember. In particular there is no command to address and work on a single cell. Instead you must select a "Range" consisting of one column and one row. For example Range ("B3:B3").

I had a problem when I wanted to split the values from one column according to categories specified in another column. Now you can do this with an IF formula, but I found a major difficulty plotting the subsequent results with a scatter (XY) graph. The problem is that an IF statement such as IF(K5="A",G5,"") puts either the desired value in the cell or a NULL value. Unfortunately, the null value is treated as a zero for XY charts and is plotted as zero. However, if the blank cells are CLEAR then they are ignored by the XY chart. I wanted them to be ignored (as there are in Open Access scatter graphs!) so I had to write a program/macro to achieve this. It worked well.

Below is some sample code. Note: the power of the statement "For Each Item in Selection". This steps through each cell in the selection and works on it using the code up to "Next Item". Note also the use of an offset value to pick the value from the G column. The relative addressing looks a little strange but Range("a1:a1") simply selects one cell at the offset location.


Public Sub split_speed()

' clear target area

Range("l3:n242").Clear 'This clears all cells in the range

' select the input range

Range("K3:K242").Select ' column K has A,C or D as a value

s1 = "text"

os = 0

For Each Item In Selection ' this steps through every cell in the

selected column

s1 = Trim(Item.Value)

os = 0 ' number of columns offset from selected column (K)

If s1 = "A" Then

os = 1 ' set offset value to 1

ElseIf s1 = "C" Then

os = 2

ElseIf s1 = "D" Then

os = 3


os = 0

End If

If os > 0 Then

r1 = Item.Offset(0, -4).Range("a1:a1").Value ' read column G value. same row)

'the a1:a1 is odd but needed - it selects one cell at the relative address

Item.Offset(0, os).Range("a1:a1").Value = r1 ' set appropriate

column in output range to the value from col G

End If

s2 = s1

s3 = "X"

Next Item ' work on the next cell in the range

End Sub

There are more great tips in the book "Excel 97 Bible" by John Walkenbach. He also has a great suite of Excel utilities - the "Power Utility Pack".

What's "dat" file?

Report from Lindy about converting DAT data files (sequential files created by old versions of Basic).

File Conversion

I have a *.dat file that I wanted to convert using either Open Access or Visual Basic. I tried several ways to look at the original data, and found that I could read it in Microsoft Word.

I discovered that it was a huge text file, with each line being over a thousand characters long, but each record within it seemed to be a fixed length. This meant that I could not use Open Access, as the lines were too long.

Michael suggested that it could be a Visual Basic file and suggested I use the following to extract the data I required. The format is for a file he used, but the principle is the same.


'Create a data file gem_town.dat

Sub Command3_Click ()
Dim mapline As gem_town_data
Open "c:\vb_run\gem_town.dat" For Random As #3 Len = 35
recno = 0
While Not data2.recordset.EOF = data2.recordset.fields("town").value
mapline.state = data2.recordset.fields("state").value
If Not IsNull(data2.recordset.fields("postcode").value) Then
mapline.postcode = data2.recordset.fields("postcode").value
mapline.postcode = ""
End If
mapline.x_grid = data2.recordset.fields("x_grid").value
mapline.y_grid = data2.recordset.fields("y_grid").value
If Not IsNull(data2.recordset.fields("page#").value) Then = data2.recordset.fields("page#").value
Else = ""
End If
recno = recno + 1
label3.Caption = Str(Int(recno))
Put #3, recno, mapline
Close #3
End Sub

' open files
Open "c:\vb_run\oz_map.dat" For Random As #1 Len = 10
rc1 = LOF(1) / 10
Open "c:\vb_run\gemstone.dat" For Random As #2 Len = 16
rc2 = LOF(2) / 16
Open "c:\vb_run\gem_town.dat" For Random As #3 Len = 35
rc3 = LOF(3) / 35
Open "c:\vb_run\gem_loc.dat" For Random As #4 Len = 72
rc4 = LOF(4) / 72

' Read data
Sub Form_Load ()
Dim townr As gem_town_data
' fill SelectGem Combo Box
For rn3 = 1 To rc3
Get #3, rn3, townr
combo1.AddItem Trim(
Next rn3

End Sub

Tips – Microsoft Word

Keyboard Shortcuts



ALT +SHIFT +D Insert Current Date

ALT +SHIFT +T Insert Current Time


Get online Help or the Office Assistant


Move text or graphics


Insert an AutoText entry (after Word displays the entry)


Repeat the last action


Choose the Go To command (Edit menu)


Go to next pane or frame


Choose the Spelling command (Tools menu)


Extend a selection


Update selected fields


Activate the menu bar


Go to the next field


Choose the Save As command (File menu)





Format letters as small capitals


Apply subscript formatting (automatic spacing)


Apply superscript formatting (automatic spacing)


Remove manual character formatting


A line break


A page break


A column break


An optional hyphen


A nonbreaking hyphen


A nonbreaking space


The copyright symbol


The registered trademark


Uses of Frames/Text Boxes

Frames enable more flexible formatting than is usually possible.

Frames can be used to position text, drawings or pictures in any part of the document. For example, frames allow you to place text or pictures in the margins or in the middle of other text

Semi-Transparent Text Box

Word has an option: ‘Semi-Transparent’ which allows existing text in the background to show faintly through the frame.

Insert a Watermark

To display the watermark behind the text:



Users should not act solely on the basis of the material contained in this document. Items contained in this document are presented as possible solutions to problems but do not constitute advice.

In particular, no assurance can be given that the possible solutions will work in every situation or that loss of data will not occur. Always back-up data before trying to rectify a problem.



Next Meeting

and Christmas Party



Tuesday, 12 October 2000


Judy Jeffery’s at 6pm


Food and drink will be supplied




Meetings are now held at Judy Jeffery’s home:


(Upstairs Flat)

25 Bent Street, Greenwich

Meetings will usually be held

on the 2nd Tuesday

of each even numbered month

at 6pm





Search our Website

A search feature has been added to the User Group Website. There are now dozens of pages, so it is best to try the search facility to narrow in on the pages that cover the topic of interest. If possible, please use this before contacting a User Group member for help.


Address for AOAUG:

The Secretary

Australian Open Access Users Group

PO Box 5003

Greenwich 2065


User Group Homepage Address - CHANGED

(Note the www4 and aoaug NOT aoaugh).


E-mail Address for User Group Queries:

Michael has agreed for the OAUG members to send their queries by E-mail to him at his E-mail address.


DISPI Address

Vivaldistraat 18

5216 EL’s-Hertogenbosch




Technical Assistance

Free Advice for one-off queries. Extended use of this facility may incur a request for payment.





Tripos Group

104 Greenhill Road

Unley, SA, 5061

(08)8272 7555 Fx: (08)8272 7344.

Prog, App’s, Ss, Db



0419 839 839. Prog, App's, Db.

(03) 9525 8960




Seaforth 2092
Phone (02) 9949 3985

25 Junction Rd
Wahroonga 2076
Phone: (02) 9489 1042(H)

PO Box 5003

Greenwich 2065

Phone: (02) 9438 5982 (H)

Fax: (02) 9439 5982

Fax: (02) 9975 3966

Fax: (02) 9653 2436