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

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

ただいまの
回答率

87.59%

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

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,466

score 2

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • yukihara

    2021/04/13 17:32

    再度のご連絡を頂き、ありがとうございました。

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

    キャンセル

  • sazi

    2021/04/14 09:26

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

    キャンセル

  • yukihara

    2021/04/14 09:40

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

    キャンセル

回答 1

checkベストアンサー

+1

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

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

結果はエラーの内容が異なりました。
![イメージ説明](9af0e3cfab29639075a4742ea4182156.png)

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

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

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

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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/04/14 13:39

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

    キャンセル

  • 2021/04/14 14:03

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

    キャンセル

  • 2021/04/14 15:04

    ありがとうございます!
    上記でお教え頂きました「Excelへの出力(DAO)」のコードを利用すると確かに使用いたしませんね。

    今は上記コード(Excelへの出力(DAO))でデータベースの相対パスの指定方法を考えております!

    キャンセル

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

  • ただいまの回答率 87.59%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る