Access2000VBA・Excel2000VBA独学～ADOにて、「SQL」や「レコードセット」で自ファイル（xlsm）の表のデータをループを使わずに一括書き換えするテスト（自ファイル以外の他の閉じたファイルの閉じたままの書き換えにも使えます～ | 無駄なお金と時間がない方・零細企業様・独立したい方・個人事業主の方・営業さん・経理さん・学生さん・就活生さん・小学生のお子様、中高生の子たちのための、　　　　「小さな会社の兼任なんちゃって社内SE・パソコン管理術・の基礎知識」

Access2000VBA・Excel2000VBA独学～ADOにて、「SQL」や「レコードセット」で自ファイル（xlsm）の表のデータをループを使わずに一括書き換えするテスト（自ファイル以外の他の閉じたファイルの閉じたままの書き換えにも使えます～
　　
※まだ書きかけです。すみません。
※間違ってたらすみません。
※メモ書きなので、自分でも意味不明な箇所も多いです。ごめんなさい。
　　
　　
※参考記事
DAO、ADO、Microsoft Query（ExcelVBAのQueryTableオブジェクト）、では「読み込み」だけでなく、「閉じたまま」の「複雑条件集計」や「書き込み」もできます。
ただし、Excelの場合はなんと「削除」ができないので対応策が必要です。
対応策はこちら。『できないこと』。（←その他の「できないこと」も書いてあります。なお、「あえて削除しない」ことも結構あります。不正対策や整合性維持のためです。基本、削除フラグを立てるだけでも対応できます。もちろん、読み込みだけに使っても構いません。）
Excel2010で、『開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）
『～SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」～Microsoft Query（QueryTableオブジェクト）を利用する場合～』
『ADO：Excel2010で、開かれていない閉じたままのブック・Excel（xls）データをできるだけ速く読み込む方法ーその２（ADOにて）』
『Access2000VBA・Excel2000VBA独学～別の閉じたExcelファイルを ”閉じたまま” 読み込みや書き込をする方法～５つ』
　　
　　

★ はじめに

　　
　　
★ サンプルダウンロード
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への「参照設定」が必要です。）

'
'
'##################################################################################
'テストのプロシージャを改造して、自作関数化しました。
'呼び出すと、自ファイルに対して、指定した「SQL」文にて、書換えを実行します。
'（どの表に対して書き換えを行うかは、呼び出し元の側にて、「SQL」文の中に書きます。）
'##################################################################################
  
Sub UpdateByADO_ACE04(CmdSqlStr01 As String)
    
    Dim Cn              As ADODB.Connection     
    Dim TrgtXLFName     As String               

    TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name  
    Set Cn = New ADODB.Connection                               

    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & TrgtXLFName & ";" & _
                        "Extended Properties=""Excel 12.0"""
                                              
    Cn.Execute CmdSqlStr01

    Cn.Close
    Set Cn = Nothing
    
End Sub


'
'

'##################################################################################

'テストのプロシージャを改造して、自作関数化しました。

'呼び出すと、自ファイルに対して、指定した「SQL」文にて、書換えを実行します。

'（どの表に対して書き換えを行うかは、呼び出し元の側にて、「SQL」文の中に書きます。）

'##################################################################################

Sub UpdateByADO_ACE04(CmdSqlStr01 As String)

Dim Cn As ADODB.Connection

Dim TrgtXLFName As String

TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name

Set Cn = New ADODB.Connection

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & TrgtXLFName & ";" & _

"Extended Properties=""Excel 12.0"""

Cn.Execute CmdSqlStr01

Cn.Close

Set Cn = Nothing

End Sub

　　
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への「参照設定」が必要です。）

'
'
'############################################################################
'「UpdateByADO_ACE04」という自作関数を呼び出して
'自ファイルの内容を書き換えるテスト。
'以下の例は、「Sheet3」の表の、「具材名」の列が「白みそ」の行を全部一括で、
'その行の「相対重量」の列の数値を「30」に書き換えます。ループを使わずに。
'############################################################################

Sub OperationTest01()

    Dim strSql01    As String          

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

    Call UpdateByADO_ACE04(strSql01)

End Sub
'
'

'############################################################################

'「UpdateByADO_ACE04」という自作関数を呼び出して

'自ファイルの内容を書き換えるテスト。

'以下の例は、「Sheet3」の表の、「具材名」の列が「白みそ」の行を全部一括で、

'その行の「相対重量」の列の数値を「30」に書き換えます。ループを使わずに。

'############################################################################

Sub OperationTest01()

Dim strSql01 As String

strSql01 = ""

strSql01 = strSql01 & "UPDATE `Sheet3$`"

strSql01 = strSql01 & " SET 相対重量 = 30"

strSql01 = strSql01 & " WHERE 具材名 = '白みそ';"

Call UpdateByADO_ACE04(strSql01)

End Sub

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

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

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

'
'
'####################################################################
'「UpdateByADO_ACE04」という自作関数を呼び出して
'自ファイルの内容に新規レコードを追加するテスト。
'ほんとは1つ前の例のように、もっと短く書けるのですが、
'でも そうすると「見づらい」ため、あえて長ったらしくして、
'一応、「どの列の値をどう書き換えたか」などが分かりやすく
'「見える」ようにしてみました。
'ムダが多いかもしれませんが、こういう書き方もできるということで。。。
'もっといい案をご自分でも考えてみてください。
'####################################################################

