(←その他の「できないこと」も書いてあります。「あえて削除しない」ことも結構あります。不正対策や整合性維持のためです。基本、削除フラグを立てるだけでも対応できます。読み込みだけに使っても構いません。ダウンロードしたらデスクトップなど、好きな場所に置いてテストしてみてください。ファイルを開いたら「編集を有効にする」ボタンを押してください。「Sheet3」が開きますので、Sheet3を開いたままじゃないとテストできません。実行してみてください。一括書き換えができるのは、「コメントや他のプロシージャあり」モジュールの、「UpdateByADO_ACE01()」というプロシージャです。10"」のところの「10」を100に変更したり、「白みそ」を別のみその名前に変えたりして実行してみてください。ループを使わなくても、原則としては上記のような短い命令文にて、指定した行が一括書き換えされるのが見られると思います。※なお、「コメントや他のプロシージャあり」モジュールの、「UpdateByADO_ACE01()」プロシージャを実行すると、「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークを出すことができます。UsedLastRowPfixChrDel01」というコードをコピペして再実行すると、グリーンの三角エラーマークが消えることを確認できます。「Sheet3」の列の入れ替えやレコード削除などの扱いについてレコード削除だけ、行番号を選択してから削除してください。セルを選択して行削除すると、次回にレコード追加した時にそこが空白になってしまいます。列の入れ替えは、列番号単位でもセル単位でも入れ替えできます。列を入れ替えても、ちゃんと、その列に指定したデータが入力されます。行の入れ替えは、行番号単位だけでの入れ替えがいいのだと思います(テストしてませんすみません)。空白行や空白列は作らないでください。表自体も、全体として移動するならOKです。表の外の空白セルには、表の名前とか注意書きとかはなにも書かないでください。書きたかったら、横書きテキストボックスに書いてください。レコード追加や書き換えができなくなります。ADOでの「Sheet3」(つまり=システムテーブル)の操作でできること列の入れ替えをしても列名でデータを操作できます。(追加、削除、書き換えなど)行の入れ替えをしても問題なし。列名でデータを操作できます。表(=システムテーブル)自体も、全体として移動するならOKです。(ただしただ、表の外の空白セルには、表の名前とか注意書きとかはなにも書かないでください。)データを操作したいファイルが閉じていても書き換えできます。LAN上のファイルも扱えます。閉じていても扱えます。ADOのオブジェクト(レコードセット)などでループの書き換えが可能です。列の入れ替えをしても列名で書き換え可能です。SQLにての書き換え、追加、削除も可能です。列の入れ替えをしても列名で書き換え可能です。xls、xksx、xlsm、が扱えると思います。書き込みはxksx、xlsm、がいいかも?xlsの書き込みはDAOのほうがいいかもしれません。DAOも上記と似たようなことができると思います。既存データの書き換えや新規レコード追加などでセル書式が変わってしまうことがあります。それをプログラムで自動的に修正することが難しそうです。(02)数式の入力(できないかも?できるかも?)数式自体は文字列データのため、数値型の列に数式を入れようとした時点で、エラーになります。基本、数式入力は「ADO+SQLでは」できなさそうです。何かほかの方法があるかもしれませんが・・・。文字列型の列には、数式を入れることができます。反映(再計算?)させる方法が見つかりませんでした。※以下、以前に書いたものです。文字列として入力してから接頭辞の「'」を取ればOKかもしれません。「数式としても文字列を入力すること」だけはできます。現時点のテストでは再計算ができませんでした。B7'"」といった感じで書き足してみるとテストできます。ActiveCell.Value」で、入力した数式が反映されるかも?。思い、テストしてみましたがダメでした。Fomulaとかかも?わかりませんが、ただ、ここで何か書き足してエラーが出るようなら、この行は消してください。当方のテストではうまくいかず、まだちゃんとした方法がわかりませんでした。文字列の列に入れる数式は、数式が返す値が文字列に・・と、同じデータ仮名になるようにしないと、いけません。ある列に、異なるデータ型となってしまう数式を入れると、そのあとから、データの追加や修正がエラーでできなくなります。(03)表の外のセルに表の名前や注意書きを入力すること表として認識できなくなるので(列名自体が認識できなくなるので)、それはできません。(04)1シート内に複数の表を作成することADOやSQLで表を扱う場合は、1シートにつき1つの表でなければなりません。※このような不都合を解決したい場合は、クラスモジュールにて、そういった操作もADO操作も両方ができるオブジェクトを自作すればできるかもしれません。(もちろん僕にはそんなことはできません。何百行でも何千行でも一括データ書き換えができてしまうので便利です。※書き換えたい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。'テストのプロシージャを改造して、自作関数化しました。'呼び出すと、自ファイルに対して、指定した「SQL」文にて、書換えを実行します。'(どの表に対して書き換えを行うかは、呼び出し元の側にて、「SQL」文の中に書きます。'123456789101112131415161718192021222324252627282930'''##################################################################################'テストのプロシージャを改造して、自作関数化しました。'呼び出すと、自ファイルに対して、指定した「SQL」文にて、書換えを実行します。'(どの表に対して書き換えを行うかは、呼び出し元の側にて、「SQL」文の中に書きます。Sub''Excelの場合、接続の相手先のファイルがxlsx・xlsmの場合は、ADOのACE系のデータベースへの接続を使います。(xlsはDAOを使うほうがいいみたいです)それは相手が(今回のテストのように)「自ファイル」でもOKです。12.0")」とだけ書くと、接続先のファイルの内容を書き換えることができます。(1行目のすべての列名もちゃんと認識されます。OKですし、また、相手ファイルが「開いたまま」でも「閉じたまま」でもOKです。※別のユーザーが開いている場合はテストしていません。今後機会があったらテストしてみたいと思います。ThisWorkbook.Name」の部分や引数の部分などを作り変えます。初心者の方は、ご自分でもチャレンジしてみてください。(わからない方は、分かる人に聞いてみてください。Server、MySQLなどにも接続することができます。どちらかというとそれらに接続して使うことが多いです。Excelではあまり使われないみたいです。「初心者本」では出てきません。無駄なクラスモジュール(自作オブジェクト)の作成をしてしまう恐れがあるので、あえて「初心者も」、「ExcelVBAの基本」として学んでおく必要があります。初心者の方でも、「さらっとは」絶対に知っておく必要があります。特別な理由がない限り、必要ないと思います。SQLも使えてしまうADOやDAO以上の機能の各オブジェクトを自ら自作するなんて、とてつもなく大変だと思いますので・・・。Excel以外のデータベースソフトで使われることが多いです。Googleやトヨタ系の企業など大企業の社内システムや、Web上のほぼすべてのカートシステム、弥生や奉行などのソフト、などに使われています。本来書き換え可能なのに、それが不可能な状態になってしまったりします。表のデータを行番号丸ごとベースではなく、セルベースで消した時もおかしな動きになります。(他のデータベースではありえないExcel特有の現象。いうような恰好になるんだと思いいます。コメントを消したプログラムコード02(前項の自作関数を呼び出して自ファイルのデータをループを使わずに一括書き換えするテスト)※書き換えたい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。'自ファイルの内容を書き換えるテスト。'その行の「相対重量」の列の数値を「30」に書き換えます。'1234567891011121314151617181920212223'''############################################################################'「UpdateByADO_ACE04」という自作関数を呼び出して'自ファイルの内容を書き換えるテスト。'以下の例は、「Sheet3」の表の、「具材名」の列が「白みそ」の行を全部一括で、'その行の「相対重量」の列の数値を「30」に書き換えます。Sub''この場合、以下のような書き換えの設定内容となります。抽出条件値」の行で、何百行あろうが、何千行あろうが、条件にあてはまる行を抜き出すので、それで、ループを使わずに一括書き換えが可能となります。コメントを消したプログラムコード03(最初の自作関数を呼び出して、自ファイルのデータに新規でレコード追加するテスト)※追加したい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。'自ファイルの内容に新規レコードを追加するテスト。'「見える」ようにしてみました。'ムダが多いかもしれませんが、こういう書き方もできるということで。'もっといい案をご自分でも考えてみてください。'123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657'''####################################################################'「UpdateByADO_ACE04」という自作関数を呼び出して'自ファイルの内容に新規レコードを追加するテスト。すると「見づらい」ため、あえて長ったらしくして、'一応、「どの列の値をどう書き換えたか」などが分かりやすく'「見える」ようにしてみました。'ムダが多いかもしれませんが、こういう書き方もできるということで。'もっといい案をご自分でも考えてみてください。すると「見づらい」ため、あえて長ったらしくして、'一応、「どの列の値をどう書き換えたか」などが分かりやすく'「見える」ようにしてみました。'ムダが多いかもしれませんが、こういう書き方もできるということで。'もっといい案をご自分でも考えてみてください。※Variant型の変数を使う場合は、値が入った直後にデータの型が自動的に設定されます。その際に、ユーザーの「想定外の値」に設定されてしまうことがあるので、それがトラブルの原因になりやすいです。Excelでは「何も知らないエンドユーザーがセルに無茶苦茶に値を入力できてしまう」のでトラブルになりやすいです。(エラーが出ないこともあるので質が悪い。)トラブルが起こったら、かならず「ローカルウィンドウ」+ステップ実行、あるいはTypeNameプロパティなどを使用して、どんなデータ型に変化しているかをチェックする必要があります。(ループなら全ループ、配列なら全要素をチェックする必要があります。)他のデータベースソフトでは、「何も知らないエンドユーザーでも、セルに無茶苦茶に値を入力できてしまう」といった感じのことは少ないので、Variant型のようなデータ型を使っても、Excelよりはトラブルは少ないと思います。コメントを消したプログラム04ADOやDAOなどでSQLにてレコードを追加すると、主に文字列型の列に接頭辞の「'」が含まれたセルができてしまいます。既存のレコードの文字列型の列を書き換えたときも同様に、「'」がついてしまいます。このプログラムはそのように、接頭辞の「'」が含まれたセルがあったらそのセルの「'」を消す処理です。文字列型の列に、接頭辞の「'」が含まれたセルがあっても』、『なんの問題もありません。』なのでこの処理はしなくてもいいといえばいいです。VBA操作上も、Valueプロパティで取得したセルの値にも、この接頭辞の「'」は含まれませんので。数値型や日付型など、文字列型の列「以外」のセルに接頭辞の「'」が含まれてしまっている・・・』、という場合には使えると思います。その場合も、ADOやDAOその他でSQLなどを使ってデータを書き換えるときは、データ型が通常のExcel操作よりも厳格にチェックされるので、数値型の列に文字を書き込もうとしてもエラーになってそれはできません。書き換える表のほうを、そのファイルを開いたときにメチャクチャな操作をしていなければ、数値型や日付型の列に文字列データや接頭辞の「'」が入ってしまうことは基本的にはありません。(行の削除だけは行番号丸ごとで行を消さないといけませんが。)その意味でも、『文字列型の列の接頭辞の「'」』については、それほど怖がらずに、放置しておいても基本、問題はないと思います。日付型の列にこのプログラム(書式の貼り付け)を使うと、日付がシリアル値(ただの数値)に変わってしまうので、セルを日付表示に直す必要があります。文字列型のセルにSQLで数字のみを入れると「このセルにある数値が、テキスト形式か、またはアポストロフィで始まっています」というグリーンの隅っこ三角エラーマークができてしまうので、それを消す処理です。▼その他の注意事項(01)あと、このままだと、「閉じたファイル」の接頭辞の「'」は消せません。(02)それと、このプログラムはテストプログラムなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。(03)ただ、ちょっと作り変えると、最後の行だけでなく、すべての使用したセルを操作対象にすることもできます。(04)また、繰り返しになりますが、このプログラムはSQLで「追加した行」にしか対応していません。実際にはSQLで「既存のレコードの文字列型の列を書き換えたとき」にも、「'」がついてしまいます。文字列型の列の列番号を求めてから、その列のUsedRangeのアドレスを求めて、毎回、列丸ごとを処理してしまうようなやりかたのほうがいいかもしれません。(「UsedRange」=値の入っているセル全部です。値の入っていないセルは対象外です。UsedRangeのセルすべてに書式の貼り付けをして、日付型など見え方が変わってしまった列だけ、書式を再設定しなおす』ということが必要だと思います。'そのセルの「'」を消す処理。'ただし、このままだと、「閉じたファイル」の「'」は消せない。'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。'「加算」の貼り付けで消す。'書式の貼り付けで消してもいいみたい。'12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879'''########################################################################################'追加した最後の行に、接頭辞の「'」が含まれたセルがあったら'そのセルの「'」を消す処理。'ただし、このままだと、「閉じたファイル」の「'」は消せない。'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。'「加算」の貼り付けで消す。Transpose:=False'書式の貼り付けで消してもいいみたい。もとになったコメントの付いたプログラムコード※書き換えたい先が、「1シートに付き1つの表」・・・というかたちになっていることが前提です。※VBE(VisualBasicEditorのツールメニューより、ADOへの「参照設定」が必要です。以下、メインプログラムです。'読み込み先のExcelファイルに接続。できるようにするための準備。'接続先を自ファイルに設定ます。'読み込みたい先のファイルのフルパスを指定(ここでは自ファイルとしています。'データの書き換えができます。'その「相対重量」の列の値を一括で「10」に書き換えます。'書き換えも可能かどうかの一応のチェック。'ループで書き換えることが可能です。」を使うと書き換え(更新)が可能なレコードセットになるようです。使っても「行セットは逆方向フェッチをサポートしていません」のエラーも出ません。'上記のプロシージャを改造して、自作関数化しまいた。'呼び出すと、指定した「SQL」文での書き換えを実行します。以下、メインプログラムです。'読み込み先のExcelファイルに接続。できるようにするための準備。'接続先を自ファイルに設定ます。'「値やオブジェクトを返さない自作関数」に作り変えたので、以下の4行すべてコメントアウト。'自ファイルの内容を書き換えたり、レコード追加したりするテスト。'ムダが多いかも・・・。'ご自分でも色々かんがえてみてください。'SQL命令文どおりの書き換えを実行。'SQL命令文どおりの書き換えを実行。'自ファイルの内容に新規レコードを追加するテスト。'「見える」ようにしてみました。'ムダが多いかもしれませんが、こういう書き方もできるということで。'もっといい案をご自分でも考えてみてください。'変な日付になってしまうようなので、こうしています。'以下は、修正前のコード。一応、対比としてのせておきました。'SQL命令文どおりの書き換えを実行。'できてしまうようなので、そのセルの「'」を消す。'特に文字列型の列のデータ。「'」は付かないっぽいです。'ExecuteメソッドでSQLを実行する方法です。'使用する必要があります。'SQLを実行してみます。'ACEタイプの接続での書き換えです。'▲なお、事前に、MicrosoftQueryを作ってなくても書き換え可能です。以下、メインプログラムです。'Excelの画面描画をいったん停止。今の画面のまま変化させないようにする。'読み込み先のExcelファイルに接続。できるようにするための準備。'読み込み先のファイルに読み書きモードで(?)接続。'よくわかってません。'ここではACEエンジンタイプでの接続方法を使います。相手を閉じたまま書き換えたいので。'新しい(空の)SQLコマンド実行環境機能の作成。'その実行環境機能を読み込み先ファイルとの「接続」に紐付け(=関連付け)。'そのセルの「'」を消す処理。'ただし、このままだと、「閉じたファイル」の「'」は消せない。'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。'どこのセルでもいいのでコピーしておきます。'「最後の行」のセル範囲のアドレスを取得する作業。'「最後の行」のうち、接頭辞の「'」を含むセルから、それを消す作業。'もし、接頭辞に「'」がついていたら消す。'「加算」の貼り付けで消す。'書式の貼り付けで消してもいいみたい。'今のセルのもともとのセルの値に1をかけて、今のセルに代入(要するに上書き)する。'値がTrue/Falseの2値だった場合は何もしない。'値が日付型だった場合は何もしない。'値が数値型だった場合は何もしない。'値がその他の場合も何もしない。以下、メインプログラムです。'========================'読み込み先のExcelファイルに接続。できるようにするための準備。)'========================'接続先を自ファイルに設定ます。'読み込みたい先のファイルのフルパスを指定(ここでは自ファイルとしています。12.0"""'========================'読み込み先のファイルに対して、SQLコマンドを実行'========================'テーブル名をできるだけ省略した形でSQL文を作成'基本、SQL命令だと、ループを使わなくても一括で'データの書き換えができます。'たとえば、以下の命令文の場合だと、'「具材名」という列の値が「白みそ」の行は(もし複数行あっても)全部、'その「相対重量」の列の値を一括で「10」に書き換えます。CmdSqlStr01'========================'ついでに、一応、列名を認識しているかと、'「SQL」ではなくてADOの「レコードセット」での'書き換えも可能かどうかの一応のチェック。'「レコードセット」の場合は、指定した行に飛んで書き変えたり'ループで書き換えることが可能です。」を使うと書き換え(更新)が可能なレコードセットになるようです。使っても「行セットは逆方向フェッチをサポートしていません」のエラーも出ません。Sub'####################################################################'上記のプロシージャを改造して、自作関数化しまいた。'呼び出すと、指定した「SQL」文での書き換えを実行します。以下、メインプログラムです。'========================'読み込み先のExcelファイルに接続。できるようにするための準備。)'========================'接続先を自ファイルに設定ます。'「値やオブジェクトを返さない自作関数」に作り変えたので、以下の4行すべてコメントアウト。Sub'####################################################################'「UpdateByADO_ACE04」という自作関数を呼び出して'自ファイルの内容を書き換えたり、レコード追加したりするテスト。'ただ、SQL実行のたびにADOでのオブジェクト作成をしているので'ムダが多いかも・・・。書けるかもしれないので'ご自分でも色々かんがえてみてください。'白みそ';"'「UpdateByADO_ACE04」を呼び出すことで、'SQL命令文どおりの書き換えを実行。;"'「UpdateByADO_ACE04」を呼び出すことで、'SQL命令文どおりの書き換えを実行。Sub'####################################################################'「UpdateByADO_ACE04」という自作関数を呼び出して'自ファイルの内容に新規レコードを追加するテスト。'どの列の値をどう書き換えたかなどが分かりやすく'「見える」ようにしてみました。'ムダが多いかもしれませんが、こういう書き方もできるということで。'もっといい案をご自分でも考えてみてください。"日付"'SQLで日付をセルに書き込む場合は、シリアル値をセルに書き込まないと'変な日付になってしまうようなので、こうしています。'以下は、修正前のコード。一応、対比としてのせておきました。VALUES('合わせ',2,0,0.5,0.7,55);"'「UpdateByADO_ACE04」を呼び出すことで、'SQL命令文どおりの書き換えを実行。UpdateByADO_ACE04(strSql01)'SQLで新規レコードを追加すると、'追加した最後の行に、接頭辞の「'」が含まれたセルが'できてしまうようなので、そのセルの「'」を消す。'特に文字列型の列のデータ。「'」は付かないっぽいです。UpdateByADO_ACE02()'不要なコメントやコードをすべて削除した状態'新規コマンド環境を作らずに、Connectionオブジェクトを直接使って'ExecuteメソッドでSQLを実行する方法です。オブジェクトを'使用する必要があります。メソッドを直接使って'SQLを実行してみます。'ACEタイプの接続での書き換えです。ACEタイプの接続でないと、閉じたまま書き換えられないので・・・)'▲なお、事前に、MicrosoftQueryを作ってなくても書き換え可能です。以下、メインプログラムです。'Excelの画面描画をいったん停止。今の画面のまま変化させないようにする。'========================'読み込み先のExcelファイルに接続。できるようにするための準備。'ADOにてのデータ接続オブジェクトの作成'読み込み先のファイルに読み書きモードで(?)接続。'よくわかってません。'ここではACEエンジンタイプでの接続方法を使います。相手を閉じたまま書き換えたいので。'新しい(空の)SQLコマンド実行環境機能の作成。'その実行環境機能を読み込み先ファイルとの「接続」に紐付け(=関連付け)。Sub'########################################################################################'追加した最後の行に、接頭辞の「'」が含まれたセルがあったら'そのセルの「'」を消す処理。'ただし、このままだと、「閉じたファイル」の「'」は消せない。'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。False'接頭辞の「'」を消すために、'書式の貼り付けに使うので'どこのセルでもいいのでコピーしておきます。Range("A1").SelectRange("A1").Copy'================================================================'「最後の行」のセル範囲のアドレスを取得する作業。UsdRng01.Rows(LastRow01).Address'================================================================'「最後の行」のうち、接頭辞の「'」を含むセルから、それを消す作業。Then'もし、接頭辞に「'」がついていたら消す。'「加算」の貼り付けで消す。Transpose:=False'書式の貼り付けで消してもいいみたい。'今のセルのもともとのセルの値に1をかけて、今のセルに代入(要するに上書き)する。Then'値がTrue/Falseの2値だった場合は何もしない。Then'値が日付型だった場合は何もしない。'値が数値型だった場合は何もしない。Else'値がその他の場合も何もしない。挫折するオブジェクト式やプログラム(プロシージャ)の書き方、挫折しない書き方、の違い。ODBCでQueryTableオブジェクトを自動作成するサンプルコード)。2000以降の全バージョンで使えると思います。~ついでに、セルの値で結果表示を自動で切り替える「パラメータクエリ」のVBAでの自動作成も。~★★Access2000VBA・Excel2000VBA独学~Win32API。eChromeを例に。(Excel、Word、Access、などが32bit版のモノでしか使えませんが、OSは64bitでも大丈夫です。』、「オブジェクトを引数にして・かつ・オブジェクトを返す」、そういう自作関数の作り方。速く動くように作り変える」方法。「安全」な、Youtube動画のダウンロード方法6つ。挫折するオブジェクト式やプログラム(プロシージャ)の書き方、挫折しない書き方、の違い。送信メールが受信トレイに入ってしまうトラブルの回避、など。ODBCでQueryTableオブジェクトを自動作成するサンプルコード)。2000以降の全バージョンで使えると思います。~ついでに、セルの値で結果表示を自動で切り替える「パラメータクエリ」のVBAでの自動作成も。technology.。