Access2000VBA・Excel2000VBA独学~ADOにて、「SQL」や「レコードセット」で自ファイル(xlsm)の表のデータをループを使わずに一括書き換えするテスト(自ファイル以外の他の閉じたファイルの閉じたままの書き換えにも使えます~
  
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
  
  
※参考記事
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つ
  
  

★ はじめに

  
  
★ サンプルダウンロード
https://euc-access-excel-db.com/00000WPZIP/ado_write_test01.zip
  
  
★ ダウンロードしたら
デスクトップなど、好きな場所に置いてテストしてみてください。
ファイルを開いたら「編集を有効にする」ボタンを押してください。
「Sheet3」が開きますので、Sheet3を開いたままじゃないとテストできません。
VBEを開いて、「コメントなし最小限」「コメントや他のプロシージャあり」というモジュールのいずれも「OperationTest02()」プロシージャ を実行してみてください。

一括書き換えができるのは、「コメントや他のプロシージャあり」モジュールの、「UpdateByADO_ACE01()」というプロシージャです。

これの、

 CmdSqlStr01 = ""
 CmdSqlStr01 = CmdSqlStr01 & "UPDATE Sheet3$"
 CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 10"
 CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '白みそ';"

という部分の、「相対重量 = 10"」のところの「10」を100に変更したり、「白みそ」を別のみその名前に変えたりして実行してみてください。

ループを使わなくても、原則としては上記のような短い命令文にて、指定した行が一括書き換えされるのが見られると思います。
  
  
※なお、「コメントや他のプロシージャあり」モジュールの、「UpdateByADO_ACE01()」プロシージャを実行すると、「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークを出すことができます。
その後、「UpdateByADO_ACE01()」プロシージャの「End Sub」の直前(「Set Cn = Nothing」とのあいだ)に、「Call UsedLastRowPfixChrDel01」というコードをコピペして再実行すると、グリーンの三角エラーマークが消えることを確認できます。
  
  
★ 「Sheet3」の列の入れ替えやレコード削除などの扱いについて
レコード削除だけ、行番号を選択してから削除してください。
セルを選択して行削除すると、次回にレコード追加した時にそこが空白になってしまいます。

列の入れ替えは、列番号単位でもセル単位でも入れ替えできます。
列を入れ替えても、ちゃんと、その列に指定したデータが入力されます。
行の入れ替えは、行番号単位だけでの入れ替えがいいのだと思います(テストしてませんすみません)。

空白行や空白列は作らないでください。

表自体も、全体として移動するならOKです。
ただ、表の外の空白セルには、表の名前とか注意書きとかはなにも書かないでください。
書きたかったら、横書きテキストボックスに書いてください。
レコード追加や書き換えができなくなります。
  
  
★ ADOでの「Sheet3」(つまり=システムテーブル)の操作でできること

列の入れ替えをしても列名でデータを操作できます。(追加、削除、書き換えなど)
行の入れ替えをしても問題なし。列名でデータを操作できます。
表(=システムテーブル)自体も、全体として移動するならOKです。(ただしただ、表の外の空白セルには、表の名前とか注意書きとかはなにも書かないでください。)

データを操作したいファイルが閉じていても書き換えできます。

LAN上のファイルも扱えます。もちろん閉じていても扱えます。

ADOのオブジェクト(レコードセット)などでループの書き換えが可能です。For Each なども多分使えます。列の入れ替えをしても列名で書き換え可能です。

SQLにての書き換え、追加、削除も可能です。列の入れ替えをしても列名で書き換え可能です。

読み込みなら xls、xksx、xlsm、が扱えると思います。
書き込みはxksx、xlsm、がいいかも?
xlsの書き込みはDAOのほうがいいかもしれません。
DAOも上記と似たようなことができると思います。

ADOXで、テーブル作成とかできるかも?(未確認)

  
  
★ ADOでの「Sheet3」(つまり=システムテーブル)の操作でできないこと

(01)セル書式の設定の自動修正
既存データの書き換えや新規レコード追加などでセル書式が変わってしまうことがあります。
でも、それをプログラムで自動的に修正することが難しそうです。

(02)数式の入力(できないかも?できるかも?)

数式自体は文字列データのため、数値型の列に数式を入れようとした時点で、エラーになります。
なので、基本、数式入力は「ADO+SQLでは」できなさそうです。
何かほかの方法があるかもしれませんが・・・。
文字列型の列には、数式を入れることができます。
ただし、すぐに反映(再計算?)させる方法が見つかりませんでした。

※以下、以前に書いたものです。

文字列として入力してから接頭辞の「'」を取ればOKかもしれません。
「数式としても文字列を入力すること」だけはできます。
ただ、現時点のテストでは再計算ができませんでした。

なお、数式を入力するには、「OperationTest02()」プロシージャにて、たとえば
「Data01(0) = "'合わせみそ'"」をコメントアウトして、
「Data01(0) = "'= B7'"」といった感じで書き足してみるとテストできます。

もしかしたら「UsedLastRowPfixChrDel01()」プロシージャでFor Each のループの最後(「Next c」の直前)に、「ActiveCell.Value = ActiveCell.Value」で、入力した数式が反映されるかも?。と思い、テストしてみましたがダメでした。
Fomulaとかかも?
わかりませんが、ただ、ここで何か書き足してエラーが出るようなら、この行は消してください。
当方のテストではうまくいかず、まだちゃんとした方法がわかりませんでした。

ただしテストするなら基本、
数値の列に入れる数式は、数式が返す値が数値に、
文字列の列に入れる数式は、数式が返す値が文字列に
・・と、同じデータ仮名になるようにしないと、いけません。

ある列に、異なるデータ型となってしまう数式を入れると、そのあとから、データの追加や修正がエラーでできなくなります。

(03)表の外のセルに表の名前や注意書きを入力すること
表として認識できなくなるので(列名自体が認識できなくなるので)、それはできません。

(04)1シート内に複数の表を作成すること
ADOやSQLで表を扱う場合は、1シートにつき1つの表でなければなりません。

※このような不都合を解決したい場合は、クラスモジュールにて、そういった操作もADO操作も両方ができるオブジェクトを自作すればできるかもしれません。(もちろん僕にはそんなことはできません。)
  
  
★ コメントを消したプログラムコード01
(自ファイルに対して、ADOで「SQL」を実行してくれるだけの自作関数)
※「SQL」を使うと、セルアドレス単位ではなく、列名や日本語条件で・しかも「ループせずに」 何百行でも何千行でも一括データ書き換えができてしまうので便利です。
※書き換えたい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。
※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。)

  
Excelの場合、接続の相手先のファイルがxlsx・xlsmの場合は、ADOのACE系のデータベースへの接続を使います。(xlsはDAOを使うほうがいいみたいです)それは相手が(今回のテストのように)「自ファイル」でもOKです。
また、「Extended Properties」は、「接続先のデータベースをどのように開くか?」の設定値ですが、これには色んな設定値があるんですけれども、それらをあえて書かずに、「"Extended Properties=""Excel 12.0"""(Extended Properties="Excel 12.0")」とだけ書くと、接続先のファイルの内容を書き換えることができます。(1行目のすべての列名もちゃんと認識されます。)
この場合、相手先が自ファイルではなく他のファイルでも もちろんOKですし、また、相手ファイルが「開いたまま」でも「閉じたまま」でもOKです。