Sub OperationTest02()

    Dim strSql01        As String       
    Dim ClmnName(7)     As String       
    Dim Data01(7)       As Variant      
    Dim DateNum01       As Long

    ClmnName(0) = "具材名"
      Data01(0) = "'合わせみそ'"
    
    ClmnName(1) = "相対重量"
      Data01(1) = 0
    
    ClmnName(2) = "玉ねぎ重量"
      Data01(2) = 0
    
    ClmnName(3) = "基本重量との差分"
      Data01(3) = 0
    
    ClmnName(4) = "基本重量"
      Data01(4) = 0
    
    ClmnName(5) = "対玉ねぎ比"
      Data01(5) = 0

    ClmnName(6) = "フラグ01"
      Data01(6) = True

    ClmnName(7) = "日付"
      DateNum01 = Date       
      Data01(7) = DateNum01  

    strSql01 = ""
    strSql01 = strSql01 & "INSERT INTO `Sheet3$`"
    strSql01 = strSql01 & " (" & Join(ClmnName, ",") & ") "
    strSql01 = strSql01 & " VALUES(" & Join(Data01, ",") & ");"
    
    Call UpdateByADO_ACE04(strSql01)

'    'Call UsedLastRowPfixChrDel01  'ここではコメントアウト

End Sub
'
'

'####################################################################

'「UpdateByADO_ACE04」という自作関数を呼び出して

'自ファイルの内容に新規レコードを追加するテスト。

'ほんとは1つ前の例のように、もっと短く書けるのですが、

'でもそうすると「見づらい」ため、あえて長ったらしくして、

'一応、「どの列の値をどう書き換えたか」などが分かりやすく

'「見える」ようにしてみました。

'ムダが多いかもしれませんが、こういう書き方もできるということで。。。

'もっといい案をご自分でも考えてみてください。

'####################################################################

Sub OperationTest02()

Dim strSql01 As String

Dim ClmnName(7) As String

Dim Data01(7) As Variant

Dim DateNum01 As Long

ClmnName(0) = "具材名"

Data01(0) = "'合わせみそ'"

ClmnName(1) = "相対重量"

Data01(1) = 0

ClmnName(2) = "玉ねぎ重量"

Data01(2) = 0

ClmnName(3) = "基本重量との差分"

Data01(3) = 0

ClmnName(4) = "基本重量"

Data01(4) = 0

ClmnName(5) = "対玉ねぎ比"

Data01(5) = 0

ClmnName(6) = "フラグ01"

Data01(6) = True

ClmnName(7) = "日付"

DateNum01 = Date

Data01(7) = DateNum01

strSql01 = ""

strSql01 = strSql01 & "INSERT INTO `Sheet3$`"

strSql01 = strSql01 & " (" & Join(ClmnName, ",") & ") "

strSql01 = strSql01 & " VALUES(" & Join(Data01, ",") & ");"

Call UpdateByADO_ACE04(strSql01)

' 'Call UsedLastRowPfixChrDel01 'ここではコメントアウト

End Sub

'ほんとは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のセルすべてに書式の貼り付けをして、日付型など見え方が変わってしまった列だけ、書式を再設定しなおす』ということが必要だと思います。

'
'
'########################################################################################
'追加した最後の行に、接頭辞の「'」が含まれたセルがあったら
'そのセルの「'」を消す処理。
'ただし、このままだと、「閉じたファイル」の「'」は消せない。
'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。
'########################################################################################

Sub UsedLastRowPfixChrDel01()

  
    Dim c                       As Range
    Dim LastRow01               As Long
    Dim UsdLastRowAdrs01        As String
    Dim UsdRng01                As Range
    

    Application.ScreenUpdating = False
    
    Range("A1").Select
    Range("A1").Copy

    Set UsdRng01 = ActiveSheet.UsedRange
    LastRow01 = UsdRng01.Rows.Count
    UsdLastRowAdrs01 = UsdRng01.Rows(LastRow01).Address
    

    For Each c In Range(UsdLastRowAdrs01)
  
        If TypeName(c.Value) = "String" Then
        
           
            If c.PrefixCharacter = "'" Then
            
'                '「加算」の貼り付けで消す。
'                c.PasteSpecial _
'                    Paste:=xlPasteAll, _
'                    Operation:=xlAdd, _
'                    SkipBlanks:=False, _
'                    Transpose:=False
    
                '書式の貼り付けで消してもいいみたい。
                c.PasteSpecial _
                    Paste:=xlPasteFormats, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
            Else
            
            End If

            If IsNumeric(c.Value) = True Then
            
                c.Value = c.Value * 1           
                c.NumberFormatLocal = "G/標準"    
                 
            Else
            
            End If

        ElseIf TypeName(c.Value) = "Boolean" Then

        ElseIf TypeName(c.Value) = "Date" Then

        ElseIf TypeName(c.Value) = "Double" Then　
                 
        Else
   
        End If
        
    Next c
    
    Application.CutCopyMode = False
    
  
End Sub
'
'

'########################################################################################

'追加した最後の行に、接頭辞の「'」が含まれたセルがあったら

'そのセルの「'」を消す処理。

'ただし、このままだと、「閉じたファイル」の「'」は消せない。

'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。

'########################################################################################

Sub UsedLastRowPfixChrDel01()

Dim c As Range

Dim LastRow01 As Long

Dim UsdLastRowAdrs01 As String

Dim UsdRng01 As Range

Application.ScreenUpdating = False

Range("A1").Select

Range("A1").Copy

Set UsdRng01 = ActiveSheet.UsedRange

LastRow01 = UsdRng01.Rows.Count

UsdLastRowAdrs01 = UsdRng01.Rows(LastRow01).Address

For Each c In Range(UsdLastRowAdrs01)

If TypeName(c.Value) = "String" Then

If c.PrefixCharacter = "'" Then

' '「加算」の貼り付けで消す。

