OneDrive環境での Excel VBAファイルパス問題の解決策

OneDrive に保存してある Excel VBAで CSV を読み込もうとするとエラーになった

Excel VBAでローカルファイルを操作する際、OneDriveを使用している環境で想定外の問題に遭遇してしまいました。

具体的には、ローカルファイルパスを指定しているのに、OneDrive の httpsパスに変換されてしまい、ファイル操作に失敗してしまうのです。

エクセルファイルが OneDrive 以外に保存してあればそんなことにはならないので、これまで気づきませんでした。潜在的なバグとなる重大な問題だと思いますが、ググっても公式の説明が見当たらず、Microsoft らしいなぁ、と思いました。

問題の詳細

OneDrive でエクセルファイルを同期している場合、VBA で OneDrive 内のローカルファイルを開こうとすると、以下のようなパスに変換されることがあります。

https://d.docs.live.net/[ID]/Documents/example.csv

または：

https://[組織名].sharepoint.com/sites/[サイト名]/Documents/example.csv

Microsoft SharePoint と呼ぶらしい。

これらのURLでは、ADO.DB でファイルを開くことができません。
ちなみに、ADO.DB を使う理由は UTF-8 と シフトJIS を自動判定させるためです。
未だにエクセルは CSV の読み込みに制約が多いので、こんな面倒が必要です。
そこに追い打ちをかける所業。
みんなエクセルを使うのをやめて、LibreOffice Calc にすればいいのに。

解決策

この問題を解決するために、OneDrive の httpsパス をローカルパスに戻す関数を実装しました。

Private Function GetLocalPath(ByVal filePath As String) As String
    ' httpsで始まらないパスはそのまま返す
    If LCase(Left(filePath, 4)) <> "http" Then
        GetLocalPath = filePath
        Exit Function
    End If
    
    Dim lPath As String: lPath = LCase(filePath)
    
    ' SharePoint Online用のパス変換
    If InStr(lPath, ".sharepoint.com") > 0 Then
        GetLocalPath = Environ$("OneDriveCommercial") & "\" & _
                      Replace(Mid(filePath, InStr(filePath, "/Documents/") + 10), "/", "\")
    
    ' 個人用OneDrive用のパス変換
    ElseIf InStr(lPath, "docs.live.net") > 0 Then
        'OneDriveConsumerが存在しない場合はOneDriveを使用
        Dim oneDrivePath As String
        oneDrivePath = Environ$("OneDriveConsumer")
        If oneDrivePath = "" Then oneDrivePath = Environ$("OneDrive")
        
        Dim idEnd As Long: idEnd = InStr(InStr(lPath, "docs.live.net/") + 14, filePath, "/")
        GetLocalPath = oneDrivePath & "\" & _
                      Replace(Mid(filePath, idEnd + 1), "/", "\")
    
    ' 上記以外のパスはそのまま返す
    Else
        GetLocalPath = filePath
    End If
End Function

使用例

この関数は以下のように使用します：

Sub OpenFile()
    Dim filePath As String
    filePath = Application.GetOpenFilename("CSVファイル (*.csv), *.csv")
    
    ' ファイルを開く前にパスを変換
    filePath = GetLocalPath(filePath)
    
    ' 変換されたパスを使用してファイルを開く
    With CreateObject("ADODB.Stream")
        .Open
        .LoadFromFile filePath
        ' ... 以降の処理
    End With
End Sub

実装のポイント

1. 環境変数の利用

   • Environ$("OneDriveCommercial"): 法人向けOneDriveのローカルパス
   • Environ$("OneDriveConsumer"): 個人向けOneDriveのローカルパス
   • Environ$("OneDrive"): 一般的なOneDriveのローカルパス

2. パスの判定

   • SharePointのパス: .sharepoint.comを含む
   • 個人用OneDriveのパス: docs.live.netを含む

3. パス区切り文字の変換

   • Webパスの/をWindowsパスの\に変換

注意点

• この実装は、OneDriveとSharePoint Onlineの標準的なパス構造を前提としています
  • 一部の環境でしか検証できていません
• ファイルが実際にローカルに同期されている必要があります

まとめ

OneDrive環境でのExcel VBAファイル操作は、適切なパス変換処理を実装することで、問題なく動作させることができます。この記事で紹介した実装は、多くの一般的なケースで利用可能な解決策を提供します。

参考情報

• 公式な説明は見つかりませんでした...

---

この記事が、同様の問題に直面している方々の助けになれば幸いです。
もし、このコードではNGな環境があるようでしたら、情報を共有してくださると嬉しいです。