質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

Q&A

解決済

1回答

7417閲覧

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

yukihara

総合スコア2

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Access

Accessはマイクロソフトによるリレーショナルデータベース管理システムです。オブジェクト指向のアプリケーション作成に対応しており、テーブルや編集をはじめ、クエリ生成、入力フォーム作成、レポート作成など一通りの機能を備えています。

マクロ

定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

0グッド

0クリップ

投稿2021/04/13 05:09

編集2021/04/13 07:18

いつも大変お世話になっております。
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です。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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、のみでした。 返答になっておりますでしょうか・・・?
guest

回答1

0

ベストアンサー

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

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

結果はエラーの内容が異なりました。
イメージ説明

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

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

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

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

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

投稿2021/04/14 00:36

編集2021/04/14 04:10
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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
sazi

2021/04/14 04:10

追記しました。
yukihara

2021/04/14 04:39

「access recordset シートへコピー」という検索ワードも思いつかなかったので、とても勉強になります! 参照先を拝見して試してもできなかったら、Excel関係のスレッドで質問させて頂きます。 この度は、誠にありがとうございました!!
sazi

2021/04/14 05:03

シート編集の際には、エクスポートはしないので、「DoCmd.TransferSpreadsheet」は使用しませんので、念のため。
yukihara

2021/04/14 06:04

ありがとうございます! 上記でお教え頂きました「Excelへの出力(DAO)」のコードを利用すると確かに使用いたしませんね。 今は上記コード(Excelへの出力(DAO))でデータベースの相対パスの指定方法を考えております!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問