Access2000VBA・Excel2000VBA独学~SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」~Microsoft Query(QueryTableオブジェクト)を利用する場合~
  
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。

※参考Webページ
http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html
https://blog.goo.ne.jp/end-u/e/2fd0b68b8cb29e4b80dc7182a800fc9c

※参考記事
DAO、ADO、Microsoft Query(ExcelVBAのQueryTableオブジェクト)、では「読み込み」だけでなく、「閉じたまま」の「複雑条件集計」や「書き込み」もできます。
ただし、Excelの場合はなんと「削除」ができないので対応策が必要です。
対応策はこちら。『できないこととExcelVBAレジェンドさんたちの怠慢』。(←その他の「できないこと」も書いてあります。なお、「あえて削除しない」ことも結構あります。不正対策や整合性維持のためです。基本、削除フラグを立てるだけでも対応できます。もちろん、読み込みだけに使っても構いません。)

Excel2010で、『 開かれていない閉じたままのブック・Excel(xlsやxlsx・xlsm)ファイルのデータ 』を読み込むだけでなく、書き込む方法の一覧表(DAOやADOにて)
~SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」~Microsoft Query(QueryTableオブジェクト)を利用する場合~
ADO:Excel2010で、開かれていない閉じたままのブック・Excel(xls)データをできるだけ速く読み込む方法ーその2(ADOにて)
Access2000VBA・Excel2000VBA独学~別の閉じたExcelファイルを ”閉じたまま” 読み込みや書き込をする方法~5つ
  
  

目次

  
  
★ はじめに

Excelで、SQLを使って閉じたままのファイルの内容を、「閉じたまま書き換える」ことができます。

ここでは、Microsorf Queryを使って、既存のxlsファイルのある表を書き換えるテスト結果をご紹介します。

これはつまり、「Microsorf Queryにて、シート上になんらかの他のxlsの表データが表示されている状態」を前提としています。

Microsorf Queryを使いつつ、SQLの実行をすると、プログラムコードが一番少なくなるのではないかと思います。
なので、ちょっとした書き換えなどにつかえると思います。
  
  

★ サンプルファイルのダウンロード

(a)書換先ファイルと操作側ファイルがともにxls拡張子のファイルの場合
http://euc-access-excel-db.com/00000WPZIP/sql-write-on-msqry.zip

(b)書換先ファイルと操作側ファイルがともにxlsm拡張子のファイルの場合

  
  
★ (a)について
解凍して、「test88」というフォルダをDドライブにコピペします。
「test03.xls」で色々と試せます。

「test03.xls」のSheet1には、下図のように、「test88」というフォルダの中の、「test01.xls」というxlsの同じくSheet1を読みに行ったMicrosoftQueryの結果の表が、表示されています。

その「test01.xls」の内容を、「test03.xls」からSQLを使って、「test01.xls」が「閉じたまま」、書き換えます。

  
【テストの方法】

 基本、「test03.xls」の中の、「Module1」の「test11()」プロシージャを
 実行してテストしますが、その前に、
 同じく「test03.xls」の「Module1」の中の、「test98()」プロシージャの、

  sqlstr02 = "SET 列a = 'qag' "

 という行の 'qag' を 'qaaaaaaaaaag' とか、適当な内容に書き換えてから
 「test11()」プロシージャを実行します。
 すると、「test03.xls」の「Sheet1」シートに、'qaaaaaaaaaag' と表示されます。

  
  
★ Microsoft Queryにて、「test01.xls」のSheet1を覗きに行った結果表を作る方法
今現在のサンプルには、「D:\test88\」フォルダの「test01.xls」のSheet1の内容が、「test03.xls」のSheet1に表示されています。

それが「Microsoft Queryの結果の表」なんですけれども、もし、その結果の表を「test03.xls」のSheet2に表示したかったり、今のSheet1の結果表をいったん消して作り直したかったり、という場合は・・・、
(イ)手作業で作る・作りなおす。
(ロ)VBAで作る・作りなおす。
でやります。

この場合、一回作れば、(同じ表を常に表示したいのであれば)それ以降は基本的にはやる必要はありません。

具体的には、次のようにします。

この

(イ)手作業で作る・作りなおす。

  
  
(ロ)VBAで作る・作りなおす。
ここでは、「もしSheet2にMicrosoftQueryの結果の表(=QueryTablesオブジェクト)が作って無かったら、作って表示させる・・・」、というプログラム内容となっています。

が、プログラム中の「Worksheets("Sheet2").」を「Activesheet.」に置換等をして書き換えると、Sheet2だけでなく、現在表示しているシートに結果の表が生成されます。

(あと、「B2」セルを起点として表が表示される設定になっていますので、もし「A1」セルを起点としたかったら「Destination:=Worksheets("Sheet2").Range("B2")」の行の「B2」を「A1」に書き換えてから実行してください。)

  
  
★ 実際の書き換えプログラムの例:(a)の場合

▼(01)汎用化していないコードの例
コメントがめっちゃ多いので、無駄だったら全部消して見てみてください。
サンプルファイルの「Module1」の内容です。
あまり汎用化してない、テスト的なプログラムコードです。

  

  
  
▼(02)少し汎用化したコードの例
こちらもコメントがめっちゃ多いので、無駄だったら全部消して見てみてください。
サンプルファイルの「Module2」の内容です。