' c.PasteSpecial _

' Paste:=xlPasteAll, _

' Operation:=xlAdd, _

' SkipBlanks:=False, _

' Transpose:=False

'書式の貼り付けで消してもいいみたい。

c.PasteSpecial _

Paste:=xlPasteFormats, _

Operation:=xlNone, _

SkipBlanks:=False, _

Transpose:=False

Else

End If

If IsNumeric(c.Value) = True Then

c.Value = c.Value * 1

c.NumberFormatLocal = "G/標準"

Else

End If

ElseIf TypeName(c.Value) = "Boolean" Then

ElseIf TypeName(c.Value) = "Date" Then

ElseIf TypeName(c.Value) = "Double" Then　

Else

End If

Next c

Application.CutCopyMode = False

End Sub

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

'
'
Option Explicit

'####################################################################
'自ファイル（xlsm）の表の内容を、ADOにて、「SQL」または
'レコードセットで書き換えテストを行うサンプル
'####################################################################
    

Sub UpdateByADO_ACE01()
 
   '★ 変数設定
   
    Dim Cn              As ADODB.Connection     '書込み対象のXLSファイルの「ADO接続文字用のオブジェクト」用の変数
    Dim rs              As ADODB.Recordset      '列名を認識できているかのチェックや書き換えのための「レコードセットオブジェクト」用の変数
    Dim TrgtXLFName     As String               '書込み対象のXLSファイルのフルパス格納用の変数
    Dim CmdSqlStr01     As String               'SQL文を格納するための変数
 
 
 
   '★ 以下、メインプログラムです。
        
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    '読み込み先のExcelファイルに接続。
    
    '（ターゲットファイルの読み書きが
    '  できるようにするための準備。）
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    '接続先を自ファイルに設定ます。
    TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name   '読み込みたい先のファイルのフルパスを指定（ここでは自ファイルとしています。）
    Set Cn = New ADODB.Connection                               'ADOにてのデータ接続用のある意味「空の」オブジェクトの作成
 
    '自ファイルに接続（「空の」オブジェクトに接続詳細設定を指定）
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & TrgtXLFName & ";" & _
                        "Extended Properties=""Excel 12.0"""

    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    '読み込み先のファイルに対して、SQLコマンドを実行
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

    'テーブル名をできるだけ省略した形でSQL文を作成
    '基本、SQL命令だと、ループを使わなくても一括で
    'データの書き換えができます。
    'たとえば、以下の命令文の場合だと、
    '「具材名」という列の値が「白みそ」の行は（もし複数行あっても）全部、
    'その「相対重量」の列の値を一括で「10」に書き換えます。
    
    CmdSqlStr01 = ""
    CmdSqlStr01 = CmdSqlStr01 & "UPDATE `Sheet3$`"
    CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 10"
    CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '白みそ';"

    'SQLの実行
    Cn.Execute CmdSqlStr01


                        
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'ついでに、一応、列名を認識しているかと、
    '「SQL」ではなくてADOの「レコードセット」での
    '書き換えも可能かどうかの一応のチェック。
    '「レコードセット」の場合は、指定した行に飛んで書き変えたり
    'ループで書き換えることが可能です。
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'    Set rs = Cn.Execute("`Sheet3$`")
'    Cn.CursorLocation = adUseClient

    Set rs = New ADODB.Recordset
    rs.Open "`Sheet3$`", Cn, adOpenStatic, adLockOptimistic  '「adOpenStatic, adLockOptimistic 」を使うと書き換え（更新）が可能なレコードセットになるようです。MoveLast を使っても「行セットは逆方向フェッチをサポートしていません」のエラーも出ません。
    rs.MoveLast              '一番最後のレコードへジャンプ
    Debug.Print rs(1).Name   '変更前の2列目の列名の表示
    Debug.Print rs(1).Value  '変更前の2列目の値の表示

    rs(1).Value = 1           '2列目の値を1に書き換え（この段階では書き換え指示のみ）
    rs.Update                'ここで書き換えを確定

    Debug.Print rs(1).Name   '変更後の2列目の列名の表示
    Debug.Print rs(1).Value  '変更後の2列目の値の表示
        
    
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'オブジェクト変数の後始末（オブジェクトをメモリ上から消す・破棄）
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    Cn.Close
    Set Cn = Nothing
    
End Sub

'####################################################################
'上記のプロシージャを改造して、自作関数化しまいた。
'呼び出すと、指定した「SQL」文での書き換えを実行します。
'####################################################################


Sub UpdateByADO_ACE04(CmdSqlStr01 As String)
 
   '★ 変数設定
   
    Dim Cn              As ADODB.Connection     '書込み対象のXLSファイルの「ADO接続文字用のオブジェクト」用の変数
    Dim TrgtXLFName     As String               '書込み対象のXLSファイルのフルパス格納用の変数
'    Dim CmdSqlStr01     As String               'SQL文を格納するための変数
 
 
 
   '★ 以下、メインプログラムです。
        
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    '読み込み先のExcelファイルに接続。
    
    '（ターゲットファイルの読み書きが
    '  できるようにするための準備。）
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    '接続先を自ファイルに設定ます。
    TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name   '読み込みたい先のファイルのフルパスを指定
    Set Cn = New ADODB.Connection                               'ADOにてのデータ接続用のある意味「空の」オブジェクトの作成
 
    '自ファイルに接続（「空の」オブジェクトに接続詳細設定を指定）
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & TrgtXLFName & ";" & _
                        "Extended Properties=""Excel 12.0"""
                                                
                                                
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    '読み込み先のファイルに対して、SQLコマンドを実行
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'    '「値やオブジェクトを返さない自作関数」に作り変えたので、以下の4行すべてコメントアウト。使いません。
'    'テーブル名をできるだけ省略した形でSQL文を作成
'    CmdSqlStr01 = "UPDATE `Sheet3$`"
'    CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 20"
'    CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '白みそ';"

    'SQLの実行
    Cn.Execute CmdSqlStr01
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'オブジェクト変数の後始末（オブジェクトをメモリ上から消す・破棄）
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    Cn.Close
    Set Cn = Nothing
    
