Macros and Automation - Frequently Asked Questions

Sending "any" keys to an application
Write macro to delete previous/next word
How to use Programmable Forms
How to use macro buttons to link Database forms
Automating printing of envelopes
How to search and replace in Write
Use Spreadsheet to create Excel xls
Start Database and open a form
Run macros from Hyperlinks
How to run a macro as an application starts/Autoexecute macros
How to create a control panel (or switchboard) in Database
How to create a toolbar button to start external applications

1. Sending "any" keys to an application

To program Tesco Complete Office, use the OLE Automation features in combination with macros. This is described in the on-line help.

For functionality not supported by OLE Automation, you can use a general routine to send keyboard characters to Tesco Complete Office. You can do this from both inside Tesco Complete Office and outside Tesco Complete Office. The method is "SendKeys" and it's part of Windows Scripting Host that comes with Microsoft Windows (98 and later).

Example:

Sub SendAbilityKeys
Set WshShell = CreateObject("WScript.Shell")
WshShell.AppActivate "Tesco Complete Office Write"

WshShell.SendKeys "Any old plain text" ' Some text in the document

End Sub

To access special characters, use the following key codes:

Key	Key code
Alt	%
Ctrl	^
Shift	+
Esc	{ESC}
Ins	{INS}
Functions keys	{Fn} eg {F1}. {F12}
Home	{HOME}
End	{END}
Delete	{DEL}
Enter	{ENTER}

To use Alt, Ctrl or Shift in combination with other characters, simply precede the character with the Alt, Ctrl or Shift key code from the above table.

Examples

Sub SendAbilityKeys
Set WshShell = CreateObject("WScript.Shell")
WshShell.AppActivate "Tesco Complete Office Write"

WshShell.SendKeys "^P"                ' Displays the Print dialog (Ctrl-P)
WshShell.SendKeys "+{F3}%U{ENTER}"    ' Converts selected text to uppercase
WshShell.SendKeys "%{F4}Y"            ' Exit and answer "Yes" to save changes

End Sub

References

For general info on scripting, including latest downloads, see:
http://msdn.microsoft.com/scripting/

For more info on "Sendkeys" in particular see:
http://msdn.microsoft.com/scripting/windowshost/doc/wsMthSendKeys.htm

2. Write macro to delete previous/next word

Some word processors (including Tesco Complete Office Write v4) support "Delete Previous Word" when Ctrl-Backspace is pressed. Similarly "Delete Next Word" for Ctrl-Delete. This macro example shows how Tesco Complete Office can made to do the same and includes how to assign a keystroke to a macro.

1. Start Write

2. Select Tools/Macros/Macro Editor

3. Copy and Paste all the text below into the Macro editor screen.

Sub DelPrevWord
With ActiveDocument
    i = .Selection.End -1
    ch = .Text(i)

    While ch = 32
      i = i - 1
      If i < 0 Then
        ch = "0"
      Else
        ch = .Text(i)
      End If
    Wend
End With

With ActiveDocument.Selection
    .Start = i
    .Collapse abForw
    .Cut
End With

End Sub

Sub DelNextWord
With ActiveDocument
    i = .Selection.End
    ch = .Text(i)

    While ch = 32
      i = i + 1
      If i > .Text.Count Then
        ch = "0"
      Else
        ch = .Text(i)
      End If
    Wend
End With

With ActiveDocument.Selection
   .End = i
   .Collapse abBack
   .Cut
End With
End Sub

4. Click the Save button

5. Use the Window menu to return to your document (any document)

6. Select Tools/Customize and the Macro Shortcuts tab

7. Click on the Macro DelPrevWord

8. Click in the "Select New Shortcut" edit box and press Ctrl-Backspace

9. Click "Assign" button

10. Repeat 7 to 9 with DelNextWord macro and Ctrl-Delete

11. Save and test

3. How to use Programmable Forms

"Forms" are used in conjunction with macros so you can provide a custom user interface for your macro program. Here's how to create a simple form in Write:

Stage 1 - create the new form

1. Start Write and Select Tools/Forms Editor
2. Click on the "+" next to "Tesco Complete Office Write" and select "Global" (or you could select the current document).
3. Click on the "Add New" button.
4. Close the form manager dialog.

Stage 2 - edit the form

