1. Sending "any" keys to an application

To program Ebuyer Office Suite, 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 Ebuyer Office Suite. You can do this from both inside Ebuyer Office Suite and outside Ebuyer Office Suite. 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 "Ebuyer Office Suite Write"

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

To access special characters, use the following key codes:

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 "Ebuyer Office Suite 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

Back to top

2. Write macro to delete previous/next word

Some word processors (including Ebuyer Office Suite Write v4) support "Delete Previous Word" when Ctrl-Backspace is pressed. Similarly "Delete Next Word" for Ctrl-Delete. This macro example shows how Ebuyer Office Suite 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

Back to top

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 "Ebuyer Office Suite 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.

Back to top

4. 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:

1. Switch the Form to Design Mode using View/Design
2. Select Insert/Macro Button and draw out the new button
3. Right-click and select Properties
4. Give the button some meaningful display text
5. Select the Macros tab
6. Select the "click" event (you don't have to but this is the normal way to use a button)
7. Select New Macro and give it a name
8. Save the form
9. In Database Manager, right-click over the new macro and select Edit
10. 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.

Back to top

5. 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:

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

2nd part - create a macro

1. 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.
2. 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

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

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

Back to top

6. 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)

  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

Back to top

7. Use Spreadsheet to create Excel xls

How to use automation of Ebuyer Office Suite Spreadsheet to create Excel files without user interaction.

There is no automation method to set a "Save As" file type in Ebuyer Office Suite. The simplest way around this problem is to create a template that is an xls file (ie a normal spreadsheet in Ebuyer Office Suite 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. Ebuyer Office Suite Write or Database, an Internet Explorer application (HTA) or a WScript file) but it would be easy to adjust to work with other languages.

Back to top

8. 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

1. Right-click over the desktop and select New/Shortcut
2. Select Browse and choose the abdata.exe program (it's normally in c:\program files\Ebuyer Office Suite)
3. Select Finish
4. Right-click over the database and select properties
5. 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\Ebuyer Office Suite 4\Abdata.exe" c:\MyDocs\MyData.adb#StartUp

Second Part: create the macro to open a form

1. Open the database containing the form you want to display
2. Select File/New/Macro and call it "StartUp"
3. 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

Back to top

9. 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

1. Create simple macro - select Tools/Macro/Macros
2. In the Macro name box, type TestMacro and click Create
3. Below the line "Sub TestMacro", enter:
   MsgBox "Hello"
4. Use the Windows menu to return to the spreadsheet
5. 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).

Back to top

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

Autoexecute macros, that start when a file is loaded, are not allowed in Ebuyer Office Suite 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 Ebuyer Office Suite application, opens the file Filename and runs the macro called Macroname that is contained within the file.

The second starts an Ebuyer Office Suite 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:

1. 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).
2. Click Browse and open up My Documents
3. Select INVOICE.ADB and OK.
4. 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"
5. Click Next and complete the shortcut

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

1. 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).
2. Click Browse, open up My Computer and browse to c:\program files\Ebuyer Office Suite
3. Select ABWRITE.EXE and OK.
4. After ABWRITE.EXE (and after any quotation marks), add a space and then #STARTUP so the line reads:
   "C:\Program Files\Ebuyer Office Suite\ABWRITE.EXE"   #STARTUP

Back to top

11. 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:

1. From Database Manager, select Create form using wizard
2. Pick any table (or create a dummy table - it does not matter)
3. Add one field and Finish the wizard
4. In Form Design mode, delete the field since all we want the user to see is buttons and text
5. Select Insert/Macro Button and draw out the button on the form
6. Right click over the button and select Properties
7. Set an appropriate caption and click on Macros
8. In the Events column, select Click
9. Click New Macro and give it a name to match its function
10. Repeat steps 5 to 9 as required
11. Save the form and in Database Manager, select Macros
12. Click on each of the new macros in turn and right-click and select Edit
13. You can rename the default "MacroName" to something more meaningful
14. 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.

Back to top

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

You can use the macro facilities in Write, Spreadsheet, Database and Photo Paint 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:

1. Select Tools/Macro/Macros
2. Enter a meaningful name, for example "StartSpreadsheet", in the Macro Name box and click Create
3. Edit the macro so it looks like this:

   Sub StartSpreadsheet
       prog = "abspread.exe"
       Set WshShell = CreateObject("WScript.Shell")
       WshShell.Exec(prog)
   End Sub

4. Close the macro Window and select Tools/Customize
5. Select the Macro tab and drag and drop the StartSpreadsheet macro to a toolbar (you can create a new toolbar if you prefer).
6. 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.

Back to top