End Sub

'####################################################################
'「UpdateByADO_ACE04」という自作関数を呼び出して
'自ファイルの内容を書き換えたり、レコード追加したりするテスト。
'ただ、SQL実行のたびにADOでのオブジェクト作成をしているので
'ムダが多いかも・・・。もっとすっきり書けるかもしれないので
'ご自分でも色々かんがえてみてください。
'####################################################################


Sub OperationTest01()

    Dim strSql01    As String       'データを書き換えるためのSQL命令文を格納するための変数を作成
    
    
    
    '既存レコードの書き換えの例
    
    strSql01 = ""
    strSql01 = strSql01 & "UPDATE `Sheet3$`"
    strSql01 = strSql01 & " SET 相対重量 = 30"
    strSql01 = strSql01 & " WHERE 具材名 = '白みそ';"

    '「UpdateByADO_ACE04」を呼び出すことで、
    'SQL命令文どおりの書き換えを実行。

    Call UpdateByADO_ACE04(strSql01)



    '新規レコードの追加の例
    strSql01 = ""
    strSql01 = strSql01 & "INSERT INTO `Sheet3$`"
    strSql01 = strSql01 & " (具材名,相対重量,玉ねぎ重量,基本重量との差分,基本重量,対玉ねぎ比)"
    strSql01 = strSql01 & " VALUES('合わせ',2,0,0.5,0.7,55);"
'    strSql01 = strSql01 & " (相対重量,玉ねぎ重量,基本重量との差分,基本重量,対玉ねぎ比) "
'    strSql01 = strSql01 & " VALUES(2,0,0.5,0.7,55) ;"

    '「UpdateByADO_ACE04」を呼び出すことで、
    'SQL命令文どおりの書き換えを実行。

    Call UpdateByADO_ACE04(strSql01)



End Sub

'####################################################################
'「UpdateByADO_ACE04」という自作関数を呼び出して
'自ファイルの内容に新規レコードを追加するテスト。
'どの列の値をどう書き換えたかなどが分かりやすく
'「見える」ようにしてみました。
'ムダが多いかもしれませんが、こういう書き方もできるということで。。。
'もっといい案をご自分でも考えてみてください。
'####################################################################

Sub OperationTest02()

    Dim strSql01        As String       'データを書き換えるためのSQL命令文を格納するための変数を作成
    Dim ClmnName(7)     As String       '列名の指定のための配列変数
    Dim Data01(7)       As Variant      '値の指定のための配列変数
    Dim DateNum01       As Long         '日付をいったんシリアル値に変換するための変数
    
    ClmnName(0) = "具材名"
      Data01(0) = "'合わせみそ'"
    
    ClmnName(1) = "相対重量"
      Data01(1) = 0
    
    ClmnName(2) = "玉ねぎ重量"
      Data01(2) = 0
    
    ClmnName(3) = "基本重量との差分"
      Data01(3) = 0
    
    ClmnName(4) = "基本重量"
      Data01(4) = 0
    
    ClmnName(5) = "対玉ねぎ比"
      Data01(5) = 0
    
    ClmnName(6) = "フラグ01"
      Data01(6) = True

    ClmnName(7) = "日付"
    'SQLで日付をセルに書き込む場合は、シリアル値をセルに書き込まないと
    '変な日付になってしまうようなので、こうしています。
      DateNum01 = Date       '日付をいったんシリアル値に変換
      Data01(7) = DateNum01  '変換したシリアル値を変数に代入

    
    '新規レコードの追加の例
    strSql01 = ""
    strSql01 = strSql01 & "INSERT INTO `Sheet3$`"
    strSql01 = strSql01 & " (" & Join(ClmnName, ",") & ") "
    strSql01 = strSql01 & " VALUES(" & Join(Data01, ",") & ");"
    
'    '以下は、修正前のコード。一応、対比としてのせておきました。
'    strSql01 = strSql01 & " (具材名,相対重量,玉ねぎ重量,基本重量との差分,基本重量,対玉ねぎ比)"
'    strSql01 = strSql01 & " VALUES('合わせ',2,0,0.5,0.7,55);"



    '「UpdateByADO_ACE04」を呼び出すことで、
    'SQL命令文どおりの書き換えを実行。

    Call UpdateByADO_ACE04(strSql01)

    'SQLで新規レコードを追加すると、
    '追加した最後の行に、接頭辞の「'」が含まれたセルが
    'できてしまうようなので、そのセルの「'」を消す。
    '特に文字列型の列のデータ。
    '数値、日付、論理、は「'」は付かないっぽいです。

    Call UsedLastRowPfixChrDel01


End Sub