1. Right click over the form and select Properties.
2. Change the default name "Untitled" to "My Form" - click on "Title" enter "My Form" in the edit box and click Apply.
3. Close the Properties dialog.
4. Select the "AbilityButton" tool from the toolbox.
5. Drag the shape of the button on the form to create a new button.
6. Right-click over the new button and select "AbilityButton1" properties.
7. Set the caption to "Close" and OK the dialog.
8. Right-click over the button and select "ActiveScript"
9. From the Events drop-down, select "Click"
10. In the new function (the event handler for the button), enter the macro code to close a form:

Forms.CloseActive

11. Save the Form (use the Save button) and switch to the Write Window (leave the forms program running for now).

Stage 3 - write a macro to call the form

1. Select Tools/Macros and create a new macro, e.g. call it TestForms
2. Add the following code to display the Form:

Forms.ShowForm("My Form")

3. Run the macro to display the dialog (e.g. Right-click over macro code and select "Run").

Next steps 1 - Get data from a form

1. Go back to the forms editor and select View/Active Objects (or click on the "VB" button to toggle between script and design modes)
2. Select the "AbilityEditBox" tool from the toolbox and draw an edit box on the form.
3. Select View/Active Script and add the following line to the close button function so it looks like this:

Retval.Var("EditText") = AbilityEditBox2.Text
Forms.CloseActive

Note that "AbilityEditBox2" is the default ID for the edit box since it was the second control placed on the form - you can change this if you want to (right-click over edit box and select Properties).

4. Return to the Write macro and add an extra line so it looks like this:

Forms.ShowForm("My Form")
MsgBox ReturnVal.Var("EditText")

5. Run the macro, type something into the Edit box and select Close.

What's happening? The macro shows the form, the user interacts with the form and when the form is closed, a "special" variable called "EditText" is created to hold the value of the edit box. This is then referenced by the Write macro. You can make as many of these special variables as you want to pass data from a form to the main macro.

Next steps 2 - Investigate the other tools and events

All the other control types and events work in the same way as described as above. You can create complex and forms and put the bulk of the macro code in the actual form as well.

5. How to use macro buttons to link Database forms

How to link two Database forms together using macros and macro buttons.

Typical scenario is that you're browsing in database form view and you want a button to display a record from another (usually related) table:

