お世話になります。
AccessからExcelへの読み出し、書き出しでの効率化に関して、
特に書き出しについてご相談させてください。
仕様：
１．Accessからある条件でデータ抽出してExcelにデータを転記する
２．AccessとExecelのカラム名は一致しているが、Excelには計算列（ユーザー任意）がランダムに存在する
計算列を除外してAccessのデータだけ出力したい
３．書き出すレコード数は数百件程度、列数は250程度ある
現状：
１．ダイアログでExcelファイルを選択し、オートメーションでExcelを開く、
２．Excelの対象シートの行タイトルのある行について、
1列目から最終列まで走査して、列名称と最初のデータ行のHasFormulaプロパティを参照し、
HasFormulaプロパティがFalseのものは、SQLの列情報を生成するため、タイトル行の列名称をカンマで繋げる、
Trueものはダミー列として繋げるとともに、列番号と最初のデータ行の関数（Formulaプロパティ）をAddNewでワークテーブルに格納
３．strSQL="select ２のフィールド定義 from テーブル where 都度設定する条件"
４．３のSQLを基にレコードセットを取得し、CopyFromRecordsetで貼り付け（関数列は一旦クリアしてしまう）
５．２のワークテーブルの列番号を基に、関数を復元（最初のデータセルにFormulaとしてはりつけ、最終行までオートフィル）
以上のように、AccessとExcelの列名称は一致しているものの、ユーザーによってExcelの列の並び順が異なるうえ、
任意に作業列を設定していています。関数列はクエリでデータ無しのダミー列として一旦書き出した後、関数を復元しています。
かなり苦しいロジックだと思っています。より効率的な方法はないでしょうか？
仕様に関していくつか曖昧な点があるので確認させてください。
Excelのシートには既にデータや計算式が入力されているようですが、
Accessのデータは、シートの最終行の後に追記するということでしょうか。
また、Excelのシートのデータ範囲にテーブル定義をしてますか。
AccessとExecelのカラム名の数も一致してますか。
それともExecelにはあるがAccessにはないカラム名が存在しますか。
例えば、「計算列（ユーザー任意）」はAccessには存在しないのでしょうか。
ご回答ありがとうございます。
一つ目のご質問ですが、最終行以下に追記します。また、テーブル定義はしていません。
二つ目のご質問ですが、後者のご認識の通りです。列数は一致しませんし、並び順も一致しません。
どうぞよろしくお願いします。
エクセルのカラム名と１行目に式があるかどうかをチェックしながらSQLが生成できたら、
それをレコードセットとして開いて、CopyFromRecordsetで貼り付ければいいですよね。
計算列はNullの演算フィールドにしておけばいいでしょう。
貼り付けた後、計算列はオートフィルで上から埋めるようにすればいいのでは。
ワークテーブルがなぜ必要なのでしょうか。
ありがとうございます。
最終行に追加する場合は、既存の関数列にデータが残っているので、それでもいいと思いますが、初期のデータ追加の場合も考慮すると、関数が、消えてしまうので、
統一的なロジックとして、そのように対応したつもりです。つまり、一番上の関数が消えてしまわないように、ワークに残しました。
なるほど、初めて入力する場合も考慮すると式は退避させておく必要はありますね。
ならば、自分がするとしてもそのような設計になると思います。
他に、計算列以外を1列ずつCopyFromRecordsetという方法も考えられますが、列数が多いと遅くなりそうです。
どうもありがとうございます。
自分のやり方に自身がなかった、
あるいは、もしかしたら私の思いもつかないやり方があるのかも？と思って相談させていただきましたので、hatena様もそのようにされるというのをお聞きできて大変参考になりました。
[初期のデータ追加]フラグを用意して
＜最終行に追加＞＜計算列オートフィル＞
if [初期のデータ追加] then 最初のデータ行目を削除
とかやればワークテーブルも関数の復元もいらないんじゃないですかね