Sub UpdateByADO_ACE02()
 
 '不要なコメントやコードをすべて削除した状態
 
 '新規コマンド環境を作らずに、Connectionオブジェクトを直接使って
 'ExecuteメソッドでSQLを実行する方法です。
 
 
 'ヘルプより
 '注意   Command オブジェクトを使わずにクエリを実行するには、
 'クエリ文字列を Connection オブジェクトの Execute メソッド、
 'または Recordset オブジェクトの Open メソッドに渡します。
 'ただし、コマンド テキストを永続しておいて、再実行させる場合、
 'またはクエリ パラメータを使う場合は、Command オブジェクトを
 '使用する必要があります。
 
 'というわけで・・・
 'ここではパラメータクエリも使わないので、
 'Connection オブジェクトの Execute メソッドを直接使って
 'SQLを実行してみます。
 
 'ACEタイプの接続での書き換えです。
 '（xlsはともかく、xlsm・xlsxの場合、おそらく、
 '  ACEタイプの接続でないと、閉じたまま書き換えられないので・・・）
 
 '▲なお、事前に、MicrosoftQueryを作ってなくても書き換え可能です。▲
 
 
   '★ 変数設定
   
    Dim Cn              As ADODB.Connection     '書込み対象のXLSファイルの「ADO接続文字用のオブジェクト」用の変数
    Dim TrgtXLFName     As String               '書込み対象のXLSファイルのフルパス格納用の変数
 
'    Dim Cmd01           As ADODB.Command        '「SQLコマンド実行環境用のオブジェクト」用の変数
    Dim CmdSqlStr01     As String               'SQL文を格納するための変数
 
   '★ 以下、メインプログラムです。
   
'    Application.ScreenUpdating = False          'Excelの画面描画をいったん停止。今の画面のまま変化させないようにする。
    
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    '読み込み先のExcelファイルに接続。
    
    '（ターゲットファイルの読み書きが
    '  できるようにするための準備。）
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    TrgtXLFName = "D:\test88\1.xlsm"   '読み込みたい先のファイルのフルパスを指定
    Set Cn = New ADODB.Connection    'ADOにてのデータ接続オブジェクトの作成
 
    '読み込み先のファイルに読み書きモードで（？）接続。
    'よくわかってません。すみません。
    'ここではACEエンジンタイプでの接続方法を使います。
    '（JETエンジンタイプでの接続方法もありますが、xlsはともかく、
    '  xlsm・xlsxだと多分相手ファイルが開かれてないとエラーになるので
    '  ここではやりません。相手を閉じたまま書き換えたいので。）
    
    Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & TrgtXLFName & ";" & _
                        "Extended Properties=""Excel 12.0"""
                        
                        
                        
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    '読み込み先のファイルに対して、SQLコマンドを実行
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
'    'SQL実行のための、コマンド実行環境機能の準備
'    Set Cmd01 = New ADODB.Command   '新しい（空の）SQLコマンド実行環境機能の作成。
'    Cmd01.ActiveConnection = Cn     'その実行環境機能を読み込み先ファイルとの「接続」に紐付け（＝関連付け）。
 
'    Debug.Print Cmd01.ActiveConnection.ConnectionString
   

    
        'テーブル名をできるだけ省略した形でSQL文を作成
        CmdSqlStr01 = "UPDATE `sheet3$`"
        CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 111"
        CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '玉ねぎ';"
 
    
    
    Cn.Execute CmdSqlStr01
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'オブジェクト変数の後始末
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'    Set Cmd01 = Nothing
    
    Cn.Close
    Set Cn = Nothing
    
End Sub


'########################################################################################
'追加した最後の行に、接頭辞の「'」が含まれたセルがあったら
'そのセルの「'」を消す処理。
'ただし、このままだと、「閉じたファイル」の「'」は消せない。
'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。
'########################################################################################

Sub UsedLastRowPfixChrDel01()

  
    Dim c                       As Range
    Dim LastRow01               As Long
    Dim UsdLastRowAdrs01        As String
    Dim UsdRng01                As Range
    

    Application.ScreenUpdating = False
    
    '接頭辞の「'」を消すために、
    '書式の貼り付けに使うので
    'どこのセルでもいいのでコピーしておきます。
    Range("A1").Select
    Range("A1").Copy
    
    '================================================================
    '「最後の行」のセル範囲のアドレスを取得する作業。
    '================================================================
    
    'コードが長く・読みにくくなってしまうので
    'とりあえず、UesedRangeをオブジェクト変数に代入
    Set UsdRng01 = ActiveSheet.UsedRange
  
    '新規追加した最後の行の行数
    LastRow01 = UsdRng01.Rows.Count
    
    'UsedRangeの最後の行のアドレスを変数に代入
    UsdLastRowAdrs01 = UsdRng01.Rows(LastRow01).Address
    
    
    '================================================================
    '「最後の行」のうち、接頭辞の「'」を含むセルから、それを消す作業。
    '================================================================
    
    For Each c In Range(UsdLastRowAdrs01)
  
        If TypeName(c.Value) = "String" Then
        
'            Debug.Print c.Value
            
            If c.PrefixCharacter = "'" Then
            'もし、接頭辞に「'」がついていたら消す。
            
'                '「加算」の貼り付けで消す。
'                c.PasteSpecial _
'                    Paste:=xlPasteAll, _
'                    Operation:=xlAdd, _
'                    SkipBlanks:=False, _
'                    Transpose:=False
    
                '書式の貼り付けで消してもいいみたい。
                c.PasteSpecial _
                    Paste:=xlPasteFormats, _
                    Operation:=xlNone, _
                    SkipBlanks:=False, _
                    Transpose:=False
            Else
            
            End If

            '逆に、文字列型のセルにSQLで数字を入れると
            '「このセルにある数値が、テキスト形式か、
            '　　　またはアポストロフィで始まっています」
            'というグリーンの隅っこ三角エラーマークができて
            'しまうので、それおｗ消す処理
            If IsNumeric(c.Value) = True Then
            
                c.Value = c.Value * 1           '今のセルのもともとのセルの値に１をかけて、今のセルに代入（要するに上書き）する。
                c.NumberFormatLocal = "G/標準"    '数値にした際にセル書式が「数値」になってしまうので「標準」にする
                 
            Else
            
            End If

        ElseIf TypeName(c.Value) = "Boolean" Then
