脱!公務員ブログ

元地方公務員(市役所職員)のSEが、自身の体験を元に公務員の実態や役立つ知識や経験談、IT関連の技術的な情報、美味しいお店の情報、趣味(野球、音楽、ゲーム、麻雀)に関すること、私自身の波乱に満ちた人生経験について発信します。記事を読んだ後は少し知識が増えて、笑顔になれる(ネタ要素満載)、そんなブログですので是非お立ち寄り下さい♪

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

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

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

www.withdrawal-civilservice.com

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

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

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

必要なExcelの構成

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

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

入力用

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

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

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

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

A列にユーザーの所属部署、B列にユーザーの職員番号、C列に氏名、D列にADのユーザーログオン名、E列にユーザーのパスワード(初期)を入力します。

Excelシートのイメージ画像

入力用シートの画像

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

所属コードマスタ

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

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

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

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

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

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

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

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

Excelシートのイメージ画像

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

CSV出力用シート

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

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

work

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

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

A列 AccountName

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

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

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

B列 Name

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

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

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

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

C列 DisplayName

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

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

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

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

D列 password

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

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

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

F列 memo

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

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

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

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

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

G列~K列

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

  • 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)))

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

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

workシートのイメージ画像

シートのイメージ画像です。

workシートのイメージ画像

51行目まで関数をオートフィルでセットしています。

次は、CSVを出力するVBAについて解説します。

CSV出力するVBAのサンプルコード

作成したExcelに埋め込むVBAのサンプルコードです。

Excelシートの内容をCSV出力するVBAのサンプルコード

このVBAを実行することで、「CSV」シートの内容がCSV形式で出力されます。

Excelファイルと同じフォルダ配下の「CSV」フォルダにCSV出力されますので、事前に「CSV」という名前のフォルダを作成しておいてください。

このVBAを「入力用」シートの入力ボタンに埋め込むと、実行が手早くできます。

処理を実行すると、メッセージが表示されます。

以下は、出力されたCSVファイルの画像です。

あとがき

ExcelVBAでActiveDirectoryユーザー作成用のCSVファイルを生成する方法の記事でした。

このCSVで、下記の記事のスクリプトを実行すると効率よくADユーザーの作成が行えますので、参考にしてください。

www.withdrawal-civilservice.com

PowerShellで指定したフォルダ配下の全ファイルのファイルパスと、容量を降順にソートしてCSV出力するスクリプトの記事です。ファイル整理作業のお供にどうぞ。

www.withdrawal-civilservice.com

PowerShellでAzureADからメールエイリアスを抽出する方法の記事

www.withdrawal-civilservice.com