VBA技術解説
OneDrive使用時のThisWorkbook.Pathの扱い方

ExcelマクロVBAの問題点と解決策、VBAの技術的解説
公開日:2023-07-26 最終更新日:2023-07-26

OneDrive使用時のThisWorkbook.Pathの扱い方


マクロVBAでは、現在動いているマクロVBAが書かれているブックのバス(Path)を取得し、そのフォルダを使って処理を組み立てる方法が良く用いられます。
ところがOneDriveと同期しているフォルダでは、このような処理が従来のVBAコードでは行えなくなっています。
従来のVBAではファイルの一覧が取得できないのです。


この問題に対処するためのVBAサンプルや方法については、ネット上に沢山出ています。
なので、本サイトで特に記事にする必要はないと思っていたのですが、、、
しかし、本サイトを訪れる方からお問い合わせをもらうことも度々ありますので、最低限の事は書いておいた方が良いだろうと思い、ここに記載しておくことにしました。
ここでは簡単な解説と、ごく単純なVBAコードの提示だけにとどめます。
よりよいVBAコードや特種な場合(SharePointファイル等々の問題)の対応については個別にお調べください。
以下では、あくまでOneDrive同期しているフォルダの場合に、簡易的にローカルフォルダ文字列に変換する方法についての解説になります。


ThisWorkbook.Pathで取得できる値

動かしているマクロVBAが書かれているブックが格納されているバスは、
ThisWorkbook.Path
これで取得できます。
VBAが書かれている自分自身(ThisWorkbookオブジェクト)のPathプロバティです。
C:\Users\ユーザー\...
D:\User\...
\\サーバー名\...
このようなパスになります。

ところが「OneDrive」のフォルダの場合はURLとなってしまいます。
VBA マクロ OneDrive使用時のThisWorkbook.Pathの扱い方

このようなURL文字列のパスでは、VBAが不都合(エラーになったり、正しく動作しなかったり)な結果になる場合が多くあります。
特定ファイル名での読み書き(Open、SaveAs)するだけなら大抵の場合はそのままでも出来るはずですが、、、
Dir関数やFileSystemObjectでフォルダ内のファイル一覧を取得しようとすると、
エラーになるか、正しく動作しないといった状態になります。

これを解決する方法として、OneDriveのURLlをローカルのフォルダに変換することで乗り越えようということです。
この下で実際のVBAコード(あくまでサンプル程度)の提示と簡単な解説をしておきます。


ローカルのThisWorkbook.Pathを取得するVBAサンプル

ブックのモジュールにプロパティを作成します。

VBA マクロ OneDrive使用時のThisWorkbook.Pathの扱い方

「ThisWorkbook.Path」と同様の使い勝手を実現するために、ブックモモジュールにプロパティを作成します。
もちろんプロパティ名は何でも構いません。

Functionで実装しても構いませんし、使い勝手はどちらでもあまり変わらないと思います。
もちろん、標準モジョールにFunctionを作成する方法でも構いません。

VBAサンプル

Public Property Get Path2()
  'URL以外(httpで始まらない)ならそのまま返す。
  Dim sPath As String
  sPath = ThisWorkbook.Path
  If Not sPath Like "http*" Then
    Path2 = sPath
    Exit Property
  End If
  
  '環境変数からOneDriveのフォルダを取得
  Dim OneD As String: OneD = Environ("OneDrive")
'  If Environ("OneDriveConsumer") <> "" Then OneD = Environ("OneDriveConsumer")
'  If Environ("OneDriveCommercial") <> "" Then OneD = Environ("OneDriveCommercial")
  
  'URL「https://d.docs.live.net/xxxxxxxxxxxxxxxx/○○○」
  '4番目の"/"以降(上記なら○○○の部分)を取り出す。
  '最初の3つの"/"を別文字にReplaceしてから、InStrで"/"の位置を求めています。
  Dim sTemp As String
  sTemp = Replace(sPath, "/", "_", , 3)
  Path2 = OneD & "\" & Mid(sTemp, InStr(sTemp, "/") + 1)