　　　　　'値がTrue/Falseの2値だった場合は何もしない。

        ElseIf TypeName(c.Value) = "Date" Then
　　　　　'値が日付型だった場合は何もしない。

        ElseIf TypeName(c.Value) = "Double" Then　
　　　　　'値が数値型だった場合は何もしない。
                 
        Else
　　　　　'値がその他の場合も何もしない。
               
        End If
        
    Next c
    
    Application.CutCopyMode = False
    
  
End Sub


'
'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

434

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

Option Explicit

'####################################################################

'自ファイル（xlsm）の表の内容を、ADOにて、「SQL」または

'レコードセットで書き換えテストを行うサンプル

'####################################################################

Sub UpdateByADO_ACE01()

'★ 変数設定

Dim Cn As ADODB.Connection '書込み対象のXLSファイルの「ADO接続文字用のオブジェクト」用の変数

Dim rs As ADODB.Recordset '列名を認識できているかのチェックや書き換えのための「レコードセットオブジェクト」用の変数

Dim TrgtXLFName As String '書込み対象のXLSファイルのフルパス格納用の変数

Dim CmdSqlStr01 As String 'SQL文を格納するための変数

'★ 以下、メインプログラムです。

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'読み込み先のExcelファイルに接続。

'（ターゲットファイルの読み書きが

' できるようにするための準備。）

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'接続先を自ファイルに設定ます。

TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name '読み込みたい先のファイルのフルパスを指定（ここでは自ファイルとしています。）

Set Cn = New ADODB.Connection 'ADOにてのデータ接続用のある意味「空の」オブジェクトの作成

'自ファイルに接続（「空の」オブジェクトに接続詳細設定を指定）

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & TrgtXLFName & ";" & _

"Extended Properties=""Excel 12.0"""

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'読み込み先のファイルに対して、SQLコマンドを実行

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'テーブル名をできるだけ省略した形でSQL文を作成

'基本、SQL命令だと、ループを使わなくても一括で

'データの書き換えができます。

'たとえば、以下の命令文の場合だと、

'「具材名」という列の値が「白みそ」の行は（もし複数行あっても）全部、

'その「相対重量」の列の値を一括で「10」に書き換えます。

CmdSqlStr01 = ""

CmdSqlStr01 = CmdSqlStr01 & "UPDATE `Sheet3$`"

CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 10"

CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '白みそ';"

'SQLの実行

Cn.Execute CmdSqlStr01

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'ついでに、一応、列名を認識しているかと、

'「SQL」ではなくてADOの「レコードセット」での

'書き換えも可能かどうかの一応のチェック。

'「レコードセット」の場合は、指定した行に飛んで書き変えたり

'ループで書き換えることが可能です。

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

' Set rs = Cn.Execute("`Sheet3$`")

' Cn.CursorLocation = adUseClient

Set rs = New ADODB.Recordset

rs.Open "`Sheet3$`", Cn, adOpenStatic, adLockOptimistic '「adOpenStatic, adLockOptimistic 」を使うと書き換え（更新）が可能なレコードセットになるようです。MoveLast を使っても「行セットは逆方向フェッチをサポートしていません」のエラーも出ません。

rs.MoveLast '一番最後のレコードへジャンプ

Debug.Print rs(1).Name '変更前の2列目の列名の表示

Debug.Print rs(1).Value '変更前の2列目の値の表示

rs(1).Value = 1 '2列目の値を1に書き換え（この段階では書き換え指示のみ）

rs.Update 'ここで書き換えを確定

Debug.Print rs(1).Name '変更後の2列目の列名の表示

Debug.Print rs(1).Value '変更後の2列目の値の表示

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'オブジェクト変数の後始末（オブジェクトをメモリ上から消す・破棄）

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Cn.Close

Set Cn = Nothing

End Sub

'####################################################################

'上記のプロシージャを改造して、自作関数化しまいた。

'呼び出すと、指定した「SQL」文での書き換えを実行します。

'####################################################################

Sub UpdateByADO_ACE04(CmdSqlStr01 As String)

'★ 変数設定

Dim Cn As ADODB.Connection '書込み対象のXLSファイルの「ADO接続文字用のオブジェクト」用の変数

Dim TrgtXLFName As String '書込み対象のXLSファイルのフルパス格納用の変数

' Dim CmdSqlStr01 As String 'SQL文を格納するための変数

'★ 以下、メインプログラムです。

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'読み込み先のExcelファイルに接続。

'（ターゲットファイルの読み書きが

' できるようにするための準備。）

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'接続先を自ファイルに設定ます。

TrgtXLFName = ThisWorkbook.Path & "\" & ThisWorkbook.Name '読み込みたい先のファイルのフルパスを指定

Set Cn = New ADODB.Connection 'ADOにてのデータ接続用のある意味「空の」オブジェクトの作成

'自ファイルに接続（「空の」オブジェクトに接続詳細設定を指定）

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & TrgtXLFName & ";" & _

"Extended Properties=""Excel 12.0"""

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'読み込み先のファイルに対して、SQLコマンドを実行

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

' '「値やオブジェクトを返さない自作関数」に作り変えたので、以下の4行すべてコメントアウト。使いません。

' 'テーブル名をできるだけ省略した形でSQL文を作成

' CmdSqlStr01 = "UPDATE `Sheet3$`"

' CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 20"

' CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '白みそ';"

'SQLの実行

