【Excel】OneDriveのファイルURLをローカルパスに変換（数式で文字列の置換）

Excelの数式で、CELL(“filename”,A1)やVBAのThisWorkbook.Pathを使用したとき、OneDriveに同期したフォルダではローカルパスが取得できず、VBA等が適切に動作しないことがあります。

例えば、

C:\Users\xxx\OneDrive\aaa\bbb\ccc\[売上データの統合.xlsm]設定

といったローカルパスを取得したいが、

ｈttps://d.docs.live.net/□□□□□/aaa/bbb/ccc/[売上データの統合.xlsm]設定

のようにhttpsから始まるURLが取得される。

Excelの数式を利用して、OneDriveのファイルURLをローカルパスに変換してみたので備忘録。

下図のように、CELL(“filename”,A1)を使用して取得したフォルダ情報をSUBSTITUTE関数を使用して置換。

if文で、https:を含むときに置換を反映するようにしている。

なお、上記図の数式部分は次のとおり。

元の情報	=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)
検索文字ｈ	ｈttps://d.docs.live.net/12345678/ （一文字目は半角”h”）
置換文字	C:\Users\xxx\OneDrive\
置換後１	=SUBSTITUTE(B2,B3,B4)
置換後２	=SUBSTITUTE(B5,"/","\")
確定	=IF(COUNTIF(B2,"https:*")=0,B2,B6)