End Property

VBAコード自体は単純なものなので、コード内のコメントを参考にしてください。
以下は、関数等の参考ページです。
Like演算子とワイルドカード|VBA入門
・Like演算子 ・パターン文字列式(ワイルドカード、文字リスト、文字範囲) ・Like演算子の使用例 ・正規表現について
VBA関数(文字列操作,Replace,InStr,StrConv)|VBA入門
・文字列操作に関するVBA関数の一覧 ・Replace関数 ・InStr関数 ・StrConv関数 ・最後に
ENVIRON関数|VBA関数
オペレーティングシステム(Windows)の環境変数に関連付けられた文字列を返します。Macintoshでは使用できません。デスクトップのパスやログインしているユーザーの名前を取得できます。IsNull関数の構文 Environ({envstring|number}) envstringまたはnumberのどちらか…

使い方

ThisWorkbook.Path
これの代わりに、
ThisWorkbook.Path2
として使う事が出来ます。

環境変数の確認

上記VBAでは環境変数からOneDriveのフォルダを取得しています。
環境変数なので環境に依存します。
一般的な環境なら、、、というVBAコードにすることは出来るとは思いますが、いろいろな環境で確認するのがかなり大変です。
先のVBAではOneDriveとして3つの環境変数がありますよ、というだけのコードにとどめています。
OneDriveに関する環境変数としては以下の3つがあるようです。
OneDrive
OneDriveConsumer
OneDriveCommercial
PCにより、どの環境変数が使われているかが変わってきます。
ネットにある汎用的なコードを使うにしても、自身の環境がどうなっているか確認しておいた方が良いでしょう。
以下のVBAでは、"OneDrive"という文字を含む環境変数だけを出力しています。

 Sub PrintEnviron()
  Dim i As Long
  i = 1
  Do Until Environ(i) = ""
    If Environ(i) Like "*OneDrive*" Then
      Debug.Print i & ":" & Environ(i)
    End If
    i = i + 1
  Loop
End Sub


参考になる外部サイトの記事

[VBA]OneDriveで同期しているファイルまたはフォルダのURLをローカルパスに変換する関数
VBAコードは結構しっかり書かれていますので参考になると良いと思います。
ただし、内容的には単純な文字列変換だけ(環境に応じた分岐のみ)なのにライセンスがどうのと書かれているのが・・・

VBAのThisWorkbook.PathでOneDriveビジネス版のリンクフォルダからもローカルパスを取得できる関数ライブラリ VBA-FileToolsの解析記録と最近使ったファイルの読み込み
文字列変換ではない方法になります。
多少の制限はあるものの実用的には問題いと思います。
以下の改良版もあるようです。
GitHub - Excel-VBA-Diary/ThisWorkbookLocalPath: OneDriveでThisWorkbook.PathがURLを返す問題を解決する (Resolve the problem of ThisWorkbook.Path returning a URL in OneDrive)




同じテーマ「マクロVBA技術解説」の記事

ユーザーに絶対に停止させたくない場合のVBA設定
印刷範囲の設定・印刷範囲のクリア
VBAの省略可能な記述について
VBAのVariant型について
VBAのインデントについて
VBAの演算子まとめ(演算子の優先順位)
列幅不足による###表示や指数表示を判定する
OneDrive使用時のThisWorkbook.Pathの扱い方
セル個数を返すRange.CountLargeプロパティとは
画像「セルに配置」のVBAについて(365の新機能)
VBAでクリップボードへ文字列を送信・取得する3つの方法


新着記事NEW ・・・新着記事一覧を見る

無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)
イータ縮小ラムダ(eta reduced lambda)|エクセル入門(2023-11-20)
PIVOTBY関数(縦軸と横軸でグループ化して集計)|エクセル入門(2023-11-19)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.マクロとは?VBAとは?VBAでできること|VBA入門
10.セルのクリア(Clear,ClearContents)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