Cn.Execute CmdSqlStr01

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'オブジェクト変数の後始末（オブジェクトをメモリ上から消す・破棄）

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

Cn.Close

Set Cn = Nothing

End Sub

'####################################################################

'「UpdateByADO_ACE04」という自作関数を呼び出して

'自ファイルの内容を書き換えたり、レコード追加したりするテスト。

'ただ、SQL実行のたびにADOでのオブジェクト作成をしているので

'ムダが多いかも・・・。もっとすっきり書けるかもしれないので

'ご自分でも色々かんがえてみてください。

'####################################################################

Sub OperationTest01()

Dim strSql01 As String 'データを書き換えるためのSQL命令文を格納するための変数を作成

'既存レコードの書き換えの例

strSql01 = ""

strSql01 = strSql01 & "UPDATE `Sheet3$`"

strSql01 = strSql01 & " SET 相対重量 = 30"

strSql01 = strSql01 & " WHERE 具材名 = '白みそ';"

'「UpdateByADO_ACE04」を呼び出すことで、

'SQL命令文どおりの書き換えを実行。

Call UpdateByADO_ACE04(strSql01)

'新規レコードの追加の例

strSql01 = ""

strSql01 = strSql01 & "INSERT INTO `Sheet3$`"

strSql01 = strSql01 & " (具材名,相対重量,玉ねぎ重量,基本重量との差分,基本重量,対玉ねぎ比)"

strSql01 = strSql01 & " VALUES('合わせ',2,0,0.5,0.7,55);"

' strSql01 = strSql01 & " (相対重量,玉ねぎ重量,基本重量との差分,基本重量,対玉ねぎ比) "

' strSql01 = strSql01 & " VALUES(2,0,0.5,0.7,55) ;"

'「UpdateByADO_ACE04」を呼び出すことで、

'SQL命令文どおりの書き換えを実行。

Call UpdateByADO_ACE04(strSql01)

End Sub

'####################################################################

'「UpdateByADO_ACE04」という自作関数を呼び出して

'自ファイルの内容に新規レコードを追加するテスト。

'どの列の値をどう書き換えたかなどが分かりやすく

'「見える」ようにしてみました。

'ムダが多いかもしれませんが、こういう書き方もできるということで。。。

'もっといい案をご自分でも考えてみてください。

'####################################################################

Sub OperationTest02()

Dim strSql01 As String 'データを書き換えるためのSQL命令文を格納するための変数を作成

Dim ClmnName(7) As String '列名の指定のための配列変数

Dim Data01(7) As Variant '値の指定のための配列変数

Dim DateNum01 As Long '日付をいったんシリアル値に変換するための変数

ClmnName(0) = "具材名"

Data01(0) = "'合わせみそ'"

ClmnName(1) = "相対重量"

Data01(1) = 0

ClmnName(2) = "玉ねぎ重量"

Data01(2) = 0

ClmnName(3) = "基本重量との差分"

Data01(3) = 0

ClmnName(4) = "基本重量"

Data01(4) = 0

ClmnName(5) = "対玉ねぎ比"

Data01(5) = 0

ClmnName(6) = "フラグ01"

Data01(6) = True

ClmnName(7) = "日付"

'SQLで日付をセルに書き込む場合は、シリアル値をセルに書き込まないと

'変な日付になってしまうようなので、こうしています。

DateNum01 = Date '日付をいったんシリアル値に変換

Data01(7) = DateNum01 '変換したシリアル値を変数に代入

'新規レコードの追加の例

strSql01 = ""

strSql01 = strSql01 & "INSERT INTO `Sheet3$`"

strSql01 = strSql01 & " (" & Join(ClmnName, ",") & ") "

strSql01 = strSql01 & " VALUES(" & Join(Data01, ",") & ");"

' '以下は、修正前のコード。一応、対比としてのせておきました。

' strSql01 = strSql01 & " (具材名,相対重量,玉ねぎ重量,基本重量との差分,基本重量,対玉ねぎ比)"

' strSql01 = strSql01 & " VALUES('合わせ',2,0,0.5,0.7,55);"

'「UpdateByADO_ACE04」を呼び出すことで、

'SQL命令文どおりの書き換えを実行。

Call UpdateByADO_ACE04(strSql01)

'SQLで新規レコードを追加すると、

'追加した最後の行に、接頭辞の「'」が含まれたセルが

'できてしまうようなので、そのセルの「'」を消す。

'特に文字列型の列のデータ。

'数値、日付、論理、は「'」は付かないっぽいです。

Call UsedLastRowPfixChrDel01

End Sub

Sub UpdateByADO_ACE02()

'不要なコメントやコードをすべて削除した状態

'新規コマンド環境を作らずに、Connectionオブジェクトを直接使って

'ExecuteメソッドでSQLを実行する方法です。

'ヘルプより

'注意 Command オブジェクトを使わずにクエリを実行するには、

'クエリ文字列を Connection オブジェクトの Execute メソッド、

'または Recordset オブジェクトの Open メソッドに渡します。

'ただし、コマンドテキストを永続しておいて、再実行させる場合、

'またはクエリパラメータを使う場合は、Command オブジェクトを

'使用する必要があります。

'というわけで・・・

'ここではパラメータクエリも使わないので、

'Connection オブジェクトの Execute メソッドを直接使って

'SQLを実行してみます。

'ACEタイプの接続での書き換えです。

'（xlsはともかく、xlsm・xlsxの場合、おそらく、

' ACEタイプの接続でないと、閉じたまま書き換えられないので・・・）

'▲なお、事前に、MicrosoftQueryを作ってなくても書き換え可能です。▲

'★ 変数設定

