【Excel VBA】出力先を記載しないで、マクロと同じ保存先に出力する方法

はじめに

こんにちは。IMMRです。

例えば、下記のような共有フォルダー等決まったパスにあるデータをローカルに持ってきたいとします。

※サンプルでは、共有フォルダーではないですが・・・

図1　共有フォルダー内のデータ

上記のように決まったフォルダーからデータをコピーしたい時は

下記のようなサンプルコードを作成します。

サンプルコード

    Dim File_old As String
    Dim File_name As String
    Dim File_new As String
    Dim File_new_path As String
        
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "C:\VBA\データ"
        If .Show = 0 Then Exit Sub
        File_old = .SelectedItems(1)
    End With
    
    File_new = "C:\VBAマクロ\outdata.txt"
    FileCopy File_old, File_new

これは決まったパスに存在するフォルダーをダイアログで開き、

その中で選択したデータを「C:\VBAマクロ」にコピーするというプログラムです。

もちろん、これで問題なく動作します。

場合にもよりますが、このマクロを他のメンバーに展開するとしたら

・出力データのパスが一定である

・出力データの名前が一定である　といった箇所があります。

1.出力先のパスについて

上記のサンプルコードで記載しているパスは

・.InitialFileName = "C:\VBA\LogData"　※入力先のパス

・File_new_path = "C:\VBAマクロ"　　※出力先のパス

の２か所になります。

共有フォルダー(入力先のパス)はどのPCでも変わらないので修正はいりません。

しかし、出力先のパスは、Cドライブ直下でない限り、使用者によって変わってきます。

もちろん、出力先をC:\VBAマクロとした場合、Cドライブの内に"VBAマクロ"という名のフォルダーがないとエラーになります。

他の人に展開するマクロだとすると使用者によって出力先を変更しなくてはいけません。

Cドライブ直下にすれば問題ありませんが、出力先はこちらのほうがいいと思う人もいます。

そのため出力先を変更しなくてはいけませんが、使用者ごとに変更では手間がかかります。

そのような場合は、出力先は記載ししないで、使用者ごとに出力先が変わるようにすればよいのです。

やり方はいろいろありますが、一つはマクロのパスを取得して、

取得したパスを出力先にすることです。

そうすることで出力先のパスを記載しないで、マクロを投稿したパスと同じ配下にデータが出力されます。

図2　出力先を記載した場合

図3　出力先を記載しない場合

2.マクロのパスを取得

上記で説明したように「出力先」を記述しないでマクロ自身と同じパスに出力するには

マクロが投稿されているパスを取得する必要があります。

パスを取得するには、

Object.Path

と記載します。

現在開いているブックはActiveWorkbookで定義出来るので、

ActiveWorkbook.Pathとすればマクロ自身のパスを調べることが出来ます。

例：「C:\VBAマクロ」内のフォルダーにある「データ取得マクロ」だとするとC:\VBAマクロと返されます。

「出力先」を記述しないでマクロと同じパスに出力するためには、取得するファイル名も必要です。

3.ファイル名を取得

ファイル名を取得するには、

Dir(Object)

と記載します。

パスとファイル名前が記載されているものからファイル名取得できます。

Dir("C:\VBA\データ\ABCDE0001.txt")だと「ABCDE0001.txt」になります。

また、ダイアログボックスで選択したデータFile_old = .SelectedItems(1)は、パスと名前が記載されていますので

File_old.Nameとすれば選択されたファイル名前だけを取得することもできます。

例：「C:\VBA\データ」内のフォルダーにある「ABCDE0001.txt」だとするとパス+ファイル名は、

　　「C:\VBA\データ\ABCDE0001.txt」ですがABCDE0001.txtとなります。

4.出力先を記載しないで、マクロと同じ保存先に出力するサンプルコード

出力先を記載しないで、ブックと同じ保存先に出力するサンプルプログラムを作成します。

保存するデータは、「図1　共有フォルダー内のデータ」で説明したC:\VBA\データ内にあるデータです。