※別のユーザーが開いている場合はテストしていません。今後機会があったらテストしてみたいと思います。

※このサンプルでは自ファイルのみを対象としていますが、他のファイルにも使えるようにしたいなら、「TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name」の部分や引数の部分などを作り変えます。初心者の方は、ご自分でもチャレンジしてみてください。(わからない方は、分かる人に聞いてみてください。)

※ADOやDAOは、ExcelだけでなくAccessやSQL Server、MySQLなどにも接続することができます。
 どちらかというとそれらに接続して使うことが多いです。
 Excelではあまり使われないみたいです。
 特に、「初心者本」では出てきません。
 ただ、ADO、DAO、MicrosoftQuery(QueryTableオブジェクト)のことを知らないと、
 無駄なクラスモジュール(自作オブジェクト)の作成をしてしまう恐れがあるので、
 あえて「初心者も」、「ExcelVBAの基本」として学んでおく必要があります。
 「SQL」も使えるので、すごく便利・かつ・重要なことなのに、
 なぜかレジェンドさんたちは教えてくれないんですけど、でも、例えば
 次項の例のように、「ループを使わずに・かつ・少ない可読性の高いコード」で、
 「何百行でも何千行でも一括書き換えができてしまう」ので、
 初心者の方でも、「さらっとは」絶対に知っておく必要があります。

 (※基本、1シートにつき1つの表で、一度作った表は絶対にセル移動させない、
   ということを守れば、
   「表に対するクラスモジュールでの自作オブジェクトの作成」は、
   特別な理由がない限り、必要ないと思います。
   SQLも使えてしまうADOやDAO以上の機能の各オブジェクトを
   自ら自作するなんて、とてつもなく大変だと思いますので・・・。
   ※「SQL」は、システム屋さんが使う、複式簿記と同じくらい有名な、
    ある意味「データ管理の基礎」「理論」「プログラム言語」「命令語句」です。
    Excel以外のデータベースソフトで使われることが多いです。
    Googleやトヨタ系の企業など大企業の社内システムや、
    Web上のほぼすべてのカートシステム、弥生や奉行などのソフト、などに
    使われています。詳しくはこちら→用語:SQL

※ただ、Excelの場合は、相手先のテーブル(シートの中の表)について、
 例えば「テーブルを作ったあとに そのテーブル丸ごとを変な風にセル移動させる」
 等々をすると、そのテーブルを正常に認識できなくなったり、
 本来書き換え可能なのに、それが不可能な状態になってしまったりします。
 表のデータを行番号丸ごとベースではなく、セルベースで消した時も
 おかしな動きになります。
 (他のデータベースではありえないExcel特有の現象。)
 なので、もし、「あえて」ADOと同じような機能(例えば列名ですべて操作できるとか)
 をクラスモジュールなどで作るのなら、そのような「トラブル」を回避するためのものを
 作る・・・、とか、
 「1シートに複数の表を作ってあってもそれを全て列名で操作できるようにする」、
 ・・・とか いうような恰好になるんだと思いいます。
  
  
★ コメントを消したプログラムコード02
(前項の自作関数を呼び出して自ファイルのデータをループを使わずに一括書き換えするテスト)
※書き換えたい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。
※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。)

  