Switch the Form to Design Mode using View/Design
Select Insert/Macro Button and draw out the new button
Right-click and select Properties
Give the button some meaningful display text
Select the Macros tab
Select the "click" event (you don't have to but this is the normal way to use a button)
Select New Macro and give it a name
Save the form
In Database Manager, right-click over the new macro and select Edit
Enter the following code (adjust to your own form / field names)

Sub OpenRelatedForm ()
id = ActiveDataObject.Fields("LinkFld1").Value
Set MyRelatedForm = DBForms.Open ("NameOfForm")
MyRelatedForm.Filter = "LinkFld2 = " & id
End Sub

Where:

LinkFld1 is the name of the field in your first form that contains the data you want to use to select the appropriate record in the second form (for example, if you had a customer table and an order table, this could be CustomerID).
LinkFld2 is the name of the field in the second form that relates to the first (for example, if you had a customer table and an order table, this could be CustomerID - i.e. often this will be the same actual name as LinkFld1).
NameOfForm is the name of the second form

An example of a very simple system of two linked tables can be downloaded here. Open the form EmailForm, browse and use the button "Get Customer". Code is in the GetCustomer macro.

6. Automating printing of envelopes

Problem - You've written a letter and now want to print only the address block on an envelope in a single step.

Solution - You need to create an envelope template and a macro to copy the address block. Follow these steps:

1st part - create a new template to match your envelope size:

Select File/New and select Template
Select File/Page Setup and pick the envelope size. Depending on the printer driver, check that the orientation is set correctly.
Select File/Save and call it "NormalEnvelope"

2nd part - create a macro

Select Tools/Macros. Set the "Macros In:" drop-down to "Application Macros". In the "Macro Name" box, type in a name like "CreateEnvelope". Click the Create button.
Copy and Paste the following code between the "Sub" and "End Sub" statements:

Dim s, i, n, pn, addstart, addblock, envDoc, TemplateName

TemplateName="NormalEnvelope"

s = ""
addstart = -1
pn = 0
For i = 0 To ActiveDocument.Text.Count
n = ActiveDocument.Text.Item(i)

If (addstart = -1) and (n > 48) Then
addstart = i
End If

If (addstart > -1) and (n = 13) and (pn = 13) Then
Exit For
End If
pn = n
Next

If addstart = -1 Then
MsgBox "Document is blank!"
Exit Sub
Else
addblock = ActiveDocument.Text.Mid(addstart, i - 1 - addstart)
End If

Set envDoc = Documents.Add(TemplateName)
envDoc.Text.Insert 0, addblock
If MsgBox("Do you want to print the envelope?", vbOKCancel) = vbOK Then
envDoc.Print 1, 1, 1, False
End If

3rd Part - run the macro

Click on the New file button
Type your letter with the address block at the top of the page
Select Tools/Macros and click on "CreateEnvelope". Tick the "Quick Macro" checkbox and and OK the dialog.
Now right-click over the letter and select Macros/CreateEnvelope.

When the macro is run next time, skip step 3.

7. How to search and replace in Write

Suppose you had a template and wanted to search and replace a "field holder" with some text of your own. For example, in your document you type *namefld* where you want a user name to appear and *addfld* where the address should appear. The following subroutine implements a search and replace in the active document:

Sub FindAndReplace(find, replace)
Set mydoc = ActiveDocument
stxt = mydoc.Text.Mid(0, mydoc.Text.Count)
br> i = InStr(stxt, find) - 1
n = Len(find)
If (i > 0) Then
mydoc.Text.Delete i, n
mydoc.Text.Insert i, replace
End If
End Sub

And the following subroutine calls the above sub with some real data:

Sub TestReplace
FindAndReplace "*namefld*", "Phil Roach"
FindAndReplace "*addfld*", "Ability, London, England"
End Sub

8. Use Spreadsheet to create Excel xls

How to use automation of Tesco Complete Office Spreadsheet to create Excel files without user interaction.

There is no automation method to set a "Save As" file type in Ability. The simplest way around this problem is to create a template that is an xls file (ie a normal spreadsheet in Tesco Complete Office and then saved using File/Save As and setting type to Excel). The following code shows how this can be done.

Sub CreateExcelFile
templateXLS = "c:\mydocs\template.xls"
newfile = "c:\mydocs\NewExcelFile.xls"

Set app = CreateObject("AbilitySpreadsheet.Application")

Set wb = app.Workbooks.Open(templateXLS)
Set ws = wb.Worksheets(0)
ws.Cells(1, 1).Value = "New Spreadsheet file"

wb.SaveAs(newfile)
app.Quit
End Sub

The above code is designed to run from any application that supports the VBScript language (e.g. Tesco Complete Office Write or Database, an Internet Explorer application (HTA) or a WScript file) but it would be easy to adjust to work with other languages.

9. Start Database and open a form

How to open a database from the desktop and automatically display a form

First Part: create a link on your desktop that runs the database application

Right-click over the desktop and select New/Shortcut
Select Browse and choose the abdata.exe program (it's normally in c:\program files\Tesco Complete Office)
Select Finish
Right-click over the database and select properties
Add the name of the database to the Target and include the name of the macro you are going to create. For example, suppose your database is called "MyData" and is a folder called "MyDocs" on your C: drive, the full Target should read:
"C:\Program Files\Tesco Complete Office 4\Abdata.exe" c:\MyDocs\MyData.adb#StartUp

Second Part: create the macro to open a form

Open the database containing the form you want to display
Select File/New/Macro and call it "StartUp"
Edit the macro so it opens the form. For example, suppose the form is called "CustomerForm", the macro should look like this:
Sub Startup ()
DBForms.Open("CustomerForm")
End Sub

Now you can close the database and test the link on the desktop - the result should be that the form you want is displayed

10. Run macros from Hyperlinks

A neat way to provide the "glue" that links documents to macro code, macros can be run using the hyperlink function.

Example using Spreadsheet

Create simple macro - select Tools/Macro/Macros
In the Macro name box, type TestMacro and click Create
Below the line "Sub TestMacro", enter:
MsgBox "Hello"
Use the Windows menu to return to the spreadsheet
In a spare cell, type:
=Hyperlink("#TestMacro", "Click here")

NB: You don't have to put in Click here - any description will do.

Now you can click the hyperlink "Click Here" to run the macro - a simple message box will appear.

The hash symbol (#) above is used to denote a macro (as opposed to a normal URL). The macro in this case is an Application level macro and is available for any Spreadsheet. It's also possible to run a macro in a document. In this case, the syntax is:

=HYPERLINK("documentname#macroname", "Click here")

For example, a user called Phil, with a macro called "startit" in a spreadsheet called myspread stored in my documents would enter:

=HYPERLINK("C:\Documents and Settings\Phil\My Documents\myspread.aws#startit", "Click here")

The HYPERLINK function can be used in a similar way in Write (use Insert/Hyperlink) and Database (create a calculated field to hold the function - see 12. Creating a Database of Links for an example).

11. How to run a macro as an application starts/Autoexecute macros

Autoexecute macros, that start when a file is loaded, are not allowed in Tesco Complete Office due to security problems - such functionality provides an easy route to spread viruses.

However, it is possible to create a shortcut that opens a file and runs a macro as the application loads.

The general format is:

ApplicationName FileName#Macroname
ApplicationName #Macroname

The first starts the Tesco Complete Office application, opens the file Filename and runs the macro called Macroname that is contained within the file.

The second starts an Tesco Complete Office application and runs the Application or Global level macro called Macroname.

Example 1. Suppose you want to open a database called INVOICE.ADB in My Documents and run a macro called STARTINV within the database:

Right click over your Desktop and select New/Shortcut (or, to put it inside the Start menu, right click on the Start button, select Open, select Programs and right-click and select New/Shortcut).
Click Browse and open up My Documents
Select INVOICE.ADB and OK.
After the name of the file, add #STARTINV so that line now reads something like:
"C:\Documents and Settings\Phil\My Documents\INVOICE.ADB#STARINV"
Click Next and complete the shortcut

Example 2. Start Write and run an Application macro called STARTUP:

Right click over your Desktop and select New/Shortcut (or, to put it inside the Start menu, right click on the Start button, select Open, select Programs and right-click and select New/Shortcut).
Click Browse, open up My Computer and browse to c:\program files\Tesco Complete Office
Select ABWRITE.EXE and OK.
After ABWRITE.EXE (and after any quotation marks), add a space and then #STARTUP so the line reads:
"C:\Program Files\Tesco Complete Office\ABWRITE.EXE" #STARTUP

12. How to create a control panel (or switchboard) in Database

Suppose you want a form that acts as a central point to the user and allows him to choose other forms or tables or run macros. Here is a guide on creating such a form:

From Database Manager, select Create form using wizard
Pick any table (or create a dummy table - it does not matter)
Add one field and Finish the wizard
In Form Design mode, delete the field since all we want the user to see is buttons and text
Select Insert/Macro Button and draw out the button on the form
Right click over the button and select Properties
Set an appropriate caption and click on Macros
In the Events column, select Click
Click New Macro and give it a name to match its function
Repeat steps 5 to 9 as required
Save the form and in Database Manager, select Macros
Click on each of the new macros in turn and right-click and select Edit
You can rename the default "MacroName" to something more meaningful
Add code to open a form or table or report. Examples:
- DBForms.Open("SomeFormName")
- Tables.Open("MyTableName")
- Reports.Open("Report1")
- Quit

Note that the above are all single line macros - you can make them as simple or complex as you want. The last of the above examples exits the database application.

You can now edit the form and add text (and pictures) to make it more user friendly.

See also 14. Start Database and open a form for details on how to open the form automatically.

13. How to create a toolbar button to start external applications

You can use the macro facilities in Write, Spreadsheet, Database and Photopaint to run external programs from toolbar buttons. The following example will show how to set this up to start Spreadsheet. All you need to change is the name of the exe (the prog= line) in step 3:

Select Tools/Macro/Macros
Enter a meaningful name, for example "StartSpreadsheet", in the Macro Name box and click Create
Edit the macro so it looks like this:
Sub StartSpreadsheet
prog = "abspread.exe"
Set WshShell = CreateObject("WScript.Shell")
WshShell.Exec(prog)
End Sub
Close the macro Window and select Tools/Customize
Select the Macro tab and drag and drop the StartSpreadsheet macro to a toolbar (you can create a new toolbar if you prefer).
Without closing the Customize dialog, right-click over the button you just added. Select Edit button image to give it your own icon or select Text Only or Image and Text to show the name of the macro as part of the button.

Browse Software

Tesco Software

Antivirus & Antispyware

Internet Security

PhotoRestyle

Personal Finance

Complete Office

Easy Record

Technical Support

Where to buy