Novell Controls for ActiveX and Microsoft Excel: Migrating Directory Entries between NDS and LDAP Directories Using Worksheets
Articles and Tips: article
Senior Software Engineer
Novell Product Group, India
bvijayakumar@novell.com
Vidula K
Technical Writer
Novell Product Group, India
kvidula@novell.com
01 Aug 2000
This AppNote describes how to migrate user and person data from Novell Directory Services (NDS) to LDAP directories or vice-versa using Novell Controls for ActiveX with the help of a sample program. The unique feature of this sample program is its implementation in Microsoft Excel.
This article is targeted at developers with fundamental Visual Basic programming knowledge.
Introduction
This article demonstrates the ease and flexibility provided by Novell's ActiveX Controls for developing various kinds of network applications in an Integrated Development Environment (IDE) that supports ActiveX technology, including Visual Basic, Delphi, PowerBuilder, C++, Visual C++, and Microsoft Office. The controls (21 as of this printing) provide diverse features including full access and search mechanism to Novell Directory Services (NDS), Internet (LDAP) directories, and administration capabilities for NetWare servers, catalog objects, print queues, printers, volumes, and so on.
The complete set of controls can be downloaded from http://developer.novell.com/ndk/ocx.htm. Documentation in the form of Windows Help is included in the download.
Complete documentation can also be viewed at http://developer.novell.com/ndk/doc.htm.
NDS tutorials are available at http://developer.novell.com/ndk/demo.htm and provide a good source of information for programming fundamentals. Check out the tutorial titled "Programming NDS with the Novell Controls for ActiveX and Visual Basic."
Additionally, you can read the series of articles by Morgan Adair in Developer Notes. The articles begin in June 1999 with "Programming with the Novell Controls for ActiveX and Visual Basic: Getting Started" through to the last article of the series "Reading and Writing NDS Stream Fields" in the March 2000 issue. http://support.novell.com/techcenter/articles/dnd19990601.html, and http://support.novell.com/techcenter/articles/dnd20000301.html
Features of the Sample Program
The sample program in this article is developed using Microsoft Excel Visual Basic for Applications. It uses Novell Directory Control, Novell Internet Directory Control, and Novell Network Selector Control.
This sample program allows you to retrieve user or person information such as surname, e-mail, and telephone number from NDS or LDAP directories into an Excel worksheet. You can migrate these entries to any directory. Before migration you can add or modify entry information in the worksheet, if required.
Microsoft Excel exposes its workbooks, worksheets, and cells through programmable objects. You can program to these objects using the Microsoft Excel Visual Basic for Applications. When Novell's Directory and Internet Directory Control are used in Excel, the worksheet is enabled with the power of a directory.
To manage directory operations in the sample program, the menu bar in Microsoft Excel is customized to include a new menu named "Directory." This makes the tasks intuitive and simple.
Note: It is important to know the terminology differences between LDAP and NDS. The following table compares some of the terms and attributes used by NDS and LDAP.
|
LDAP Term |
NDS Term |
|
Entry |
Both Object and Entry |
|
Attribute |
Attribute |
|
Person object |
User object |
|
LDAP Attribute |
NDS Attribute |
|
commonName |
CN |
|
fullName |
Full Name |
|
description |
Description |
|
facsimileTelephoneNumber |
Facsimile Telephone Number |
|
|
Internet EMail Address |
|
postalAddress |
Postal Address |
|
postalCode |
Postal Code |
|
sn, surname |
Surname |
|
telephoneNumber |
Telephone Number |
|
title |
Title |
For extensive information on NDS and LDAP, see http://developer.novell.com/ndk/cldap.htm.
Controls Used in this Sample Program
The following controls are used in this sample program:
Directory Control (NWDir.ocx)
Internet Directory Control (NWIDir.ocx)
Selector Control (NWSelect.ocx)
Setup Requirements
Windows 95/98/NT/2000 operating system, the appropriate Novell Client for the operating system, Microsoft Office 97/2000, and Novell Controls for ActiveX.
Sample Program
This sample program is created through the following steps:
Customizing the workbook
Creating UserForms
Enabling migration
To edit a Visual Basic Application in Excel, select the Visual Basic Editor from Tools > Macro > Visual Basic Editor or by pressing Alt+F11.
The Project Explorer contains a list of the projects and the Excel objects contained in each project as shown in Figure 1.
Figure 1: The Project Explorer.
Customizing the Workbook
To manage directory operations in the sample program, a custom menu named "Directory" is added to the Excel menu bar. It contains four menu items to load and migrate data. The "Directory" menu is implemented in such a way that it is added when the workbook is opened and removed when the workbook is closed. The Workbook object's "Open" and "BeforeClose" events occur when the workbook is opened and closed respectively.
To add the new menu to the Excel menu bar:
Double-click the "ThisWorkbook" object in the Project Explorer.
In the ThisWorkbook (code) window, select the "Workbook" object from the Object box and "Open" event from the Events box.
Enter the following code.
' Workbook_Open - Event
' Called when the workbook is opened
Private Sub Workbook_Open()
Dim cstmDir, cstmMenuItem As CommandBarControl
' Add the Directory menu to the Excel worksheet menu bar
Set cstmDir = Application.CommandBars("Worksheet Menu Bar").Controls.Add _
(Type:=msoControlPopup, Before:=9, Temporary:=True)
cstmDir.Caption = "Director&y"
' Add individual menu items to the Directory menu
' The menu items to be added are,
' 1) Load Data from NDS
' 2) Load Data from LDAP
' 3) Migrate Data to NDS
' 4) Migrate Data to LDAP
Set cstmMenuItem = cstmDir.Controls.Add(Type:=msoControlButton)
cstmMenuItem.Caption = "Load Data from &NDS"
' The event function implemented in Sheet1, will be called
' when a menu item is selected
cstmMenuItem.OnAction = "Sheet1.OnNDSLoad"
Set cstmMenuItem = cstmDir.Controls.Add(Type:=msoControlButton)
cstmMenuItem.Caption = "Load Data from &LDAP"
cstmMenuItem.OnAction = "Sheet1.OnLDAPLoad"
Set cstmMenuItem = cstmDir.Controls.Add(Type:=msoControlButton)
cstmMenuItem.Caption = "Migrate Data to N&DS"
cstmMenuItem.OnAction = "Sheet1.OnNDSMigrate"
Set cstmMenuItem = cstmDir.Controls.Add(Type:=msoControlButton)
cstmMenuItem.Caption = "Migrate Data to LDA&P"
cstmMenuItem.OnAction = "Sheet1.OnLDAPMigrate"
End Sub
To remove the Directory menu when the workbook is closed:
Select "BeforeClose" from the Events box.
Enter the following code.
' Workbook_BeforeClose - Event
' Called before the workbook closes
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ctrlsDir As CommandBarControls
Dim cstmDir As CommandBarControl
If Not Cancel Then 'If the close operation is not cancelled
Set ctrlsDir = Application.CommandBars("Worksheet Menu Bar").Controls
' Find the "Directory" menu and remove it
For Each cstmDir In ctrlsDir
If cstmDir.Caption = "Director&y" Then
cstmDir.Delete
End If
Next
End If
End Sub
Creating UserForms
Customized UserForms or dialog boxes are included in the sample program. These forms are used for user interaction and to get the required inputs.
The sample program uses three UserForms:
The Load Data from NDS and Load Data from LDAP form loads directory information from NDS and LDAP respectively into the worksheet.
The Migrate Data to NDS form migrates data from the worksheet to NDS.
The Migration Data to LDAP form migrates data from the worksheet to LDAP directories.
To create a new UserForm:
Click Insert > UserForm.
To display the Toolbox click View > Toolbox. The Toolbox is displayed.
To add Novell's ActiveX Controls to the Toolbox, right-click the Toolbox > click Additional Controls. From the Additional Controls dialog box, select Novell Directory Control, Novell Internet Directory Control, and Novell Selector Control from the list of controls > click OK.
Design the UserForm as required.
Enabling Migration
Enabling migration is divided into two sections:
Migrating data to LDAP
Migrating data to NDS
Migrating Data to LDAP
Directory entries from NDS or LDAP can be loaded into the worksheet using the form shown in Figure 2. To display the Load Data form select Directory > Load Data from NDS or Load Data from LDAP. While creating the Field Names list box, set the MultiSelect property to fmMultiSelectExtended. This allows sections of multiple field names.
Figure 2: Load data form.
Declare a public flag variable, which will indicate whether the form is to be displayed for NDS or LDAP. The following global declarations are to be included to the frmLoad form.
Public fNDSLoad As BooleanOption Base 0
The declaration Option Base 0 should be declared in the beginning of each form.
The Load and Migrate forms should be displayed when the menu items are selected. The following procedures should be implemented in Sheet 1 of the Worksheet object to show the forms when the menu items are selected.
' OnNDSLoad - Procedure
' Called when the "Load Data from NDS"
' menu item is selected from the "Directory" menu
Sub OnNDSLoad()
frmLoad.fNDSLoad = True
frmLoad.Show
End Sub
' OnLDAPLoad - Procedure
' Called when the "Load Data from LDAP"
' menu item is selected from the "Directory" menu
Sub OnLDAPLoad()
frmLoad.fNDSLoad = False
frmLoad.Show
End Sub
' OnNDSMigrate - Procedure
' Called when the "Migrate Data to NDS"
' menu item is selected from the "Directory" menu
Sub OnNDSMigrate()
frmNDSMigrate.Show
End Sub
' OnLDAPMigrate - Procedure
' Called when the "Migrate Data to LDAP"
' menu item is selected from the "Directory" menu
Sub OnLDAPMigrate()
frmLDAPMigrate.Show
End Sub
Enter the code given below for the Activate event. The Full Name text box is initialized with the default full name using the Directory Control's FullName property.
' UserForm_Activate - Event
' Called when this form becomes the active window
Private Sub UserForm_Activate()
' Display the dialog caption based on the mode,
' which can be NDS or LDAP
If fNDSLoad Then
frmLoad.Caption = "Load Data from NDS"
txtFullName.Text = NWDir1.FullName
cmdBrowse.Enabled = True
Else
frmLoad.Caption = "Load Data from LDAP"
' LDAP entries cannot be browsed through
' the Selector dialog. So disable the Browse button
cmdBrowse.Enabled = False
End If
End Sub
The Novell Network Selector Control is a visual control used to browse and select the desired tree and context. The FullName specifies the location of the User or Person object in an NDS or LDAP directory. For example, consider NDS:\\APPS_AXJB\novell\docs\peter. Here, the tree name is APPS_AXJB, the context is docs.novell and the user name is peter.
Figure 3: The Selector dialog.
The Browse button displays the Selector Dialog using the following code.
' cmdBrowse_Click - Event
' Called when the Browse button is clicked
Private Sub cmdBrowse_Click()
On Error GoTo errExit
NWSelect1.FullName = txtFullName.Text
' Show the selector dialog to select the NDS Tree and context
If NWSelect1.Show Then
' If the selection is successful, set the selected fullname
' in the Text Box
txtFullName.Text = NWSelect1.FullName
End If
Exit Sub
errExit:
MsgBox Str(Err.Number) + Err.Description
End Sub
When you switch to the Field Names list box, all the field names available for the User or Person object should be listed. Becaues this list box supports multiple-selection, the user can select all the required fields. The AfterUpdate event occurs whenever the data in a text box changes. The following code is required to initialize the Field Names list box. The NWLayoutDescription object of Directory and Internet Directory Controls are used to get the layout description of an object in the directory. Through this object, the layout of NDS User object and LDAP Person object is retrieved. The NWLayoutDescription.Fields property returns the NWFieldDescriptions collection, which contains the field information. The individual NWFieldDescription objects in this collection will contain the field names and types.
' txtFullName_AfterUpdate - Event
' Called when the FullName Text Box is set with a full name
' and the user presses the <TAB> key
Private Sub txtFullName_AfterUpdate()
Dim ndsField As NWDirLib.NWFieldDescription
Dim ndsFields As NWDirLib.NWFieldDescriptions
Dim ldapField As NWIDirLib.NWFieldDescription
Dim ldapFields As NWIDirLib.NWFieldDescriptions
Dim ndsUser As NWDirLib.NWLayoutDescription
Dim ldapUser As NWIDirLib.NWLayoutDescription
On Error GoTo errExit
lstFields.Clear
If fNDSLoad Then ' Fill the List Box with NDS field names
NWDir1.FullName = txtFullName.Text
Set ndsUser = NWDir1.Layouts.Item("User")
' Get the fields of the NDS User object
Set ndsFields = ndsUser.fields
' List all the String fields
For Each ndsField In ndsFields
If ndsField.TypeName = "String" Then
lstFields.AddItem ndsField.Name
End If
Next
Else
NWIDir1.FullName = txtFullName.Text
Set ldapUser = NWIDir1.Layouts.Item("person")
' Get the fields of the LDAP person object and list them
Set ldapFields = ldapUser.fields
For Each ldapField In ldapFields
lstFields.AddItem ldapField.Name
Next
End If
Exit Sub
errExit:
MsgBox Str(Err.Number) + Err.Description
End Sub
The Load button populates the worksheet with the directory entries and field values. Each entry is filled in one row, and the first row displays the selected field names. The Directory Control is set with the filter "User" and the Internet Directory Control is set with "Person" as the filter to retrieve user information. The NWEntries collection will contain the directory entries. From each NWEntry object in this collection, call the GetFieldValue method to get the selected field values.
' cmdLoad_Click - Event ' Called when the Load button is clicked Private Sub cmdLoad_Click() On Error GoTo errExit Dim ndsEntry As NWDirLib.NWEntry Dim ndsEntries As NWDirLib.NWEntries Dim ldapEntry As NWIDirLib.NWEntry Dim ldapEntries As NWIDirLib.NWEntries Dim selFields() As String Dim i, j, count, fldCount As Integer Dim cell As Range Dim varValue As Variant count = lstFields.ListCount j = 0 ' Store the field names selected from the List Box, ' in the selFields array For i = 0 To count - 1 If lstFields.Selected(i) Then ReDim Preserve selFields(j + 1) selFields(j) = lstFields.List(i) j = j + 1 End If Next fldCount = j ' Clear the existing values in the worksheet Sheet1.Cells.ClearContents ' Highlight the first row, and display the field names there ' as column headings Set cell = Sheet1.Cells(1) cell.Font.Size = 12 cell.Font.Color = RGB(0, 64, 255) cell.Value = "CN" For i = 0 To fldCount - 1 Set cell = Sheet1.Cells(i + 2) cell.Font.Size = 12 cell.Font.Color = RGB(0, 64, 255) cell.Value = selFields(i) Next If fNDSLoad Then ' NDS Load NWDir1.FullName = txtFullName.Text NWDir1.Filters = "User" ' In NDS load User objects Set ndsEntries = NWDir1.entries ' Start from the second row, because the first row ' contains the field names as column headings i = 2
' Each row in the worksheet will contain one directory entry
For Each ndsEntry In ndsEntries
Set cell = Sheet1.Cells(i, 1)
cell.Value = ndsEntry.ShortName
' For each entry, the values of the selected fields
' will be filled in the columns
For j = 0 To fldCount - 1
varValue = ndsEntry.GetFieldValue(selFields(j), "", True)
Set cell = Sheet1.Cells(i, j + 2)
cell.Value = varValue(0)
Next
i = i + 1
Next
Else ' LDAP Load
NWIDir1.FullName = txtFullName.Text
NWIDir1.Filter = "person" ' In LDAP load person objects
Set ldapEntries = NWIDir1.entries
i = 2
For Each ldapEntry In ldapEntries
Set cell = Sheet1.Cells(i, 1)
cell.Value = ldapEntry.ShortName
For j = 0 To fldCount - 1
varValue = ldapEntry.GetFieldValue(selFields(j), True, "")
Set cell = Sheet1.Cells(i, j + 2)
' If the value returned is an array of strings,
' show the first value
If VarType(varValue) = vbArray + vbString Then
cell.Value = varValue(0)
Else
cell.Value = varValue
End If
Next
i = i + 1
Next
End If
frmLoad.Hide
Exit Sub
errExit:
MsgBox Str(Err.Number) + Err.Description
End Sub
Before the entries in the worksheet are migrated to an LDAP directory, the user must be authenticated to the target server. Design the form as shown in Figure 4 to gather authentication details. The user must have administrator privileges on the target server to be able to migrate the entries.
Figure 4: Migrate to LDAP form.
When the Migrate button is clicked, the LDAP directory updates the directory entries and their field values given in the worksheet. Since the format of the field names is different in LDAP and NDS, modify the field names displayed in the first row accordingly before you start the migration. For example, the 'Internet Email Address' field in NDS is equivalent to 'mail' in LDAP. The Add method in the NWEntries collection adds a new entry to the directory. For each NWEntry object, call the SetFieldValue method for the fields in the worksheet and finally call Update to update the fields to the selected directory. Refer to the "Features of the Sample Program" section for a list of NDS and LDAP field names.
' cmdMigrate_Click - Event
' Called when the Migrate button is clicked
Private Sub cmdMigrate_Click()
On Error GoTo errExit
Dim ldapEntry As NWIDirLib.NWEntry
Dim ldapEntries As NWIDirLib.NWEntries
Dim i, j, rowCount, colCount As Long
Dim entries, entry, fields, field As Range
Dim cell As Range
NWIDir1.FullName = txtFullName.Text
NWIDir1.UserName = txtUserName.Text
NWIDir1.Password = txtPassword.Text
Set ldapEntries = NWIDir1.entries
' All the entry names are in Column A
Set entries = Sheet1.Range("A:A")
' All the field names are in Row 1
Set fields = Sheet1.Range("1:1")
rowCount = entries.Rows.count
For i = 2 To rowCount
' Get all the entry names from the first Column
Set entry = entries(i, 1)
If Not IsEmpty(entry) Then
' Add a new person object to the given LDAP search base
Set ldapEntry = ldapEntries.Add("person", entry.Value)
colCount = fields.Columns.count
' For each entry, get the field values from its
' corresponding row
For j = 2 To colCount
' Get the field names from the first row
Set field = fields(1, j)
If IsEmpty(field) Then Exit For
' Get the field value from the worksheet cell
' in the entry's row and field's column
Set cell = Sheet1.Cells(entry.Row, field.Column)
' Set the value to the directory entry's field
ldapEntry.SetFieldValue field.Value, cell.Value
Next
' Update the directory entry
ldapEntry.Update
Else
' End of entry names in Column A
Exit For
End If
Next
frmLDAPUpdate.Hide
Exit Sub
errExit:
MsgBox Str(Err.Number) + Err.Description
End Sub
Migrating Data to NDS
After looking into how user or person information is retrieved to the worksheet, we will see how data is migrated to NDS. The Migrate Data to NDS form is shown in Figure 5.
Figure 5: Migrate to NDS form.
The Browse button displays the Selector dialog for the desired tree and context to be updated.
' UserForm_Activate - Event
' Called when this form becomes the active window
Private Sub UserForm_Activate()
txtFullName.Text = NWDir1.FullName
End Sub
' cmdBrowse_Click - Event
' Called when the Browse button is clicked
Private Sub cmdBrowse_Click()
On Error GoTo errExit
NWSelect1.FullName = txtFullName.Text
' Show the selector dialog to select the NDS Tree and context
If NWSelect1.Show Then
' If the selection is successful, set the selected fullname
' in the Text Box
txtFullName.Text = NWSelect1.FullName
End If
Exit Sub
errExit:
MsgBox Str(Err.Number) + Err.Description
End Sub
To migrate the directory entries to NDS, add the following code. The code is similar to the Migrate Data to LDAP section.
' cmdMigrate_Click - Event
' Called when the Migrate button is clicked
Private Sub cmdMigrate_Click()
On Error GoTo errExit
Dim ndsEntry As NWDirLib.NWEntry
Dim ndsEntries As NWDirLib.NWEntries
Dim i, j, rowCount, colCount As Long
Dim entries, entry, fields, field As Range
Dim cell As Range
NWDir1.FullName = txtFullName.Text
Set ndsEntries = NWDir1.entries
' All the entry names are in Column A
Set entries = Sheet1.Range("A:A")
' All the field names are in Row 1
Set fields = Sheet1.Range("1:1")
rowCount = entries.Rows.count
For i = 2 To rowCount
' Get all the entry names from the first Column
Set entry = entries(i, 1)
If Not IsEmpty(entry) Then
' Add a new User object to the NDS context
Set ndsEntry = ndsEntries.Add("User", entry.Value)
colCount = fields.Columns.count
' For each entry, get the field values from its
' corresponding row
For j = 2 To colCount
' Get the field names from the first row
Set field = fields(1, j)
If IsEmpty(field) Then Exit For
' Get the field value from the worksheet cell
' in the entry's row and field's column
Set cell = Sheet1.Cells(entry.Row, field.Column)
' Set the value to the directory entry's field
ndsEntry.SetFieldValue field.Value, cell.Value
Next
' Update the directory entry
ndsEntry.Update
Else
' End of entry names in Column A
Exit For
End If
Next
frmNDSUpdate.Hide
Exit Sub
errExit:
MsgBox Str(Err.Number) + Err.Description
End Sub
Conclusion
Isn't it simple? This sample program demonstrates the versatility provided by Novell's ActiveX Controls for developing directory-enabled solutions of your choice. Diverse applications can be developed in Microsoft Excel using the controls.
For a wide range of sample applications created using Novell Controls for ActiveX see:
http://support.novell.com/techcenter/articles/ana20000807.html
http://developer.novell.com/ndk/doc/samplecode/ocx_sample/index.htm
* Originally published in Novell AppNotes
Disclaimer
The origin of this information may be internal or external to Novell. While Novell makes all reasonable efforts to verify this information, Novell does not make explicit or implied claims to its validity.