アクセス（access）からエクセルにエクスポート

質問

アクセスから、エクセルにクエリのデータを出力する際に、
「TransferSpreadsheet」を使用していますが、
そのエラー処理について、教えて下さい。

保存しようとしているファイルが既に開かれている場合に、
処理を中止し、エラーメッセージを出したいのですが、
どのようにすればいいのでしょうか？

なお、現在のVBAは次のとおりです。

Private Sub コマンド01_Click()

Dim a As String
Dim b As Variant

a = "Q_一覧表"
b = "C:\一覧表.xls"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, a, b, True
MsgBox "データを出力しました。"

Exit Sub

End Sub

回答No.1

agricap · ベストアンサー

自分でエラー処理を特に書いていない場合には、自動的にエラー番号(Err.Number)とエラーメッセージ(Err.Description)がメッセージボックスで表示されます。

実際に出力先のファイルがオープンしている状態でこれを実行すると、エラー番号3010のエラーになることがわかります。
そこで、自分でエラー処理を書くことにして、3010の場合に好きなメッセージを表示するように書き換えます。
たとえば、以下のようになります。

Private Sub コマンド01_Click()

Dim a As String
Dim b As Variant

On Error GoTo OPENERR

a = "Q_一覧表"
b = "C:\一覧表.xls"

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, a, b, True
MsgBox "データを出力しました。"

Exit Sub

OPENERR:
If Err.Number = 3010 Then
MsgBox "出力先のファイルが開かれています"
Else
MsgBox Err.Description
End If

End Sub

お礼

エラー番号の取得の仕方を教えていただき、ありがとうございました。
私は、まだまだVBA初心者のため、非常に勉強になりました。
エラー番号のことをほとんど知らなかったため、
教えてもらっていなければ、解決策が分かりませんでした。
一時は諦めようかと思いましたが、なんとか解決することができました。
本当にありがとうございました。

補足

ご回答ありがとうございます。
的確な回答でとても分かりやすかったです。

しかし、実際にやってみると新たな問題が生じました。
エクセルファイルが開いている状態で実行すると、出力先のエクセルデータを全て消去してから、
エラー番号（3010）が表示されるようです。

もし、エクセルをそのまま上書き保存しても、アクセスのデータが消えるわけではないので、再度、実行すれば大丈夫なのですが、
問題は、エクセルデータの消去に時間がかかりすぎるということです。

現在、クエリのデータは3000行以上あるため、コマンドをクリックしてから、
砂時計状態が数分間続き、待ちきれずにエクセルを終了させると、
別のエラー（リンクされているExcelの～～が切断されました）が表示されます。

このアクセスは複数人で使用するため、もし誰かがエクセルファイルを開いている状態で実行しても、
エクセルのデータを消去せずに、すぐに「出力先のファイルが開かれています」というメッセージを表示させることはできないでしょうか？

回答No.3

kurodai2

2です。
そうでしたか
同じような質問がありました
http://oshiete1.goo.ne.jp/kotaeru.php3?q=1392761

参考URL：

http://oshiete1.goo.ne.jp/kotaeru.php3?q=1392761

お礼

ありがとうございました。

「Name b As b」を記入することによって、
エクセルファイルが開いていた場合、
すぐにエラー番号「75」が表示されるので、
希望どおりの動作をすることができました。

なお、エクセルファイルが存在しない場合は、
ファイルが自動的に作成されなくなりましたが、
エラー番号「53」が表示されるので、その場合も、
「TransferSpreadsheet」の処理を記入することで解決しました。

むしろ、最初は「ファイルを作成しました。」、
２回目以降は「データを出力しました。」、
というようにメッセージを分けることができたので、
よりいい物が完成し、嬉しいです。

本当に感謝しています。ありがとうございました。

回答No.2

kurodai2

同じファイル名で書き出すという仕様ですよね。
と言う事は、既存EXCELファイルは無くなってもよいので
・書き出すファイル名が存在するかチェック
・存在しなければ、書き出し
・存在すれば、削除を行う
・正常に削除できれば、書き出し
・削除できなければ、「使用中」のメッセージを表示し
　書き出し処理を抜ける。
上記のような、判断で問題ないともいます。

補足

別シートにピボットテーブルがあったり、変更した書式がリセットされるため、
既存EXCELファイルが削除されるのは、非常に都合が悪いです。