AccessのクエリをExcel(マクロ形式で保存)にエクスポートするVBA

いつも大変お世話になっております。
VBA(AccessもExcelも)初心者です。
今回はAccessのクエリをExcel(マクロ形式)にエクスポートしようとVBAを作成したところ、エラーになるためご相談申し上げました。

VBAがよく分かっていないので、お見苦しいところが多いかもしれません。
また、そもそもこのVBAでは駄目でしたり他に良い方法がございましたら、大変恐縮ですがご教授頂けますと幸いです。

前提・実現したいこと

◆前提　1
①セキュリティのかかったODBCが元のテーブル
②セキュリティは外せないので、これを元にアクションクエリ1を作成
③アクションクエリ1を実行して、テーブル1を作成
④テーブル1を元にクエリ1を作成

◆前提　2
AccessとExcelは同じフォルダに保存しています。

◆実現したいこと
フォーム1で抽出条件を絞ったクエリ1をマクロ形式で保存されたExcelの「一覧」シートにエクスポートしたいです。
もしかして、エクスポートでなくエクセルを立ち上げてコピペするVBAの方が良いのでしょうか・・・？

発生している問題・エラーメッセージ

実行時エラー'3274'
外部テーブルのフォーマットが正しくありません。

下記、ソースコードの「'エクスポート」でエラーとなります。

該当のソースコード

Option Compare Database

Private Sub cmd_Click()

Dim path As String
Dim db As DAO.Datebase
Dim rs As DAO.Recordset

path = aplication.CurrentProject.path & "\Book1.xlsm"

'エクスポート
DoCmd.TrnsferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ1", path, True, "一覧"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Open path

Application.Quit

End Sub

試したこと

　参照元テーブルがODBCで、セキュリティが解除できないのがネックになっているようなので、別途テーブル化してみたのですが、フォーマットが正しくありませんと表示されます。

投稿後に追加で確認

　path = aplication.CurrentProject.path & "\Book1.xlsm"のブック形式を「.xlsx」にしたところ、問題なくエクスポートできました。
今回は「.xlsm」へのエクスポートがしたいのですが・・・。
なお、マイクロソフトのオフィシャルページでは対応可能な形式でした。
https://docs.microsoft.com/ja-jp/office/vba/api/access.acspreadsheettype

補足情報（FW/ツールのバージョンなど）

Access2016です。

sazi

2021/04/13 05:44

エクスポートを手動で行ってエラーが出ない事を確認済みでしょうか？

yukihara

2021/04/13 05:46

早速のご確認を頂き、ありがとうございます。いま確認してみましたが、Excel(マクロ保存ではない)へのエクスポートは出来ます。エラーも表示されませんでした。

sazi

2021/04/13 08:26 編集

いえ、.xlsmに手動でエクスポートしてエラーになるかどうかです。手動でもエラーになるなら、一旦.xlsxに保存して、.xlsmでそれを参照するというような方法考えないと駄目かもしれませんし。指定した.xmlの問題かもしれませんので、空の.xlsmに出力するのも試された方が良いと思います。

yukihara

2021/04/13 08:32

再度のご連絡を頂き、ありがとうございました。知識がなくて恐縮なのですが、教えてください。手動とはリボンの外部データからエクスポート機能を使用してのエクスポート、のことで宜しいでしょか？なお、その機能では.xlsmへの出力が選択できませんでした。

sazi

2021/04/14 00:26

出力したいものを右クリックでもいいですけど。試した内容で回答します。

yukihara

2021/04/14 00:40

ご返答を頂き、ありがとうございました。リボンからでも右クリックからでも、マクロ付きExcel(.xlsm)はエクスポート先として選択できませんでした。エクスポート先として選択できるのは、.xlsb、.xlsx、.xls、のみでした。返答になっておりますでしょうか・・・？

行動規範の内容に同意します

回答1件

ベストアンサー

先ず、手動でのエクスポート
出力先を.xslmにしましたが、.xslxに強制的出力されます。

次にVBAで試しました。※質問のコードはTransferSpreadsheetのスペルが誤っていますが大丈夫ですか？

結果はエラーの内容が異なりました。

以上の事から、TransferSpreadsheetのacSpreadsheetTypeExcel12Xmlは「Microsoft Excel 2010/2013/2016 XML 形式」というXMLの形式という事だけで、.xslmには対応していないと思われます。

日本語文献は見当たりませんでしたが、海外サイトでももっぱら出来ないという記事しか見当たりません。
対応としては、エクセルブックを開いて編集するというものでした。

「外部テーブルのフォーマットが正しくありません。」というメッセージについては、別物という気がします。

クエリーの結果をエクセルを開いて貼り付ける処理は以下が参考になると思います。
Excelへの出力（DAO）

※「access recordset シートへコピー」辺りで検索すれば他にも事例は見つかります。

投稿2021/04/14 00:36

編集2021/04/14 04:10

sazi

総合スコア25327

yukihara

2021/04/14 02:04

迅速なご回答を頂き、ありがとうございました！＞TransferSpreadsheetのスペルが誤っている　恥ずかしながら投稿時にaを削除しておりました。ご指摘を頂き、ありがとうございます！　はい、Access上ではスペルは正しかったので大丈夫でございます。＞.xslmには対応していないと思われます。　ご多忙の中、ご検証を頂き誠にありがとうございました。　マイクロソフトのオフィシャルページにAcSpreadSheetType 列挙型 (Access)について、　名前：acSpreadsheetTypeExcel12Xml、値：10、説明：Microsoft Excel 2010/2013/2016 XML 形式 (.xlsx、.xlsm、.xlsb)と書かれていたので出来るものとばかり思っておりました・・・。 ◆申し訳ございません、AccessからExcelへのスムーズな処理のつなぎ方が全く判らず、引き続きご教授をお願いいたします。 ◆目指しているのは、Accessのフォームのボタンをクリック→クエリの情報を絞り込み→クエリの内容をマクロ形式のExcelに貼付すること、です。 ◆したいこと 1.　出力用Excelブック(.xlsx)のBook2に一時的にエクスポートする 2.　マクロ付きExcelのBook1を立ち上げる 3.　マクロ付きExcelに一覧シートを貼付する 4.　出力用Excelブックを閉じる ◆初心者の考え方で恐縮ですが、質問時のコードをそのまま流用して下記のようなコードを考えてみました。エクスポートまではできるのですが、それ以降が出来ておりません。正しいコードをご教授頂けますと幸いです。 Option Compare Database Private Sub cmd_Click() Dim path As String Dim db As DAO.Datebase Dim rs As DAO.Recordset path = aplication.CurrentProject.path & "\Book2.xlsx" 'エクスポート DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "クエリ1", path, True, "一覧" Set objExcel = CreateObject("Excel.Application") objExcel.Visible = True objExcel.Workbooks.Open path Application.Quit '一覧シートをコピーする Worksheets("一覧").Copy 'マクロ付きExcelを立ち上げる Workbooks.Open ThisWorkbook.Path & "\Book1.xlsm" 'マクロ付きExcelに一覧シートを貼付する Workbooks("Book2.xlsx").Worksheets("一覧").Copy After:=Workbooks("Book1.xlsm").Worksheets("一覧") '出力用Excelブックを閉じる Workbooks("Book2.xlsx").Close savechanges:=False End Sub