How to Sort Schedule+ 7.0 Contacts List Using VB for Apps (150002)

MORE INFORMATION

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft Support professionals can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

The following Visual Basic for Applications macro connects to Schedule+ (which must be running before you start the macro), and then copies the Contacts list to a Microsoft Excel worksheet. The Contacts list is sorted on the worksheet using built-in sort routines. In the below sample, the sort only occurs on the first column. The macro deletes the original Contacts list in Schedule+, and then writes the data back from the worksheet into the Contacts list.

To enter the macro code, do the following:

1. Start Microsoft Excel.
2. On the Insert menu, point to Macro, and then click Module.
3. Enter the macro code on the module sheet.

In this example, the macro sorts the firstname, surname, and home phone number. The macro assumes a contact list similar to the following:

 Firstname     Surname     Homephone
 ---------     -------     ---------
    D             D            4
    B             B            2
    A             A            1
    C             C            3
				

'====================================================================
'Macro SortScheduleContacts
'This macro only sorts the firstname, surname, and homephone fields
'and will need to be expanded to cover all additional contact fields
'====================================================================

   Sub SortScheduleContacts()
      Dim objSched, objTable, objItem As Object
      Dim i, j As Integer

      'Create the Schedule+ object
      Set objSched = CreateObject("scheduleplus.application.7")

      'Creates the contacts database object
      Set objTable = objSched.ScheduleSelected.Contacts

      'Moves to first row of contact list
      objTable.Reset
      For i = 1 To objTable.Rows
         'Get item in contacts database
         Set objItem = objTable.Item
         'Copy contact details to worksheet
         Cells(i, 1).Value = Format(objItem.FirstName)
         Cells(i, 2).Value = Format(objItem.lastname)
         Cells(i, 3).Value = Format(objItem.phonehome)
         'Go to Next item
         objTable.skip
      Next

      'Sorts the contact list on worksheet (sorts on column A)
      Range("a1").CurrentRegion.Select
      Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:= _
      xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
      xlTopToBottom

      'Deletes all items on schedule contact list
      objTable.Reset
      For i = 1 To objTable.Rows
      Set objItem = objTable.Item
         objTable.deleteitem (objItem.itemid)
         objTable.skip
      Next

      'Copies sorted contact list from worksheet to Schedule+ 95
      Range("a1").CurrentRegion.Select
      j = Selection.Rows.Count
      For i = j To 1 Step -1
         'Creates a blank new entry in the table
         Set objItem = objTable.New
         'Creates new entries in the contacts database
         objItem.SetProperties FirstName:=Cells(i, 1)
         objItem.SetProperties lastname:=Cells(i, 2)
         objItem.SetProperties phonehome:=Cells(i, 3)
      Next

      'Delete objects
      Set objItem = Nothing
      Set objTable = Nothing
      Set objSched = Nothing
   End Sub
				

For more information about programming Schedule+, see the Microsoft Developer Network compact disc.