NOOCIW

タグ: vba

  • File System ObjectをVBAで使うための参照設定の導入手順

    FileSystemObjectをVBAで使用する場合、「CreateObject関数」で使用できますが補完機能が利用できません。

    補完機能を利用するためには参照設定を行う必要があるためその手順を最初からやっていきます。

    まずはExcelを起動して「開発」というリボンを表示させます。これはVBAを編集する場合には必ずやっておいた方が良い設定です。

    Excelを起動したら「ファイル」ボタンをクリックします。

    「ファイルボタン」をクリック

    次に「オプション」ボタンをクリックします。

    「オプション」ボタンをクリック

    新しく表示されたウィンドウから「リボンのユーザー設定」を選択します。

    「リボンのユーザー設定」を選択

    「開発」のチェックボックスをONにして「OK」ボタンをクリックします。

    「開発」のチェックボックスをON

    「開発」ボタンが表示されるのでクリックします。

    「開発」ボタンの表示

    「Visual Basic」ボタンを選択します

    「Visual Basic」ボタンを選択

    これでVisual Basic Editor（VBE）が表示されます。

    ここからが参照設定となります。

    VBE上で「ツール」ボタンを選択します。

    「ツール」ボタンを選択

    「参照設定」ボタンをクリックします。

    「参照設定」ボタンをクリック

    一覧から「Microsoft Scripting Runtime」という項目を探してチェックボックスにチェックを入れて「OK」ボタンをクリックします。

    「Microsoft Scripting Runtime」にチェックを入れて「OK」ボタンをクリック

    もう一度参照設定を確認すると「Microsoft Scripting Runtime」にチェックが入っています。

    再度参照設定を確認すると「Microsoft Scripting Runtime」にチェックが入っている

    これでFileSystemObjectの参照設定の導入手順は終わりです。

    参照設定を導入することでFileSystemObjectのオブジェクト（FolderやFile）がそのまま使用できて補完機能も利用できるようになるためコーディングが楽になります。

  • Pingで疎通確認を実行するVBAコードサンプル

    ExcelのVBAでマクロを実行してPingコマンドを実行するコードサンプルです。

    Sub PingTest()
    
    Dim cmd As String
    Dim url As String
    Dim return_code As Long
    Dim wsh As Object
    Set wsh = CreateObject("WScript.Shell")
    
    'URLの設定
    url = "google.com"      'IPアドレスの場合には「192.168.10.1」など
    'url = "www.google.com"             'Return Code : 0
    'url = "8.8.8.8"                    'Return Code : 0
    'url = "xxxyyyzzz"                  'Return Code : 1
    'url = "https://www.google.com"     'Return Code : 1
    
    ' IPアドレスまたはホスト名を取得してping送信
    ' WSH経由でping送信コマンドを実行
    cmd = "cmd.exe /c ping " & url

    'Ping実行時の戻り値を取得
    return_code = wsh.Run(cmd, vbNormalFocus, True)

    ' pingの成功・失敗を記述
    Debug.Print return_code            '0 or 1
    
    'コマンドプロンプトでの標準出力結果を取得する場合の記述
    Debug.Print wsh.Exec(cmd).StdOut.ReadAll()
    
    ' オブジェクトの後処理
    Set wsh = Nothing
    
End Sub
    実行結果

    「WScript.Shell」経由でコマンドプロンプトを起動してPingコマンドを実行しています。

    WScript.ShellスクリプトのRunメソッドを使用した場合、戻り値（整数）があるためそれを取得しています。1つ目の引数で実行コマンドを記述します。2つ目の引数でウィンドウのスタイルを指定します。「vbNormalFocus」は既定値でウィンドウをアクティブにして表示します。3つ目の引数をTrueとすることで同期処理を行っています。

    WScript.ShellスクリプトのExecメソッドを使用した場合、標準出力を取得できます。

  • Excelファイルのシート名を全て抽出するVBAサンプル

    Excelファイルのシート名を全て取得するVBAコードのサンプルです

    Private Sub ExtractExcelSheetNames()
    Dim wb As Workbook                  'ワークブック
    Set wb = Application.Workbooks("Sample.xlsx") 'ワークブックをセット
    Dim ws As Worksheet                 'ワークシート
    'ワークブックのワークシート名を全て取得する
    For Each ws In wb.Worksheets
        Debug.Print ws.Name
    Next
End Sub

    上記の例では「Sample.xlsx」というファイルを開いた状態で実行するとVBE（ビジュアル・ベーシック・エディター）上のイミディエイトウィンドウにワークシート名を表示します。

    イミディエイトウィンドウ