派遣事務員の迷走

派遣事務員コロ子。会社の犬。顔出しNG。常に迷走している。

サーバー名が変更になっていた 解決法(FileSystemObject + VBProjectプロパティ)

こんにちは。
派犬事務員のコロ子です。

前回、サーバーの名前が変わって困った!の記事を書いたら、たくさんの方からいろいろな素晴らしいらしいアイディアを頂きました。どうもありがとうございます。
どれも自力で絶対たどり着けないものばかり。
みんな天才過ぎ!!!

目次

FileSystemObjectオブジェクトで現在のサーバー名(ドライブ名)を取得する

まずは、yuricks7さんより頂いたアイディアでFSO(FileSystemObjectオブジェクト)を使って現在のサーバー名(ドライブ名)を取得する。
ドライブ名が取得できるGetDriveNameメソッドを発見!

新しいモジュールを作成する

新しいジュールを追加し、名前を「サーバーの名前」にする。

f:id:SNegishi:20190705063356p:plain

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 & "\●●●\●●●●\●●●"

f:id:SNegishi:20190705220938j:plain

モジュールごとに置換すれば漏れなく変更できる。
これなら今後サーバー名が変更になってもそのまま使える。
Great!!


VBProjectプロパティでマクロでマクロを操作できる。(VBAでVBEを操作する)


では、この「サーバーの名前」モジュールをどうやって既存のファイルに入れてい行くかが問題。
・マクロ入りファイルを開く。
・モジュールを追加する。
・モジュールごとにパスのサーバー名を置換する。

という作業を手で行うとなると結構大変。既存のファイル全部で何個あるのー!?


そこで、@すでぃさんより外部モジュールを教えて頂いたのでこれを使用して上記の工程をVBAでやってみよう!

外部モジュールを作成する。

まずは、開発タブのセキュリティより「VBAのプロジェクトオブジェクトモデルへのアクセスを信頼する」にチェックを入れる。
f:id:SNegishi:20190705223732p:plain
f:id:SNegishi:20190705223634p:plain


別途、VBE操作用のマクロ入りファイルを用意する。
そこに先ほどの「サーバーの名前」モジュールを作成する。
「サーバーの名前」モジュールを右クリックし、ファイルのエクスポートを選択するとサーバーの名前.basが作成される。(.basは標準モジュールの拡張子)


f:id:SNegishi:20190705224220p:plain


この作業をマクロを使ってやってみる。

モジュールを操作するには
Workbook.VBProject.VBComponent オブジェクト
を使用する。

モジュールをエクスポートする

VBComponent オブジェクト. Export ファイル名.bas(パス含む)
ファイル名.basファイルが作成される。

同様にインポートもできる

モジュールをインポートする

VBComponent オブジェクト. Import ファイル名.bas(パス含む)


モジュール内のコードを変更する

モジュールに対する操作は、行を追加したり、削除したり、行ごとになるらしい。
モジュールの中身を直接置換する事はできないみたいなので、モジュールの中身を取り出して置換する。

①モジュール内の行数を調べる

VBComponent オブジェクト. CodeModule.CountOfLines
行数が戻る(数値型)

②モジュール内のコードを取得(開始行から指定行数分)

VBComponent オブジェクト .CodeModule.Lines(開始行, 指定行数)
文字列が戻る

③モジュールの指定行を削除する(開始行から指定行数分)

VBComponent オブジェクト .CodeModule.DeleteLines 開始行, 指定行数

④モジュール内にコードを追加する

VBComponent オブジェクト .CodeModule.AddFromString 文字列


コードを書いてみる

では、上記のことを踏まえてコードを書いてみる。

f:id:SNegishi:20190706135349p:plain

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さんより
f:id:SNegishi:20190704225445p:plain
素晴らしい!!


IPアドレスで指定できるんだ!知らなかった。
ファイル選択ダイアログならサーバーどころかフォルダ構造が変更になってもOK!


外部モジュール、初めて知りました。これ凄いです!!
読み込む外部モジュールのパスどうやって変更するかに悩んだので、今回はモジュールを操作する処理にしました。


こういうの大切!


あるあるですね・・・
同様のトラブルがよくあるので、私はシート上にパス設定テーブルを置いています。
www.excel-chunchun.com

相対パス絶対パス変換も。
www.excel-chunchun.com

やっぱりこういうの大切!

設定シート派多数。
やっぱり大切と実感。

やっぱり!サーバー名は変更しちゃダメなんだ!
サーバー関係は詳しくないけど、今度知った顔して言ってみよう。

そもそもの問題はサーバーの名前が変更になった事だ!

大正解!!思わず笑った。

本当にありがとうございました。