この場合、以下のような書き換えの設定内容となります。
(「1シートに付き1つの表」・・・というかたちになっていることが前提です)

strSql01 = ""
strSql01 = strSql01 & "UPDATE 書き換えたいシートの名前$"
strSql01 = strSql01 & " SET 書き換えたい列の列名 = 書換値"
strSql01 = strSql01 & " WHERE 行を抽出するための基準としたい列名の列名 = 抽出条件値;"

「WHERE 行を抽出するための基準としたい列名の列名 = 抽出条件値」の行で、何百行あろうが、何千行あろうが、条件にあてはまる行を抜き出すので、それで、ループを使わずに一括書き換えが可能となります。
  
  
★ コメントを消したプログラムコード03
(最初の自作関数を呼び出して、自ファイルのデータに新規でレコード追加するテスト)
※追加したい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。
※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。)

'ほんとは1つ前の例のように、もっと短く書けるのですが、
'でも そうすると「見づらい」ため、あえて長ったらしくして、
'一応、「どの列の値をどう書き換えたか」などが分かりやすく
'「見える」ようにしてみました。
'ムダが多いかもしれませんが、こういう書き方もできるということで。。。
'もっといい案をご自分でも考えてみてください。

※Variant型の変数を使う場合は、値が入った直後にデータの型が自動的に設定されます。
が、その際に、ユーザーの「想定外の値」に設定されてしまうことがあるので、それがトラブルの原因になりやすいです。特にExcelでは「何も知らないエンドユーザーがセルに無茶苦茶に値を入力できてしまう」のでトラブルになりやすいです。(エラーが出ないこともあるので質が悪い。)
トラブルが起こったら、かならず「ローカルウィンドウ」+ステップ実行、あるいはTypeNameプロパティなどを使用して、どんなデータ型に変化しているかをチェックする必要があります。(ループなら全ループ、配列なら全要素をチェックする必要があります。)
他のデータベースソフトでは、「何も知らないエンドユーザーでも、セルに無茶苦茶に値を入力できてしまう」といった感じのことは少ないので、Variant型のようなデータ型を使っても、Excelよりはトラブルは少ないと思います。
  
  
★ コメントを消したプログラム04
ADOやDAOなどでSQLにてレコードを追加すると、主に文字列型の列に接頭辞の「'」が含まれたセルができてしまいます。既存のレコードの文字列型の列を書き換えたときも同様に、「'」がついてしまいます。
このプログラムはそのように、接頭辞の「'」が含まれたセルがあったらそのセルの「'」を消す処理です。

