サーバー名が変更になっていた 解決法(FileSystemObject + VBProjectプロパティ)
こんにちは。
派犬事務員のコロ子です。
前回、サーバーの名前が変わって困った!の記事を書いたら、たくさんの方からいろいろな素晴らしいらしいアイディアを頂きました。どうもありがとうございます。
どれも自力で絶対たどり着けないものばかり。
みんな天才過ぎ!!!
目次
FileSystemObjectオブジェクトで現在のサーバー名(ドライブ名)を取得する
まずは、yuricks7さんより頂いたアイディアでFSO(FileSystemObjectオブジェクト)を使って現在のサーバー名(ドライブ名)を取得する。
ドライブ名が取得できるGetDriveNameメソッドを発見!
新しいモジュールを作成する
新しいジュールを追加し、名前を「サーバーの名前」にする。
Function関数を作成する
「サーバーの名前」モジュールにサーバーの名前を返すFunction関数を作成する。
コードはこんな感じ。
Function ServerName() Dim FSO As Object Set FSO = CreateObject("Scripting.FileSystemObject") Dim buf As String buf = FSO.GetDriveName(ThisWorkbook.Path) Set FSO = Nothing ServerName = buf End Function
FileSystemオブジェクト.GetDriveName(パス名)
ドライブ名が取得できる。
あとは古いサーバー名をベタ打ちしているところをServerNameに置換する。
"古いサーバー名\●●●\●●●●\●●●" ↓ ServerName & "\●●●\●●●●\●●●"
モジュールごとに置換すれば漏れなく変更できる。
これなら今後サーバー名が変更になってもそのまま使える。
Great!!
VBProjectプロパティでマクロでマクロを操作できる。(VBAでVBEを操作する)
では、この「サーバーの名前」モジュールをどうやって既存のファイルに入れてい行くかが問題。
・マクロ入りファイルを開く。
・モジュールを追加する。
・モジュールごとにパスのサーバー名を置換する。
という作業を手で行うとなると結構大変。既存のファイル全部で何個あるのー!?
そこで、@すでぃさんより外部モジュールを教えて頂いたのでこれを使用して上記の工程をVBAでやってみよう!
外部モジュールを作成する。
まずは、開発タブのセキュリティより「VBAのプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れる。
別途、VBE操作用のマクロ入りファイルを用意する。
そこに先ほどの「サーバーの名前」モジュールを作成する。
「サーバーの名前」モジュールを右クリックし、ファイルのエクスポートを選択するとサーバーの名前.basが作成される。(.basは標準モジュールの拡張子)
この作業をマクロを使ってやってみる。
モジュールを操作するには
Workbook.VBProject.VBComponent オブジェクト
を使用する。
モジュールをエクスポートする
VBComponent オブジェクト. Export ファイル名.bas(パス含む)
ファイル名.basファイルが作成される。
同様にインポートもできる
モジュールをインポートする
VBComponent オブジェクト. Import ファイル名.bas(パス含む)
モジュール内のコードを変更する
モジュールに対する操作は、行を追加したり、削除したり、行ごとになるらしい。
モジュールの中身を直接置換する事はできないみたいなので、モジュールの中身を取り出して置換する。
①モジュール内の行数を調べる
VBComponent オブジェクト. CodeModule.CountOfLines
行数が戻る(数値型)
②モジュール内のコードを取得(開始行から指定行数分)
VBComponent オブジェクト .CodeModule.Lines(開始行, 指定行数)
文字列が戻る
③モジュールの指定行を削除する(開始行から指定行数分)
VBComponent オブジェクト .CodeModule.DeleteLines 開始行, 指定行数
④モジュール内にコードを追加する
VBComponent オブジェクト .CodeModule.AddFromString 文字列
コードを書いてみる
では、上記のことを踏まえてコードを書いてみる。
Sub MacroChangeProcess() Dim WorkbookName As String Application.ScreenUpdating = False 'サーバーの名前.basを作成する ThisWorkbook.VBProject.VBComponents("サーバーの名前").Export _ ThisWorkbook.Path & "\サーバーの名前.bas" '同じフォルダ内の全ての.xlsmファイルが対象 WorkbookName = Dir(ThisWorkbook.Path & "\*.xlsm") Do Until WorkbookName = "" If WorkbookName <> ThisWorkbook.Name Then Workbooks.Open ThisWorkbook.Path & "\" & WorkbookName 'サーバー名を置換し、「サーバーの名前」モジュールを追加する Call Replace_and_Add(WorkbookName) Workbooks(WorkbookName).Close SaveChanges:=True End If WorkbookName = Dir() Loop 'サーバーの名前.basを削除する Kill ThisWorkbook.Path & "\サーバーの名前.bas" Application.ScreenUpdating = True End Sub
Sub Replace_and_Add(WorkbookName As String) Dim Mdl As Variant '全てのモジュールに対して処理を行う For Each Mdl In Workbooks(WorkbookName).VBProject.VBComponents If Mdl.Name = "サーバーの名前" Then 'すでにあったら削除する Workbooks(WorkbookName).VBProject.VBComponents.Remove Mdl Else 'サーバー名を置換する With Workbooks(WorkbookName).VBProject.VBComponents(Mdl.Name) 'モジュール内の行数を取得 Dim cnt As Long cnt = .CodeModule.CountOfLines 'モジュールの1行目からcnt行までの内容を取得 Dim Contents As String Contents = .CodeModule.Lines(1, cnt) 'サーバー名を置換する Contents = Replace(Contents, """古いサーバー名", "ServerName & """) 'モジュールの1行目からcnt行までを削除 .CodeModule.DeleteLines 1, cnt 'モジュールに置換した内容を入れる .CodeModule.AddFromString Contents End With End If Next Mdl 'サーバーの名前モジュールをインポートする Workbooks(WorkbookName).VBProject.VBComponents.Import _ ThisWorkbook.Path & "\サーバーの名前.bas" End Sub
できた!
同一フォルダ内の全ての.xlsmファイルに対して処理を行う。
手で一つづつポチポチ作業するより格段に速い。というか爆速。
難点は対象のフォルダにいちいちこのマクロ入りファイルをコピーして実行しなくてはいけない。
理想は作業フォルダの一番上の階層にこのファイルを置いて、配下のフォルダ全部に対して処理を行うようにしたいけど難しそう。再帰処理みないのを使うのかな?
【お詫び】
このようなコードを作成しましたが、外部モジュールについて知らなかったため調べるのに時間がかかり、急ぎのため手で変更しました。
変更処理が終わって落ち着いてから作成しました。
作成するのにかなり時間がかかりました。サクサク作れるようになりたいです。
ありがとうございます!
いろいろな方から貴重なアドバイスを頂いたので、無くなってしまわないように記しておきます。
yuricks7さんより
素晴らしい!!
「¥¥192.168.100.100」←みたいな感じでIPアドレスでサーバーを指定することもできます。これもファイル置き場をコロコロ変えられると結局直さないといけないのですが・・・😅
— きぬあさ (@kinuasa) July 1, 2019
頻繁に変わるようならファイルの場所を直書きせずに、ファイル選択ダイアログ(&初期フォルダ指定)を使う手もあります😊
IPアドレスで指定できるんだ!知らなかった。
ファイル選択ダイアログならサーバーどころかフォルダ構造が変更になってもOK!
サーバーパスの定数を外部モジュールに書いておいて、すべてのマクロでその外部モジュールを読み込む事を徹底しておけば、パスの変更があっても外部モジュールを更新するだけで済むのではと思います。
— すでぃ (@thdy5) July 2, 2019
外部モジュール、初めて知りました。これ凄いです!!
読み込む外部モジュールのパスどうやって変更するかに悩んだので、今回はモジュールを操作する処理にしました。
相対参照で切り抜けられる場合はThisworkbook.Pathで。相対参照できない場合は(こっちの方が多いですよね)やはり直打ちするしか無いのかなって思います。対策としては、絶対変わらない場所(または変えてはならないと決めた場所)に、変数名称とパスのテーブルを作っておくというのをするかも。
— MASAKI (@himawari_beer) July 2, 2019
こういうの大切!
あるあるですね・・・
同様のトラブルがよくあるので、私はシート上にパス設定テーブルを置いています。
www.excel-chunchun.com
相対パス、絶対パス変換も。
www.excel-chunchun.com
やっぱりこういうの大切!
個人的には、利用するパスなんかの情報は、コードなどのユーザーから見えないところには書かず、設定シートとかから参照するようにしてる。
— rr_robert (@rrrobert10) July 2, 2019
設定シート派多数。
やっぱり大切と実感。
サーバサイドの管理しています。サーバ名は変えてはいけないのですーーーーー。
— 数井仁@エンジニア (@kazuihitoshi) July 2, 2019
並行運用するから変えるんだという人でても、バンししゃいましょう。
新サーバを新しい名前で並行稼働させたら、絶対旧サーバみんな使うだろ!!
新サーバには従来の名前が良いと思います!!先生
やっぱり!サーバー名は変更しちゃダメなんだ!
サーバー関係は詳しくないけど、今度知った顔して言ってみよう。
いや、これ修正大変だな。サーバー名変更をしない運用を考えないとね。 https://t.co/gi6iyMVNUf
— 佐藤嘉浩(Excelの魔法使い・スピルのひと) (@yosatonet) July 2, 2019
そもそもの問題はサーバーの名前が変更になった事だ!
サーバー名がかぶってないならLMHOSTSに書いてしまえばいい気が。
— Shinsuke Saito (まさ) (@masalabo716) July 2, 2019
AD環境なら旧サーバー名でAレコードでもCNAMEでも着ればそれでもよし。
一番の正解は、当然のことながら他のファイルを参照するマクロを作らないこと。
大正解!!思わず笑った。
本当にありがとうございました。