使用プロパティは上記で説明した「パスを取得するプロパティー」と「ブック名を取得するプロパティー」です。

下記がサンプルコードです。

サンプルコード

Sub TEST33_2()
    Dim File_old As String
    Dim File_name As String
    Dim File_new As String
    Dim File_new_path As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "C:\VBA\データ"
        If .Show = 0 Then Exit Sub
        File_old = .SelectedItems(1)
    End With
    
    File_name = Dir(File_old)
    File_new_path = ActiveWorkbook.Path
    File_new = File_new_path & "\" & File_name
    FileCopy File_old, File_new
End Sub

入力データのフォルダーのパス"C:\VBA\LogData"は記載していますが、

出力先のパスは記載していません。

そのため、このマクロをどこに格納しても入力先のフォルダーから

データをマクロが格納してあるパスと同じ場所に出力されます。

簡単にサンプルコードの説明をします。

With Application.FileDialog(msoFileDialogFilePicker)

～

End With

　ダイアログボックスよりファイルを選択しています。

　以前に別の記事で説明していますので、参照下さい。

【Excel VBA】ダイアログボックスより処理に必要なファイルを選択する方法

File_name = Dir(File_old)

　File_oldにはダイアログボックスで選択したファイル名およびパスが格納されています。

　その中からファイル名だけを取得してFile_nameに格納しています。

　例："C:\VBA\データ\ABCDE0001.txt"だとすると"ABCDE0001.txt"になります。

File_new_path = ActiveWorkbook.Path

　　現在のマクロ(ブック)が格納されているパスを取得し、File_new_pathに格納しています。

　　例："C:\VBAマクロ\データ取得マクロxlsm"だとすると"C:\VBAマクロ"になります。

File_new = File_new_path & "\" & File_name

　File_new_pathで格納したマクロが格納されているパスと

　File_nameで格納したファイル名を一つに繋げています。

　ただ繋げただけだとパス\ファイル名にしたいところをパスファイル名となってしまうため

　"\"をパスとファイル名の間に入れております。

　例：File_new_pathは"C:\VBAマクロ"

　　　File_nameは"ABCDE0001.txt"

　　　File_newは"C:\VBAマクロ\ABCDE0001.txt"になります。

FileCopy File_old, File_new

　FileCopyは、File_old(パスを含んだファイル名)からFile_new（パスを含んだファイル名）にコピーします。

　この場合、コピーを実行しても元のファイルは無くなりません。

　例：File_oldはC:\VBA\LogData\ABCDE0001.txt

　　　File_newは"C:\VBAマクロ\ABCDE0001.txt"になります。

5.出力先を記載しないで、マクロと同じ保存先に出力するコードの実行

では、出力先を記載しないで、ブックと同じ保存先に出力されるか確認します。

サンプルコードは、下記で行います。

サンプルコード

Sub TEST33_2()
    Dim File_old As String
    Dim File_name As String
    Dim File_new As String
    Dim File_new_path As String
    
    With Application.FileDialog(msoFileDialogFilePicker)
        .InitialFileName = "C:\VBA\データ"
        If .Show = 0 Then Exit Sub
        File_old = .SelectedItems(1)
    End With
    
    File_name = Dir(File_old)
    File_new_path = ActiveWorkbook.Path
    File_new = File_new_path & "\" & File_name
    FileCopy File_old, File_new
End Sub

ここで注目するべき点は、出力するファイル名およびパスを記載していないことです。

入力データのパスは以下になります。

図4　共有フォルダー内のデータ

そして、マクロを下記場所に投稿します。

図5　マクロ投稿

※マクロ以外は何も何も投稿されていません。

そして、投稿したマクロを実行します。

図6　マクロ実行

実行するとダイアログボックスが表示されます。

データを選択するとマクロの処理が実行し、終了します。※　ここでは「ABCDE0001」を選択します。

そして再度、マクロを投稿したフォルダーを見てみると

図7　マクロ実行後

先ほど選択したデータがマクロと同じフォルダーに出力されていることが確認できます。

出力先を記載しないで、ブックと同じ保存先に出力することが出来ました。