ただ、SQLでファイルを閉じたままデータを扱う上でも、普通にファイルを開いてユーザーが手操作でデータを扱う上でも、『 文字列型の列に、接頭辞の「'」が含まれたセルがあっても』、『なんの問題もありません。』

なのでこの処理はしなくてもいいといえばいいです。

VBA操作上も、Valueプロパティで取得したセルの値にも、この接頭辞の「'」は含まれませんので。

ただ、『どうしても気になる』とか、『 数値型や日付型など、文字列型の列「以外」のセルに接頭辞の「'」が含まれてしまっている・・・』、という場合には使えると思います。
でも、その場合も、ADOやDAOその他でSQLなどを使ってデータを書き換えるときは、データ型が通常のExcel操作よりも厳格にチェックされるので、数値型の列に文字を書き込もうとしてもエラーになってそれはできません。なので、書き換える表のほうを、そのファイルを開いたときにメチャクチャな操作をしていなければ、数値型や日付型の列に文字列データや接頭辞の「'」が入ってしまうことは基本的にはありません。
(行の削除だけは行番号丸ごとで行を消さないといけませんが。)
その意味でも、『文字列型の列の接頭辞の「'」』については、それほど怖がらずに、放置しておいても基本、問題はないと思います。

なお、日付型の列にこのプログラム(書式の貼り付け)を使うと、日付がシリアル値(ただの数値)に変わってしまうので、セルを日付表示に直す必要があります。

また、「If IsNumeric(c.Value) = True Then」の行からの条件分岐は・・・、
文字列型のセルにSQLで数字のみを入れると「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークができてしまうので、それを消す処理です。
関連記事はもちら→→ 『Excel2010:「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークを消して、セルの値を明示的に数値化するプログラム
  
  
▼その他の注意事項

(01)あと、このままだと、「閉じたファイル」の接頭辞の「'」は消せません。

(02)それと、このプログラムはテストプログラムなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。

(03)ただ、ちょっと作り変えると、最後の行だけでなく、すべての使用したセルを操作対象にすることもできます。

(04)また、繰り返しになりますが、このプログラムはSQLで「追加した行」にしか対応していません。しかし、実際にはSQLで「既存のレコードの文字列型の列を書き換えたとき」にも、「'」がついてしまいます。
なので、文字列型の列の列番号を求めてから、その列のUsedRangeのアドレスを求めて、毎回、列丸ごとを処理してしまうようなやりかたのほうがいいかもしれません。(「UsedRange」=値の入っているセル全部です。値の入っていないセルは対象外です。)
あるいは、『 数値型や日付型など、文字列型の列「以外」のセルに接頭辞の「'」が含まれてしまっている・・・』、という事態に対応するために、『 UsedRangeのセルすべてに書式の貼り付けをして、日付型など見え方が変わってしまった列だけ、書式を再設定しなおす』ということが必要だと思います。

  
  
★ もとになったコメントの付いたプログラムコード
※書き換えたい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。
※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。)