Access SQL Access>>Excel Select INTO で テーブル クエリをxlsbに出力する Access Query Can Export xlsb file from Table or Query With Acrion Query Select Into

従来Excel8.0形式でVBAはあった

http://www.ken3.org/asp/backno/asp054.html
この手法はよく知られています。
なお、ここで出力されるクエリは選択クエリです。
集計やユニオンは未実験です。

しかしExcel12.0は定かではない

どこにも見つからない。
そこで結論から書きます。

Access 2019 64bit accdb形式

SELECT TabelOrQuery.* INTO [Excel 12.0;ReadOnly=False;HDR=YES;IMEX=0;DATABASE=.\sample.xlsb].Sheet1
FROM TabelOrQuery;

Excel 8.0は簡単

SELECT TabelOrQuery.* INTO [Excel 8.0;IMEX=0;DATABASE=.\sample.xls].Sheet1
FROM TabelOrQuery;

Excel 5.0も可能

SELECT T_Mas.* INTO [Excel 5.0;ReadOnly=False;HDR=YES;IMEX=0;DATABASE=.\dbtestfolder\sample201.xls].Sheet1
FROM T_Mas;

流石にExcel 4.0以前は無理
ImportされたISAMファイルが見つかりませんエラーがでます。
このエラーが出ると一度データベースを終了させないと調子が悪くなるときがあります。
どうも一度接続を握ったまま話さなくなる時があるようです。

特にExcel 12.0は不安定

バイナリでやっていますが、xlsx形式はファイルが壊れているという警報がでます。xlsbでやってもエラーを修復するときがあります。

エラーファイルの出力場所と分析

大抵の場合失敗するので、xlsbはこわれたファイルとしてエラーが出力されます。

%LOCALAPPDATA%\temp\
にxml形式で出力されます。
あとで削除する必要があります。
ファイル名は
error000000_01.xml
というエラーのあとに数字が6付きます。

XMLの内容からUTF-8で出力されているらしい

<?xml version="1.0" encoding="UTF-8" standalone="****"?>
<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error000000_01.xml</logFileName><summary>ファイル 'fullpath\exportFromTable.xlsb' にエラーが検出されました</summary><removedRecords><removedRecord>削除されたレコード: /xl/workbook.bin パーツ内の名前付き範囲 (ブック)</removedRecord></removedRecords></recoveryLog>

ここで重要なのはUTF-8で出力されていることです。つまり、UNICODE文字を使っていても文字化けをする率は少ないということがわかります。

Schema.iniはつくられない

Schema.iniは出力されないため、テーブルの構造を出そうとしてもうまくいきません。

サンプルは相対パス表記

通常はフルパスです。最後に\がついてもつかなくても構いません。
これはなんの相対パスになるかというとAccessのオプションで設定できる既定のディレクトリを起点とした相対パスです。
これはこういうことも可能だという曲芸であって、通常はフルパスがおすすめです。これは公式のマニュアルにも書いていない技法です。

クエリに書くときの全般的なポイント

Provider=がいらない

これはすでに接続しているというか開いているため不要となります。

Extended Propertiesと書かない

その内容を普通に書きます。VBAでAdoのConnectionを開くより簡単です。

SELECT TabelOrQuery.* とテーブルまたはクエリの名前に続けてピリオドアスタリスク

もちろんここでフィールドを抜き出すことも可能です。
しかし、全てのフィールドであれば通常はアスタリスクだけでいいのですが、ここは必ず明記が必要です。

[Excel 12.0;ReadOnly=False;HDR=YES;IMEX=0;DATABASE=.\sample.xlsb].Sheet1

ここは非常に重要です

Excel 12.0であって Excel 12.0 xmlではありません。

またExcel12もダメです。
半角をあけて表記しましょう。半角は一つです。正確に書かないとエラーになります。
またExcel 12.0 xmlではありません

拡張子はxlsb

xlsxではファイルが壊れて開けませんで終わります。
xlsbだと修復されて開けるか、そのまま開けます。
おそらく、ADOはバイナリのxmlでデータをはき、xlsbを通してxlsxに変わっているものと思われます。

ReadOnly=False;HDR=YES;IMEX=0;

このへんはあまり効いていない感じですが、エラーにはなりません。

].Sheet1ここが重要

上記のサイトを見ると、
]!Sheet1 ].[Sheet1$]``]#[Sheet1]とか書いてしまいそうになりますが
全部失敗します。
特に$は不要です。#も意味がありません。
Select intoは基本的には新しいファイルを作るため（極稀に作らない場合もあるし、シート名を変えると作らない）デフォルトのSheet1に出力されるようです。
もっとも既存のファイルがあれば普通はエラーになります。しかしSelect Intoは Excelファイルが「なければ作っているだけで、Excelファイルというデータベースにテーブルを作っている命令といえるようです。
このため、Select intoは新しいファイルを作ると考えるとそうではないときがあります。
]..Sheet1とかくとならないときもありますが、これはファイルがデータベースだからです。

シート名Sheet1にはプラケット（角カッコ）は不要です

あるとエラーになります。基本的にSheet1にしか出力しません。
しかしなぜか.Sheet1に出力されるときもあります。
xlsb].[.Sheet1]と書くとそうなります。

現在のところ範囲指定はできていません。

.Sheet1$B1:C1こうした書き方はテーブル名が見つかりませんとなります。
やはりテーブルとして捉えられていることがわかります。

].Sheet1#B1:D2

今前提としているテーブルについて記載していませんでしたが、IDとFNewとFOldしかありません。
またレコードも一つです。
項目名を入れれば3列×2行なので入るはずです。
しかしこれは
Sheet1.B1_D2というシートにA1から出力されます。
もともとこの記載方法は「Excel＝>accdb,mdb」へインポートするときに「参照先」であって「出力先」ではありません。
失敗でしたが、シートがテーブルとしてとらえられていることがわかります。

結論

Accessのテーブル、選択クエリからExcelに対してクエリを用いてxlsb出力することは可能である。
拡張子がxlsbで、記述の条件が厳しい。
既存のファイルがあるとエラーにほぼなるため、連続して使えない。
おそらく出力できるフィールドタイプも限界がある。
Excel 8.0でxlsは簡単で、この方法は使いやすい。
しかしWhereやフィールドを指定すればコンパクトに出力できる。
直接ここで絞り込まなくても絞り込んだ選択クエリを作って保存し、そこからSelectIntoに書き換えても良いし、Select Intoを別に作ってExportさせてもよい。
DoCmdを使わなくてもテーブルをExportできるためVBSでAccessを開かなくてもテーブルが出力できる可能性がある。
また、テーブルの構造がわからなくてもExportできる。
ユニコードで出力される(xlsb)ため文字化けのおそれが少ない。