てんこ製作

Tenco Works

トップ > IT > VBAマクロのOneDrive移行

VBAマクロのOneDrive移行

IT

社内のファイルサーバーにVBAマクロ付きOfficeファイルを置いて社内のローカル業務で多用していたら、ファイルサーバーを廃止しますと言われた時の話です。

社内でもMicrosoft 365化が進んでおりまして、ファイルサーバーもOneDriveとSharePointドキュメントライブラリに移行しろという話が落ちてきました。

ところがOfficeファイルに組み込んでいるVBAマクロがファイルサーバー前提で作ってあるもんだから、そのままOneDriveやSharePointドキュメントライブラリに持って行ってもVBAマクロが動かないのでは疑惑が浮上。

なので、いろいろ調べてみました。

結論

Excelの場合

  • 基本的にはファイルサーバーパス指定からOneDriveなどのurl指定に書き換えるだけで良さそう。

Accessの場合

  • そもそもOneDrive上に置いたままでは開くことができない（Web版Accessがない）
  • ローカルにダウンロードして開くしかなさそう。更新が終わったらOneDriveにアップロードする形。
  • OneDriveなら同期ツールで多少楽できるけど、SharePointドキュメントライブラリだとそれも難しい。
  • ファイルサーバーで共有しながらAccessを使う方法は、OneDriveやSharePointではできない？

何が変わるのか

ファイルのアクセスの仕方が変わります。それにより、ファイルの指定の仕方も変わります。

ファイルサーバーのアクセス

ファイルサーバーの場合

¥¥サーバー名¥共有名¥ファイルパス・・・

例えば、サーバー名FileServer1に共有名Sharedという共有を作成して、そのフォルダにSample.xlsxというファイルを置いたとすると、こういう書き方になります。

¥¥FileServer1¥Shared¥Sample.xlsx

OneDriveのファイルアクセス

OneDriveの場合

https://OneDriveのurl/OneDrive上のパス/ファイル名

url自体は環境によって大きく違うようなのですが、確実に調べるならアプリ版のExcelでファイルを開いた後にメニューバーの

ファイル＞情報

で表示される画面の上段にパスのコピーというボタンをクリックするのがいいようです。

ブラウザで開くとブラウザのurlバーにもurlが表示されますが、家でやるとファイル名すら含まれていないurlだし、しかもVBAからそのurlを開こうとしてもうまくいかないし、でもブラウザでちゃんと開けてるし、よくわからない状態でした・・・。

https://d.docs.live.net/{各アカウントごとの識別する何か}/%E3%83%89%E3%82%AD%E3%83%A5%E3%83%A1%E3%83%B3%E3%83%88/Sample.xlsx

urlのhttps://d.docs.live.netは個人用OneDriveのurlなんですかね？

microsoft.comじゃなくてちょっとびっくりしますが。

あと%E3%83・・・などと暗号めいたものは日本語などのマルチバイト文字をurlエンコードしたものですね。「ドキュメント」というフォルダを作ったので、その部分がurlエンコードされてます。

パターン別対処

Excelで従来ファイルサーバーにアクセスしていて今回OneDrive移行に影響がありそうなパターンとしてはこんな感じ。

  • セルの数式にファイルサーバーのパスが埋め込まれている
  • VBAマクロの中にファイルサーバーのパスが埋め込まれている
  • VBAの中でWorkbooksオブジェクトのopenメソッドでファイルサーバー上のExcelファイルを開こうとしている
  • VBAの中でFileDialogメソッドでファイルサーバー上のフォルダを開こうとしている

数式にファイルパスが埋め込まれている場合

例えば単純に参照する数式だと、こんな感じになっていると思います。

=\\FileServer1\Shared\ExcelVBA\[Sample.xlsx]Sheet1'!$A$1

これをOneDriveに保存しているExcelファイルのセル参照に直すと、

='https://d.docs.live.net/{各アカウントごとの識別する何か}/ドキュメント/[Sample.xlsx]Sheet1'!$A$1

基本的にパス全体をクォーテーショ'でくくって、ファイル名の部分は角カッコ'[]'で囲めばよいみたい。

数式の時は大抵シート名やセルアドレスも一緒に指定するので、上記のような書き方になります。

vlookup()関数などでファイルを指定して参照するときもこのルールは一緒です。

VBAマクロ内のopenメソッドでファイルパスを指定している場合

WorkbooksオブジェクトのopenメソッドでExcelファイルを開くことができますが、ファイルサーバー上のファイルならこんな感じ。

Workbooks.Open("\\FileServer1\Shared\Sample.xlsx")

これをOneDriveに保存しているExcelファイルを開くように書き直すと、

Workbooks.Open("https://d.docs.live.net/{各アカウントごとの識別する何か}/%E3%83%89%E3%82%AD%E3%83%A5%E3%83%A1%E3%83%B3%E3%83%88/src/SPExcelFile_1.xlsx")

これでうまくいきました。更新してセーブもできたので、これで問題なくいけそう。

ファイルダイアログを使っている場合

実行時にファイルを指定するのにファイルダイアログを使うケースがあります。ApplicationオブジェクトのFileDialogプロパティになります。

Dim path
path = Worksheets("Sheet2").Range("H24").Value
With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = path
    .Show  
End With

InitialFileNameにパスを入れておくとそこが初期フォルダとして表示されるはずですが、https://で始まるurlをセットしても見えるのはローカルフォルダーでした。どうもFileDialog()はurlには対応してないみたい。

マルチバイトコードを含むパス/ファイル名

家で実験したときは特に問題なかったのですが、会社のMicrosoft365環境で試したときにマルチバイトコードを含むパス/ファイル名の場合は、urlエンコード指定だと開くことができませんでした。なぜなのか不明。。。

わからないので、会社では素直にurlエンコード指定はしないようにして対処してます。

Accessのファイル

そもそもAccessはSharePoint上やOneDrive上では使えるのか？

新規ボタンをクリックしてもAccessファイルを作るメニューは出てきません。

ローカルで作ったAccessファイルをOneDriveやSharePointドキュメントライブラリに置いてみましたが、「開く」メニューも出てきません。

プレビューメニューはあるのでプレビューしようとしても、結局ダウンロードさせようとする始末。

これはMicrosoft365上ではAccessを使わせる気がありませんね・・・

確認はここまで

一旦現段階の確認はここまで。

てんこの知識不足とか誤認識とかあるかもしれません。また、今後のMicrosoft側の機能改善やら改変でできることも変わってくるでしょう。その時はまた別途調査確認したいと思います。