ADOを使用してエクセルシートをSQLでSELECTするようなVBAマクロが組まれているExcelファイルで、今までは問題なかったのですが、
「データプロバイダまたはほかのサービスがE_FAIL状態を返しました」というメッセージが表示されるようになった！なにかわかんない？
という相談を受けたことがありました。
これまで問題なかったのに急にエラーが出始めた、ということで依頼者も原因不明で困っている様子。相談を受けた私が調べてみた結果を記事にしてみました。
エラーが発生したエクセルVBAマクロの構成
問題が急に発生しだしたのは、次のような構成のエクセルVBAマクロファイルでした。
- ボタンをクリックした際に「データプロバイダまたはほかのサービスがE_FAIL状態を返しました」というエラーが発生するようになった
- 特にマクロ（VBA）に対しては修正を入れていない
- データ用のシートについては値を変更した
- 最近、Windows7からWindows10にアップグレードした
急に「データプロバイダまたはほかのサービスがE_FAIL状態を返しました」というエラーが発生するようになったとのこと、VBAのマクロは特に何も行っていないとのことで、環境的な部分で何か発生したのかなと想定されましたが、これだけだとわからないため問題の切り分けをまず行ってみました。
E_FAILエラーの原因特定のために問題の切り分け
ソースの解析は問題の切り分け後に行おうと思ったので、まずは原因特定のために依頼してきた人に質問をしてみました。
エクセルファイルのバックアップの確認
まず、エクセルファイルのバックアップがあるかを確認してみました。幸いにも、マクロ内で毎日エクセルファイルを開いた際にバックアップを取る処理があったようで、1週間分のバックアップファイルが存在していました。
最新のバックアップファイルから開いてエラーが発生するかを確認していったところ、ある日付以降からエラーが発生するようになっていることを確認できました。つまり、この日の前後に何かやらかしているということです。
また、この確認によって、Windows7からWindows10へのアップグレードがこのエラーには関係ないことが検証できました。
エクセルに対しての変更内容の確認
次に、バックアップファイルから特定できた日付の前後に何かエクセルファイルに対して変更を加えていないかを相談してきた人に確認をしてみたところ、
「データ用シートに追加とか変更をしたかも」
との回答がありました。
データ用シートって何？と依頼者に確認したところ、どうやらエクセルマクロの処理で使用する際の定義値を保存しているシートのようでした。シートを見ると表形式になっていたので、どうやらシートをデータベース（RDB）のように見立ててマクロ内では処理をしているようでした。
このシートが10シートぐらいあったので、どのシートに対して修正を入れたのかを確認してみたところ、
「変更を入れた個所は色を付けているけど、どのシートかは覚えていないよ」
とのことで、シートの特定までは至りませんでしたが、とりあえずは何かシートへのデータの変更が怪しそうだという点を確認できました。マクロに対してはプログラムソースはいじっていないため、ここが一番原因に近いだろうと目星をつけてみました。
と思ってどこを変えたのか差分を取ろうとシートを比較してみたのですが・・・結構大量の部分を変更していたようで、どこが怪しいのか見た目にはわかりませんでした。データがシート内に記載されているだけなので、ぱっと見では特に問題があるようには見えません。
エクセルVBAマクロの調査
マクロは修正していないとのことで、まずはどの部分でエラーが出ているのかをデバッグしてみました。VBA自体触るのが久々だったので手間取りましたが、Google検索＆記憶を頼りにブレークポイントを張りながら調べて、エラー発生個所を特定することができました。
パット見たところ、どうやらマクロ内では次のようになっているようでした。
- データ用シートからSQLで必要な情報を取得している。
- SQLを利用するためにADOを使用している。
- ADOで取得したデータをレコードセットに格納し、レコードセットのRecordCountプロパティの値を取得しようとした際に値を取得できずに「データプロバイダまたはほかのサービスがE_FAIL状態を返しました」のエラーが返されている。
「へー、エクセルシートの内容をADOでSQLを使って取得できるのか」と知らなかった部分の知識をゲットしつつ、レコードセットのRecordCountの取得がうまくいっていない部分がエラー発生個所だということが判明しました。
エラーが発生しないファイルとエラーが発生するファイルはVBAマクロは同じなので、それ以外の原因。やはりデータが怪しそうです。
そのため、エラーが出ているSQLで使用しているシートが原因だろうと目星をつけ、対象のデータシートのデータを確認してみることにしました。
データは問題ないように見える・・・
データを見てみたところ、特に問題があるようにはやはり見えません。しいて言えば、同じ列の値について、セルの書式が文字列になっているか数値になっているかぐらいです。
ただ、エラーが発生しないエクセルのほうのデータ用シートを見てみたところ、この部分は同じデータになっていたので問題はなさそうです。
そこで、
- エラーが発生していないエクセルのほうに、エラーが発生するエクセル内のデータシートの値をコピペする
- エラーが発生していないエクセルでマクロを動かす
ということをしてみました。想定ではこれでエラーが再現されると思っていたのですが、エラーが再現されません。つまり、このデータシート内のデータがおかしいわけではなさそうです。
マクロを見てもADOでSELECTしているのはこのデータシートなので、ほかの部分が影響しているとは考えにくいです。データが問題ではないとなると、何が影響しているのか、手詰まりとなりました。
Googleでエラーメッセージで検索してみる
原因がわからないため、
「データプロバイダ サービス E_FAIL 状態 エラー」
「ADO E_FAIL エラー エクセル」
などでGoogle検索をしてみました。検索結果に出てきた対応のなかで
adoRs.CursorLocation = adUseClient を adUseServer に変更する
という対応でうまくいったという内容がありましたが、そもそもCursorLocationを使用していないためとりあえずやってみましたがやはりダメで、問題解決には至りませんでした。
動くファイルに動かないファイルのデータシートを移動しての検証
とりあえずデータが怪しいという予想は外れたようで原因も特定できないため、次の操作を行って動くようにしてみることにしました。
- エラーが発生しないバックアップファイルをコピーする（ファイルAとする）
- ファイルA内のデータ用シートを全て削除する。
- エラーが発生するファイル（ファイルBとする）のデータ用シートを全てコピーしてファイルAに挿入する。
- ファイルAでマクロを起動する。
この操作を行ってみたところ、ファイルAでもエラーが再現するようになりました。これにより、やはりデータ用シートが怪しいということが確定しました。データ用シートを全て元に戻すと正常にマクロが動くようになりましたので、どこかのシートが怪しいということです。
次に、どのシートが悪さをしているのかを特定するために、1シートずつ確認をしてみることにしました。
- エラーが発生しないバックアップファイルをコピーする（ファイルAとする）
- ファイルAのデータ用シートAを削除する。
- エラーが発生するファイル（ファイルBとする）のデータ用シートBをコピーしてファイルAに挿入する。
- ファイルAでマクロを起動する。
- エラーが発生しない場合、データ用シートB、C、D・・・のように1つシートずつファイルBからファイルAにシートを移して動作確認を行う。
すると、特定のデータ用シートの場合に、E_FAILエラーが発生することが確認できました。ただ、データを比較して見る限りは特に何も変わりはありません。そこで、問い合わせをしてきた方に、再度何をしたのか思い出してみてもらいました。
「んー、何って言われてもねぇ・・思い出せないよ。セルの書式を文字列にしたぐらいかな」
セルの書式が影響するとは思えないのですが、エラーが発生するシートのセルの書式を見てみると、確かに文字列になっていました。ただ、エラーが発生しないデータ用シートも同様に文字列になっているので、これが原因ではないようです。
ここで少しひらめきました。もしかしたら・・・
試しにデータシート内の列を複数選択して列の書式設定を見てみたところ、列によって書式が異なっていることが判明しました。
ここを全ての列について「標準」に変更してマクロを実行してみたところ、エラーが解消しました。
このことから、今回のE_FAILエラーは列の書式設定が原因で起こっていたようでした。データ自体ではなくセルの書式設定が影響していたようで、だいぶ調査に手間取ってしまいました。
再度E_FAILエラーが発生
と思ってしばらくしたところ、また同じようなE_FAILエラーが発生したと相談がありました。どうやら今回もデータ用シートの書式設定を変更したようなのですが、前回解決できた方法を行ってもうまくいかないということでした。
調べてみたところ、
- 同一列内の各セルの書式が一致していないとエラーと、SELECT後のレコードセット.recordCountがE_FAILエラーとなる。
- セルの書式設定に「文字列」を使用している場合に特に発生する様子
ということが判明しました。しかも、SELECT句でテーブルとして指定したシート以外のシートでデータ内に書式違いがあっても発生します。
つまり、データ用シートAとデータ用シートBがあった場合、SELECT分ではデータ用シートAから取得しようとしていても、データ用シートBの列内で書式の整合性が合わないような場合、そのSELECT文の結果であるレコードセットのRecordCountが「データプロバイダまたはほかのサービスがE_FAIL状態を返しました」となります。
何を言っているかわからないと思いますが、実際そうなっていました。なので、エラーとなっているSELECT文で使用しているデータ用シートAのセルの書式を一生懸命調べても問題解決がしなくてハマってしまいました。原因はSELECT文で使用していないデータ用シートBにあったためです。
Officeのバグなのか仕様なのかはわかりませんが、VBAマクロでシートをSELECT文で取得する際は、こんな現象が発生する点を頭の片隅に置いておくとよいかもしれませんね。
まとめ
今回は、エクセルマクロで急に発生するようになった「データプロバイダまたはほかのサービスがE_FAIL状態を返しました」 というエラーについて、問題の切り分けと原因の特定について行ったことを記事にしてみました。
急にエラーが発生するのは何か変わったことが原因なので、どんな変更をしたのかきちんと覚えておくのが原因分析のためには必要ですね。
今回のケースではセルの書式設定に問題があったようで、中々調査に戸惑ってしまいました。VBAでADOを利用してシートをSELECTするような操作をする場合、テーブルとして使用しているシート内のデータについて、列ごとに書式を統一しておいたほうが良いようです。特に指定がない場合はセルの書式設定をすべて「標準」としておくのがよさそうです。
また、この記事に書いたようなちょっとした困ったことでも、自分に経験や知識が足りないために対応できなかったり、時間をかけたわりに全然進まなかったりと、思うようにいかない事ってありますよね。そんなときは専門家に相談したり解決してもらったりする方法もありますので、一人で悩まず対処してみましょう。
