Access2007のエキスポートについて

Access2007でマクロを使ってExcel2007のファイル(xlsx)形式へエキスポートします。その際、
・「ワークシート変換」を指定
・ワークシートの種類に「Excel Work book」
・出力ファイルパスに「(絶対パス)\(ファイル名).xlsx」
としました。
既にAccess2007のテーブルには、65535行を超える200000行のレコードが入っていることが確認できてます。
マクロを実行すると、「指定範囲を広げることができません」とダイアログが出てしまい、マクロが中断し、異常終了してしまいます。

windowsXP SP2 pentium4を使用。

異常終了せず、全レコードをファイルにエキスポートする方法はありませんか？
宜しくおねがいいたします。

No.2 ベストアンサー 回答者： DexMachina 回答日時： 2009/06/20 22:58

> 「指定範囲を広げることができません」とダイアログが出てしまい、マクロが中断し、

> 異常終了してしまいます。

ここで言われている『異常終了』というのは、「以降のマクロが一切処理されなかった」
という意味でよろしいでしょうか。
(Accessアプリケーションが落ちた、といったことではなく、と)

それでよろしければ、多分こちらで現象を再現できました。
指定したファイルに、既に同名のワークシートが(そしてそこにデータが)存在するために、
ご質問のエラーになったものと思われます。

上記ダイアログで『Ok』ボタンの右隣に出たはずの『ヘルプ』ボタンを押すと、以下の
文が表示されます：

> 指定範囲を広げることはできません。(エラー3434)
> Microsoft Excel Version 5.0 の指定範囲に行を追加する際、値が入っているセルを
> 検出しました。追加処理によりこのようなセルを上書きすることはできません。

・・・少なくともAccess2003の『ワークシート変換』では、出力しようとしたワークシートが
既存のものとかぶる場合は上書きされていたはずなのですが、Access2007ではその辺り
の仕様が変わってしまったようです。

従って、解決策としては、出力しようとしているxlsxファイルから、当該シートを予め削除
しておく、ということになるかと思います。
(VBAなら自動化できますがマクロでは無理なので、手作業になってしまいますが(汗))

～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～～
No.1の方の回答について：

マクロの『ワークシート変換』は、VBAの『DoCmd.TransferSpreadsheet』と(ほぼ)
同じものです(前者を、Accessの「VBAに変換」機能を通すと、後者に変換される)。

で、回答中の「acSpreadsheetTypeExcel12」は、「Excel Binary Workbook」を
指定する定数ですので、出力ファイルは指定された「xlsb」ファイル(Excel バイナリ ワーク
シート形式)で出力されるため、拡張子が「xlsx」だとエラーになってしまいます。
TransferSpreadsheetで「Excelワークシート形式」(拡張子xlsx)を出力する場合は、
「acSpreadsheetTypeExcel12xml」を指定します。

この回答へのお礼

ご回答ありがとうございます。

>> 「指定範囲を広げることができません」とダイアログが出てしまい、マクロが中断し、
>> 異常終了してしまいます。
>
>ここで言われている『異常終了』というのは、「以降のマクロが一切処理されなかった」
>という意味でよろしいでしょうか。
>(Accessアプリケーションが落ちた、といったことではなく、と)

はい、マクロが中断されただけで、Accessアプリケーションは動作しています。


実は、手作業で出力ファイル削除してからも再実行してみましたが、
同じエラーが発生していました．．．

『ヘルプ』ボタンのエラー詳細については、再度確認してみます。


65535行は試していないですが、3万行程度で実施すると正常に動作していました。

お礼日時：2009/06/21 12:36

No.3 回答者： 30246kiku 回答日時： 2009/06/20 23:36

＃１です。

acSpreadsheetTypeExcel12　としたのは、サイズが小さい方がいいのかな程度のものです。

テーブル構成「ID」「F1」～「F10」
IDはオートナンバー、F1～F10はテキスト型、サイズ100で内容は「あいうえおかきくけこ」の10文字。
レコード数は200000レコード。

acSpreadsheetTypeExcel12 で出力した xlsb ファイルは、1462272バイト。
acSpreadsheetTypeExcel12Xml で出力した xlsx ファイルは、8974336バイトでした。

どちらでも 65535行超え出来るようですので、ご自身で判断してください。
（出力時の処理時間は違いがあるようですが）
（ちょっと書き変えればいいので、どちらでも検証できると思います）


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

No.1 回答者： 30246kiku 回答日時： 2009/06/20 14:42

DoCmd.TransferSpreadsheet を使われてはいかがでしょう。

（マクロはあまり使ったことがないので、どのような指定になるのかは分かりません）

コマンドボタン「btn01」をクリックした時、テーブル「T_T3」を「E:\tmp\abc」に出力する時の例です。
出力されたファイルの拡張子は「xlsb」になりました。

Private Sub btn01_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "T_T3", "E:\tmp\abc", True
End Sub

拡張子「xlsx」を付けた「E:\tmp\abc.xlsx」で実行すると、
ファイルは出来上がりますが、ファイルダブルクリック時以下のエラーになります。

Excel でファイル 'abc.xlsx' を開くことができません。ファイル形式またはファイル拡張子が正しくありません。ファイルが破損しておらず、ファイル拡張子とファイル形式が一致していることを確認してください。

拡張子を「xlsb」に変更すると開けます。

ファイル名には拡張子を付けないか、「xlsb」を付けるのだと思います。

この回答へのお礼

回答ありがとうございます。
どうなるか是非試してみたいと思います。

お礼日時：2009/06/21 12:18