ExcelVBAでActiveDirectoryユーザー作成用のCSVを生成する方法

本記事は、PowerShellを利用してActiveDirectoryのユーザーをCSVで一括作成するスクリプトに利用するためのCSVを、ExcelVBAで生成する方法について解説する記事です。

★PowerShellのスクリプトに関しての記事はこちら

www.withdrawal-civilservice.com

上のリンクの記事と合わせて利用することで、AD管理が大幅に効率化されます。

また、単にExcelVBAでCSVを作成するためのソースコードとして転用することも可能ですので、用途に合わせてご活用ください。

それではご説明させていただきます。

必要なExcelの構成
CSV出力するVBAのサンプルコード
あとがき

必要なExcelの構成

このExcelでは、各シート毎に役割を持たせて値を管理し、VBAでCSVを出力する仕様にしています。

各シート毎に必要な要素を解説します。

入力用

まずは作成するユーザーを定義するシートを作成します。

シート名を「入力用」とし、以下のように項目を定義します。

A列「所属部署」
B列「職員番号」
C列「氏名」
D列「アカウント名」
E列「パスワード」

Ａ列は後述する「所属コードマスタ」のシートから、所属部署を入力規則でリスト化すると入力の手間が省けて正確さが向上します。

Ａ列にユーザーの所属部署、Ｂ列にユーザーの職員番号、Ｃ列に氏名、Ｄ列にADのユーザーログオン名、Ｅ列にユーザーのパスワード（初期）を入力します。

★Excelシートのイメージ画像

入力用シートの画像

※右側のボタン（CSV出力実行）は後ほど解説するVBAを埋め込み、実行するためのものとしています。

所属コードマスタ

次に、ユーザーの所属部署（セキュリティグループ）を管理するシートを下記のように定義します。

シート名は「所属コードマスタ」としてください。

A列「所属CD」
B列「名称」
C列「スクリプト」
D列「セキュリティグループ1」
E列「セキュリティグループ2」
F列「セキュリティグループ3」
G列「セキュリティグループ4」
H列「セキュリティグループ5」

このシートは、組織内の所属部署とそれに対応するAD上のセキュリティグループを定義するためのシートとしています。

A列の「所属CD」は、人事システム等の部署コードと統一することが望ましいです。

B列の名称は部署の名称を入力します。

C列のスクリプトはログオンスクリプトでネットワークドライブを割り当てる場合、そのスクリプトファイルを定義するための項目です。（ログオンスクリプトを使用していない場合は、セルの値は空でＯＫ）

D列からH列は、それぞれAD上のセキュリティグループを入力します。ユーザーの所属部署に対応するセキュリティグループを入力してください。

★Excelシートのイメージ画像

所属コードマスタシートの画像

CSV出力用シート

シート名を「CSV」として作成し、シートには何も入力しないでよいです。

後述するVBAでCSVを出力するためのシートです。

work

このシートで、CSVに出力する項目と値について、関数で成形します。

シート名を「work」とし、各列は下記のように関数をセットしてください。

A列　AccountName

項目名を「AccountName」とし、2列目から下記のように関数をセットします。

=IF(入力用!D2="","",入力用!D2)

入力用のシートのD列から値を引用し、ADのログオンユーザーアカウントの値としています。

B列　Name

項目名を「Name」とし、２列目から下記のように関数をセットします。

=IF(入力用!C2="","",LEFT(入力用!D2,1)&入力用!C2)

この項目は、AD上のユーザー一覧での表示名です。

この関数の内容は、入力用シートのD列の先頭1文字のアルファベット＋日本語名としています。先頭の文字が不要（表示名とログオン名を同一にする）の場合は、A列の関数と同じ内容をセットしてください。

C列　DisplayName

項目名を「DisplayName」とし、２列目から下記のように関数をセットします。

=IF(入力用!C2="","",入力用!C2)

この項目は、ユーザーがログオンした際に表示されるユーザー名を定義しています。

例は「入力用」シートから「氏名」の項目を引用することとしていますが、ログオン名と同一にしたい場合は、A列と同じ内容で関数をセットしてください。

D列　password

項目名を「password」とし、２列目から下記のように関数をセットします。

=IF(入力用!E2="","",入力用!E2)

この項目では、「入力用」シートに入力したパスワードの値を、ADユーザーの初期パスワードとしています。

F列　memo

項目名を「memo」とし、２列目から下記のように関数をセットします。

=IF(入力用!B2="","",入力用!B2)

「入力用」シートの「職員番号」欄の値を引用しています。

この項目で、AD上の電話タブのメモ欄に職員番号をセットしています。

ユーザーを職員番号（社員番号）などで、一意に識別したい場合に便利です。

G列～K列

それぞれの列の項目名を「group1」～「group5」とし、２列目から下記のようにそれぞれの列に関数をセットします。

G列　=IF(入力用!A2="","",IF(ISBLANK(VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,3,0)),"",VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,3,0)))
H列　=IF(入力用!A2="","",IF(ISBLANK(VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,4,0)),"",VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,4,0)))
I列　=IF(入力用!A2="","",IF(ISBLANK(VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,5,0)),"",VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,5,0)))
J列　=IF(入力用!A2="","",IF(ISBLANK(VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,6,0)),"",VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,6,0)))
K列　=IF(入力用!A2="","",IF(ISBLANK(VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,7,0)),"",VLOOKUP(入力用!A2,所属コードマスタ!$B$2:$H$11,7,0)))

「所属コードマスタ」のセキュリティグループの値を引用しています。

こうすることで、所属部署に応じたセキュリティグループの定義が可能です。