Dim Cn As ADODB.Connection '書込み対象のXLSファイルの「ADO接続文字用のオブジェクト」用の変数

Dim TrgtXLFName As String '書込み対象のXLSファイルのフルパス格納用の変数

' Dim Cmd01 As ADODB.Command '「SQLコマンド実行環境用のオブジェクト」用の変数

Dim CmdSqlStr01 As String 'SQL文を格納するための変数

'★ 以下、メインプログラムです。

' Application.ScreenUpdating = False 'Excelの画面描画をいったん停止。今の画面のまま変化させないようにする。

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'読み込み先のExcelファイルに接続。

'（ターゲットファイルの読み書きが

' できるようにするための準備。）

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

TrgtXLFName = "D:\test88\1.xlsm" '読み込みたい先のファイルのフルパスを指定

Set Cn = New ADODB.Connection 'ADOにてのデータ接続オブジェクトの作成

'読み込み先のファイルに読み書きモードで（？）接続。

'よくわかってません。すみません。

'ここではACEエンジンタイプでの接続方法を使います。

'（JETエンジンタイプでの接続方法もありますが、xlsはともかく、

' xlsm・xlsxだと多分相手ファイルが開かれてないとエラーになるので

' ここではやりません。相手を閉じたまま書き換えたいので。）

Cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _

"Data Source=" & TrgtXLFName & ";" & _

"Extended Properties=""Excel 12.0"""

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'読み込み先のファイルに対して、SQLコマンドを実行

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

' 'SQL実行のための、コマンド実行環境機能の準備

' Set Cmd01 = New ADODB.Command '新しい（空の）SQLコマンド実行環境機能の作成。

' Cmd01.ActiveConnection = Cn 'その実行環境機能を読み込み先ファイルとの「接続」に紐付け（＝関連付け）。

' Debug.Print Cmd01.ActiveConnection.ConnectionString

'テーブル名をできるだけ省略した形でSQL文を作成

CmdSqlStr01 = "UPDATE `sheet3$`"

CmdSqlStr01 = CmdSqlStr01 & " SET 相対重量 = 111"

CmdSqlStr01 = CmdSqlStr01 & " WHERE 具材名 = '玉ねぎ';"

Cn.Execute CmdSqlStr01

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

'オブジェクト変数の後始末

'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

' Set Cmd01 = Nothing

Cn.Close

Set Cn = Nothing

End Sub

'########################################################################################

'追加した最後の行に、接頭辞の「'」が含まれたセルがあったら

'そのセルの「'」を消す処理。

'ただし、このままだと、「閉じたファイル」の「'」は消せない。

'テストなので、現在開いている、しかも、アクティブなシートのみにしか対応していません。

'########################################################################################

Sub UsedLastRowPfixChrDel01()

Dim c As Range

Dim LastRow01 As Long

Dim UsdLastRowAdrs01 As String

Dim UsdRng01 As Range

Application.ScreenUpdating = False

'接頭辞の「'」を消すために、

'書式の貼り付けに使うので

'どこのセルでもいいのでコピーしておきます。

Range("A1").Select

Range("A1").Copy

'================================================================

'「最後の行」のセル範囲のアドレスを取得する作業。

'================================================================

'コードが長く・読みにくくなってしまうので

'とりあえず、UesedRangeをオブジェクト変数に代入

Set UsdRng01 = ActiveSheet.UsedRange

'新規追加した最後の行の行数

LastRow01 = UsdRng01.Rows.Count

'UsedRangeの最後の行のアドレスを変数に代入

UsdLastRowAdrs01 = UsdRng01.Rows(LastRow01).Address

'================================================================

'「最後の行」のうち、接頭辞の「'」を含むセルから、それを消す作業。

'================================================================

For Each c In Range(UsdLastRowAdrs01)

If TypeName(c.Value) = "String" Then

' Debug.Print c.Value

If c.PrefixCharacter = "'" Then

'もし、接頭辞に「'」がついていたら消す。

' '「加算」の貼り付けで消す。

' c.PasteSpecial _

' Paste:=xlPasteAll, _

' Operation:=xlAdd, _

' SkipBlanks:=False, _

' Transpose:=False

'書式の貼り付けで消してもいいみたい。

c.PasteSpecial _

Paste:=xlPasteFormats, _

Operation:=xlNone, _

SkipBlanks:=False, _

Transpose:=False

Else

End If

'逆に、文字列型のセルにSQLで数字を入れると

'「このセルにある数値が、テキスト形式か、

'　　　またはアポストロフィで始まっています」

'というグリーンの隅っこ三角エラーマークができて

'しまうので、それおｗ消す処理

If IsNumeric(c.Value) = True Then

c.Value = c.Value * 1 '今のセルのもともとのセルの値に１をかけて、今のセルに代入（要するに上書き）する。

c.NumberFormatLocal = "G/標準" '数値にした際にセル書式が「数値」になってしまうので「標準」にする

Else

End If

ElseIf TypeName(c.Value) = "Boolean" Then

　　　　　'値がTrue/Falseの2値だった場合は何もしない。

ElseIf TypeName(c.Value) = "Date" Then

　　　　　'値が日付型だった場合は何もしない。

ElseIf TypeName(c.Value) = "Double" Then　

　　　　　'値が数値型だった場合は何もしない。

Else

　　　　　'値がその他の場合も何もしない。

End If

Next c

Application.CutCopyMode = False

End Sub

投稿タグ: AccessVBA, Accessの独学, ExcelVBA, パソコンでの自動化, マクロ, 独学, 自動化

MENU

関連記事

最近の投稿

カテゴリー

MENU

関連記事

最近の投稿

カテゴリー

タグ