Excel2010で、『開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）

● Excel2010で、『開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）
　　
　　
本記事は「32bitの」Excelだけでのお話です。ご注意ください。
ただ、OSが64bitであっても、インストールされているExcelが32bitなら大丈夫ではないかと思います。
現在のExcelがどのbitかは、「Excel bit 確認」などの語句でWeb検索してみてください。

※参考記事
Excel上で使う、DAO、ADO、Microsoft Query（ExcelVBAのQueryTableオブジェクト）、では「読み込み」だけでなく、「閉じたまま」の「複雑条件集計」や「書き込み」もできます。
ただし、Excel同士のデータのやりとりの場合はなんと！「削除」ができないので対応策が必要です。
（未確認ですが片方がExcelだとダメかもしれません。逆に、両方Accessとか、AccessADP＋SQLServer、などのようなケースなら削除できます。AccessMdb＋MySQLも多分ですがODBCのリンクテーブルにすれば、OKなのではないかと思います。）
対応策はこちら。『できないこと』。（←その他の「できないこと」も書いてあります。なお、「あえて削除しない」ことも結構あります。不正対策や整合性維持のためです。基本、削除フラグを立てるだけでも対応できます。もちろん、読み込みだけに使っても構いません。）
Excel2010で、『開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）
『～SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」～Microsoft Query（QueryTableオブジェクト）を利用する場合～』
『ADO：Excel2010で、開かれていない閉じたままのブック・Excel（xls）データをできるだけ速く読み込む方法ーその２（ADOにて）』
『Access2000VBA・Excel2000VBA独学～別の閉じたExcelファイルを ”閉じたまま” 読み込みや書き込をする方法～５つ』
　　
　　

※Shift＋TABキー、もしくは、Homeキー、Homeキー＋TAB数回、を押すと、目次付近に戻れます。
　　
　　

★ はじめに

「32bitの」Excelファイルの値を、そのExcelファイルを開かずに読みに行ったり書き換えたりする方法です。

（「64bitの」ExcelではDAOは使えないかもしれません（未確認です。）その場合はADOでやる方法があります。→こちら、でテストしましたが、これは32bitExcelでやったテストですので、64bitだとまた微妙にちがうかもしれません。ご参考にとどめるだけにしてください。）

閉じたままのExcelファイルのデータを取得するには、「ExecuteExcel4Macro」を使ったり、「Excel.Application」などで読み込む方法があるようなのですが、以下のサイトによりますと、データの量が多いと速度が遅くなる？みたいです。
http://excel-ubara.com/excelvba5/EXCELVBA242.html

というわけで、DAOとかADOなどのミドルウェアを使う方法で試してみました。

で、今回は「読み込み」だけではなく「書き込み」も、操作対象のExcelファイルを「閉じたまま」できるかをテストしてみました。

その結果、以降のようになりました。
（『Excelの真の基礎～効率を良くする作表の基本手順（ちょっと詳細版）～Excelやパソコンを単なる電卓としてではなく、真のコンピュータとして使うために～Microsoft Queryやピボットテーブルを使えるようにするために』のような作表がしてあることが前提です。）

結論から言うと、「閉じたまま」、読み込みだけでなく「書き込み」も同時に行いたい場合は、

「xlsファイル＋DAO 3.6（or ADO 2.1～2.5）」
（修正先のファイルがxls。操作側（操作元）はxlsmやPersonal.xlsb（≒xlsxファイル）でも可。）
※「Extended Properties」の設定値は「Excel8.0」。のみにします。
（他にもあるかもですが）
とりあえず「Excel 8.0;HDR=YES;IMEX=1」だと読み取り専用になってしまうっぽいです。
※DAOでは、Editメソッド＋Updateメソッド、あるいは、SQLでの書き換えができます。（SQLの実行は、recordsetではなくdatabaseオブジェクトのExecuteメソッドで。 Excelの場合はSQL文の中でのシートの指定には、$付きのシート名を「`」＝バッククォートで囲むことが必要。）

でやるか、もしくは、

「xlsx・xlsmファイル」を使うなら
DAOはやめて、ADOで、しかも「ACEエンジン系構文」でやる（JETエンジン系の構文は不可）。
（修正先のファイルがxlsx・xlsmの場合です。操作元はxlsmやPersonal.xlsb（≒xlsxファイル）でも可。）
※「Extended Properties」の設定値は「Excel 12.0」。のみにします。
（他にもあるかもですが）
とりあえず「Excel 12.0;HDR=YES;IMEX=1」だと読み取り専用になってしまうっぽいです。
※ADOでは、Updateメソッドのみ、あるいは、SQLでの書き換えができます。（SQLの実行は、ConnectionオブジェクトのExecuteメソッドで。Excelの場合はSQL文の中でのシートの指定には、$付きのシート名を「`」＝バッククォートで囲むことが必要。）

のがエラーや不都合が少なそう・・・

です。

そのほかの方法は、閉じたまま処理できなかったり、面倒な不都合が出る可能性が上がってしまうかもです。

なお、いずれも、レコードセットのUpdateメソッドなどを使うか、SQLで「修正先のファイルを閉じたままで」書き換えができました。

以下の表に、実験結果とそのことを簡単にですが、まとめてみましたのでご参考にしてみてください。（ざっとでやったので間違ってるかもしれません。もしそうならごめんなさい。ご自分でも色々と試してみてください。）

・『 DAOやADOで、閉じたExcelファイルを、閉じたまま、「読み書き」することについて』
https://euc-access-excel-db.com/00000WPhtml/dao_ado_close_file_read_write01.htm

以下はA4用紙1枚としての印刷用です（↓見る分にはCtrl＋＋やCtrl＋- 等々にて拡大縮小ができます。）
https://euc-access-excel-db.com/00000WPPDF/dao_ado_close_file_read_write01.pdf

　　
　　
★DAOでのSQLでの書き換え例

'
'
Option Explicit

Private Sub ReadByDao01()

'操作元のこのプロシージャを書くxlsmはどこに置いてもOKです。
'ただし、必ずVBEの参照設定でMicrosoft DAO 3.6 Object Library にチェックを入れておきます。
'書き換え先のファイルは「D:\aa\DAOテスト.xls」として配置し、
'Shee1に最低限、「氏名」と「年齢」の列を作った適当な表を作成します。
'Dドライブのルートには置きません。

    Dim DB           As DAO.Database
    Dim RS           As DAO.Recordset
    Dim xlFName      As String
    Dim SheetName    As String
    Dim StrData01    As String
    Dim sqlstr01     As String
 
 
    'データベースファイル名定義、システムテーブル定義
    xlFName = "D:\aa\DAOテスト.xls"
    SheetName = "Sheet1$"  '「Sheet1」をシステムテーブルとして読み込む。
                           '「$」を付けるとシステムテーブルになる。
   
   
    'データベース(xlFName)ファイルを開く（ここではxlsファイル）
    'HDR=NO →「IMEX」の値を「=1」にしたときは、1行目を列名として読込む。（どうやら数値もテキストとして読み込まれるようです）
    'HDR=YES →「IMEX」の値を「=1」にしたときは、1行目を列名として読込まない。（どうやら数値は数値、文字列は文字列として読み込まれるようです）
    'IMEX=1→表示されているセル書式の値でデータを吸い込む。（今回のテストでは完全にはそうはなりませんでした。よくわかりません。）
    '        ただし、https://support.microsoft.com/ja-jp/help/194124/prb-excel-values-returned-as-null-using-dao-openrecordset
    '        や http://blog.sorceryforce.net/?p=154 を参照
    'Excel 8.0→「VBA Excel バージョン番号」でGoogle検索。
    '            8.0で2000でも2010でも動いた。
    
    '   Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0;HDR=NO;IMEX=1")

　　　　'指定したxlsファイルを、「閉じたままでも書き込み可能なモード」で開く。
        Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0")
 
 
 
    'レコードセットを定義
    Set RS = DB.OpenRecordset(SheetName)

    'ただし、本サンプルでは、このレコードセットはSQLで書き換えるためのものではなく、
    'SQLでセルの値を書きかえたあとに、その結果をイミディエイトウィンドウに
    '表示するために使います。
    'SQLでの書き換えには、このあと「DB（本サンプルでのdatabaseオブジェクト）の
    'Execute メソッドにておこないます。
   
   
    'SQL文（書き換え命令文）の生成（この場面ではRSは無関係）
    sqlstr01 = "Update `Sheet1$` SET 氏名 = 100 WHERE 年齢 = 21;"

        '「Sheet1の表の年齢が21の行はすべて、氏名を「100」に書き換える」、という意味です。
        '年齢が21の行がもし3行あったら、その3行すべての「氏名」が「100」に書き換わります。
'    sqlstr01 = "Update `Sheet1$` SET `氏名` = 2 WHERE `年齢` = 21;"
        'シート（システムテーブル）の指定にはシート名を「`」（バッククォート）で囲むことが必要です。
        '（Microsoft QueryでのSQLの仕様と同じです。）
        '列名も同様にしないといけないこともあるかもしれません。（今回は無しでもOKでした。）
        '各列において、その列のデータ型と違う型のデータを書き込むと
        '書き込めず空白になりますので注意が必要です。この場合、
        'エラーも出ないまま空白になってしまうので注意が必要です。
        '列のデータ型は、その列の最初の数行をExcelが見に行って、
        '自動的に判別・決定するようです。
        '例えば、数値型の列に、文字列型のデータを書き込むSQLを書いてしまうと
　      'その書き換え対象のセルはすべて空白に書き換わってしまいます。
    
    'SQLを実行（＝修正先のExcelファイルを閉じたまま、書き換えを実行。この場面でもRSは無関係）
    DB.Execute sqlstr01
    
    '以下のコード、本当は要らないけど、念のため書いてしまいました。
　　'表（＝レコードセット）の中の最初の行（レコード）にスタンバイします。
    RS.MoveFirst
    
　　'最初の行から最後の行までを、以下のループ処理。（イミディエイトへの書き換え結果の表示の準備）
　　'表示するデータを変数に格納して準備します。
    Do Until RS.EOF
   
        With RS 'A列・B列・C列のデータを取得
      
        '「HDR=NO」＝1行目を列名として読み込まない場合その1→列名を「Index番号」で表現して変数に吸い込む
'        StrData01 = StrData01 & .Fields(0) & vbTab & .Fields(1) & vbTab & .Fields(2) & vbCrLf
 
        '「HDR=NO」＝1行目を列名として読み込まない場合その2→列名を「システムテーブルとしての列名」で表現して変数に吸い込む
'        StrData01 = StrData01 & .Fields("F1") & vbTab & .Fields("F2") & vbTab & .Fields("F3") & vbCrLf
         
        '「HDR=YES」＝1行目を列名として読み込む場合→列名を1行目の「セルの列名」で表現して変数に吸い込む
        StrData01 = StrData01 & .Fields("氏名") & vbTab & .Fields("年齢") & vbTab & .Fields("性別") & vbCrLf
         
        .MoveNext  '次のレコード（行）に移動
         
        End With
      
    Loop
   
　　'イミディエイトウィンドウに書き換え結果を表示
    Debug.Print StrData01
    
   
    RS.Close
    DB.Close
    Set RS = Nothing
    Set DB = Nothing
   
End Sub


'
'

100

101

102

103

104

105

106

107

108

109

110

111

Option Explicit

Private Sub ReadByDao01()

'操作元のこのプロシージャを書くxlsmはどこに置いてもOKです。

'ただし、必ずVBEの参照設定でMicrosoft DAO 3.6 Object Library にチェックを入れておきます。

'書き換え先のファイルは「D:\aa\DAOテスト.xls」として配置し、

'Shee1に最低限、「氏名」と「年齢」の列を作った適当な表を作成します。

'Dドライブのルートには置きません。

Dim DB As DAO.Database

Dim RS As DAO.Recordset

Dim xlFName As String

Dim SheetName As String

Dim StrData01 As String

Dim sqlstr01 As String

'データベースファイル名定義、システムテーブル定義

xlFName = "D:\aa\DAOテスト.xls"

SheetName = "Sheet1$" '「Sheet1」をシステムテーブルとして読み込む。

'「$」を付けるとシステムテーブルになる。

'データベース(xlFName)ファイルを開く（ここではxlsファイル）

'HDR=NO →「IMEX」の値を「=1」にしたときは、1行目を列名として読込む。（どうやら数値もテキストとして読み込まれるようです）

'HDR=YES →「IMEX」の値を「=1」にしたときは、1行目を列名として読込まない。（どうやら数値は数値、文字列は文字列として読み込まれるようです）

'IMEX=1→表示されているセル書式の値でデータを吸い込む。（今回のテストでは完全にはそうはなりませんでした。よくわかりません。）

' ただし、https://support.microsoft.com/ja-jp/help/194124/prb-excel-values-returned-as-null-using-dao-openrecordset

' や http://blog.sorceryforce.net/?p=154 を参照

'Excel 8.0→「VBA Excel バージョン番号」でGoogle検索。

' 8.0で2000でも2010でも動いた。

' Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0;HDR=NO;IMEX=1")

　　　　'指定したxlsファイルを、「閉じたままでも書き込み可能なモード」で開く。

Set DB = OpenDatabase(xlFName, False, False, "Excel 8.0")

'レコードセットを定義

Set RS = DB.OpenRecordset(SheetName)

'ただし、本サンプルでは、このレコードセットはSQLで書き換えるためのものではなく、

'SQLでセルの値を書きかえたあとに、その結果をイミディエイトウィンドウに

'表示するために使います。

'SQLでの書き換えには、このあと「DB（本サンプルでのdatabaseオブジェクト）の

'Execute メソッドにておこないます。

'SQL文（書き換え命令文）の生成（この場面ではRSは無関係）

sqlstr01 = "Update `Sheet1$` SET 氏名 = 100 WHERE 年齢 = 21;"

'「Sheet1の表の年齢が21の行はすべて、氏名を「100」に書き換える」、という意味です。

'年齢が21の行がもし3行あったら、その3行すべての「氏名」が「100」に書き換わります。

' sqlstr01 = "Update `Sheet1$` SET `氏名` = 2 WHERE `年齢` = 21;"

'シート（システムテーブル）の指定にはシート名を「`」（バッククォート）で囲むことが必要です。

'（Microsoft QueryでのSQLの仕様と同じです。）

'列名も同様にしないといけないこともあるかもしれません。（今回は無しでもOKでした。）

'各列において、その列のデータ型と違う型のデータを書き込むと

'書き込めず空白になりますので注意が必要です。この場合、

'エラーも出ないまま空白になってしまうので注意が必要です。

'列のデータ型は、その列の最初の数行をExcelが見に行って、

'自動的に判別・決定するようです。

'例えば、数値型の列に、文字列型のデータを書き込むSQLを書いてしまうと

　 'その書き換え対象のセルはすべて空白に書き換わってしまいます。

'SQLを実行（＝修正先のExcelファイルを閉じたまま、書き換えを実行。この場面でもRSは無関係）

DB.Execute sqlstr01

'以下のコード、本当は要らないけど、念のため書いてしまいました。

　　'表（＝レコードセット）の中の最初の行（レコード）にスタンバイします。

RS.MoveFirst

　　'最初の行から最後の行までを、以下のループ処理。（イミディエイトへの書き換え結果の表示の準備）

　　'表示するデータを変数に格納して準備します。

Do Until RS.EOF

With RS 'A列・B列・C列のデータを取得

'「HDR=NO」＝1行目を列名として読み込まない場合その1→列名を「Index番号」で表現して変数に吸い込む

' StrData01 = StrData01 & .Fields(0) & vbTab & .Fields(1) & vbTab & .Fields(2) & vbCrLf

'「HDR=NO」＝1行目を列名として読み込まない場合その2→列名を「システムテーブルとしての列名」で表現して変数に吸い込む

' StrData01 = StrData01 & .Fields("F1") & vbTab & .Fields("F2") & vbTab & .Fields("F3") & vbCrLf

'「HDR=YES」＝1行目を列名として読み込む場合→列名を1行目の「セルの列名」で表現して変数に吸い込む

StrData01 = StrData01 & .Fields("氏名") & vbTab & .Fields("年齢") & vbTab & .Fields("性別") & vbCrLf

.MoveNext '次のレコード（行）に移動

End With

Loop

　　'イミディエイトウィンドウに書き換え結果を表示

Debug.Print StrData01

RS.Close

DB.Close

Set RS = Nothing

Set DB = Nothing

End Sub

　　
　　
★ ADOでの、ACE系接続での、SQLで書き換えの例など
（無駄なプロシージャがあるかもしれないのですみません）

'
'
Option Explicit

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:\b\tes1.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 `動的な表$`"
'    CmdSqlStr01 = CmdSqlStr01 & " SET `動的な表$`.販売員 = 'pppヴｊｙｖ'"
'    CmdSqlStr01 = CmdSqlStr01 & " WHERE `動的な表$`.連番 = 1"
    
        'テーブル名をできるだけ省略した形でSQL文を作成
        CmdSqlStr01 = "UPDATE `動的な表$`"
        CmdSqlStr01 = CmdSqlStr01 & " SET 販売員 = 'aytytaaa'"
        CmdSqlStr01 = CmdSqlStr01 & " WHERE 連番 = 1"
 
 
'    '作成したSQL文をSQLコマンド実行環境機能にセット
'    Cmd01.CommandText = CmdSqlStr01
'
'    'SQLの実行
'    Cmd01.Execute
    
    
    Cn.Execute CmdSqlStr01
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'オブジェクト変数の後始末
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
'    Set Cmd01 = Nothing
    
    Cn.Close
    Set Cn = Nothing
    
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'もしSheet1に、
    '「ターゲットファイルの書込み先シート」をソースにした、
    'すべての列と行を表示できるMicrosoftQueryの結果表を作ってあったら、
    'それを更新して、本当に「ターゲットを閉じたまま」書き換わったかをチェックします。
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    Sheet1.ListObjects("テーブル_Excel_Files_からのクエリ").QueryTable.Refresh
        
    '↑↑ここでの ListObjects(Index)の Index部分は、「テーブル機能」のテーブル名。
    'インデックス番号でも可。
    'xlsの場合と違ってxlsm・xlsxの場合は、
    'MicrosoftQueryは「テーブル機能」のテーブルが
    'コンテナ（ガワ）のようになっていて、その中に結果表が埋め込まれている感じ
    'になっているので、ListObjects(Index)の指定が必要。
    'xlsでは「テーブル」機能はないので、ListObjects(Index)も無く、
    'Sheet1.QueryTables(1).Refresh とか、
    'WorkSheets(1).QueryTables(1).Refresh みたいな感じになると思います。
    '未検証なので、ご自分でも確認してみてください。

    
'    Application.ScreenUpdating = True         'Excelの画面描画を再開。画面の変化が普通に表示されるようにする。
   
    
 End Sub








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

 '新規コマンド環境を作って、いったん Connectionオブジェクトと関連付け（紐付け）させ、
 'その新規コマンド環境のExecuteメソッドでSQLを実行する方法です。

 'ヘルプより
 '注意   Command オブジェクトを使わずにクエリを実行するには、
 'クエリ文字列を Connection オブジェクトの Execute メソッド、
 'または Recordset オブジェクトの Open メソッドに渡します。
 'ただし、コマンド テキストを永続しておいて、再実行させる場合、
 'またはクエリ パラメータを使う場合は、Command オブジェクトを
 '使用する必要があります。
 
 'というわけで・・・
 'ここではパラメータクエリも使わないので、本当は
 'Connection オブジェクトの Execute メソッドを直接使って
 'SQLを実行すればよいのですが、あえて、Commandオブジェクトを使ってみます。

 '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:\b\tes1.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 `動的な表$`"
'    CmdSqlStr01 = CmdSqlStr01 & " SET `動的な表$`.販売員 = 'pppヴｊｙｖ'"
'    CmdSqlStr01 = CmdSqlStr01 & " WHERE `動的な表$`.連番 = 1"
    
        'テーブル名をできるだけ省略した形でSQL文を作成
        CmdSqlStr01 = "UPDATE `動的な表$`"
        CmdSqlStr01 = CmdSqlStr01 & " SET 販売員 = 'aaaa'"
        CmdSqlStr01 = CmdSqlStr01 & " WHERE 連番 = 1"
 
 
    '作成したSQL文をSQLコマンド実行環境機能にセット
    Cmd01.CommandText = CmdSqlStr01
    
    'SQLの実行
    Cmd01.Execute
    
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'オブジェクト変数の後始末
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    Set Cmd01 = Nothing
    
    Cn.Close
    Set Cn = Nothing
    
    
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    'もしSheet1に、
    '「ターゲットファイルの書込み先シート」をソースにした、
    'すべての列と行を表示できるMicrosoftQueryの結果表を作ってあったら、
    'それを更新して、本当に「ターゲットを閉じたまま」書き換わったかをチェックします。
    '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
    
    Sheet1.ListObjects("テーブル_Excel_Files_からのクエリ").QueryTable.Refresh
        
    '↑↑ここでの ListObjects(Index)の Index部分は、「テーブル機能」のテーブル名。
    'インデックス番号でも可。
    'xlsの場合と違ってxlsm・xlsxの場合は、
    'MicrosoftQueryは「テーブル機能」のテーブルが
    'コンテナ（ガワ）のようになっていて、その中に結果表が埋め込まれている感じ
    'になっているので、ListObjects(Index)の指定が必要。
    'xlsでは「テーブル」機能はないので、ListObjects(Index)も無く、
    'Sheet1.QueryTables(1).Refresh とか、
    'WorkSheets(1).QueryTables(1).Refresh みたいな感じになると思います。
    '未検証なので、ご自分でも確認してみてください。

    
'    Application.ScreenUpdating = True         'Excelの画面描画を再開。画面の変化が普通に表示されるようにする。
   
    
 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

Option Explicit

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:\b\tes1.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 `動的な表$`"

' CmdSqlStr01 = CmdSqlStr01 & " SET `動的な表$`.販売員 = 'pppヴｊｙｖ'"

' CmdSqlStr01 = CmdSqlStr01 & " WHERE `動的な表$`.連番 = 1"

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

CmdSqlStr01 = "UPDATE `動的な表$`"

CmdSqlStr01 = CmdSqlStr01 & " SET 販売員 = 'aytytaaa'"

CmdSqlStr01 = CmdSqlStr01 & " WHERE 連番 = 1"

' '作成したSQL文をSQLコマンド実行環境機能にセット

' Cmd01.CommandText = CmdSqlStr01

' 'SQLの実行

' Cmd01.Execute

Cn.Execute CmdSqlStr01

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

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

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

' Set Cmd01 = Nothing

Cn.Close

Set Cn = Nothing

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

'もしSheet1に、

'「ターゲットファイルの書込み先シート」をソースにした、

'すべての列と行を表示できるMicrosoftQueryの結果表を作ってあったら、

'それを更新して、本当に「ターゲットを閉じたまま」書き換わったかをチェックします。

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

Sheet1.ListObjects("テーブル_Excel_Files_からのクエリ").QueryTable.Refresh

'↑↑ここでの ListObjects(Index)の Index部分は、「テーブル機能」のテーブル名。

'インデックス番号でも可。

'xlsの場合と違ってxlsm・xlsxの場合は、

'MicrosoftQueryは「テーブル機能」のテーブルが

'コンテナ（ガワ）のようになっていて、その中に結果表が埋め込まれている感じ

'になっているので、ListObjects(Index)の指定が必要。

'xlsでは「テーブル」機能はないので、ListObjects(Index)も無く、

'Sheet1.QueryTables(1).Refresh とか、

'WorkSheets(1).QueryTables(1).Refresh みたいな感じになると思います。

'未検証なので、ご自分でも確認してみてください。

' Application.ScreenUpdating = True 'Excelの画面描画を再開。画面の変化が普通に表示されるようにする。

End Sub

Sub UpdateByADO_ACE01()

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

'新規コマンド環境を作って、いったん Connectionオブジェクトと関連付け（紐付け）させ、

'その新規コマンド環境のExecuteメソッドでSQLを実行する方法です。

'ヘルプより

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

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

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

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

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

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

'というわけで・・・

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

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

'SQLを実行すればよいのですが、あえて、Commandオブジェクトを使ってみます。

'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:\b\tes1.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 `動的な表$`"

' CmdSqlStr01 = CmdSqlStr01 & " SET `動的な表$`.販売員 = 'pppヴｊｙｖ'"

' CmdSqlStr01 = CmdSqlStr01 & " WHERE `動的な表$`.連番 = 1"

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

CmdSqlStr01 = "UPDATE `動的な表$`"

CmdSqlStr01 = CmdSqlStr01 & " SET 販売員 = 'aaaa'"

CmdSqlStr01 = CmdSqlStr01 & " WHERE 連番 = 1"

'作成したSQL文をSQLコマンド実行環境機能にセット

Cmd01.CommandText = CmdSqlStr01

'SQLの実行

Cmd01.Execute

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

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

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

Set Cmd01 = Nothing

Cn.Close

Set Cn = Nothing

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

'もしSheet1に、

'「ターゲットファイルの書込み先シート」をソースにした、

'すべての列と行を表示できるMicrosoftQueryの結果表を作ってあったら、

'それを更新して、本当に「ターゲットを閉じたまま」書き換わったかをチェックします。

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

Sheet1.ListObjects("テーブル_Excel_Files_からのクエリ").QueryTable.Refresh

'↑↑ここでの ListObjects(Index)の Index部分は、「テーブル機能」のテーブル名。

'インデックス番号でも可。

'xlsの場合と違ってxlsm・xlsxの場合は、

'MicrosoftQueryは「テーブル機能」のテーブルが

'コンテナ（ガワ）のようになっていて、その中に結果表が埋め込まれている感じ

'になっているので、ListObjects(Index)の指定が必要。

'xlsでは「テーブル」機能はないので、ListObjects(Index)も無く、

'Sheet1.QueryTables(1).Refresh とか、

'WorkSheets(1).QueryTables(1).Refresh みたいな感じになると思います。

'未検証なので、ご自分でも確認してみてください。

' Application.ScreenUpdating = True 'Excelの画面描画を再開。画面の変化が普通に表示されるようにする。

End Sub

投稿タグ: AccessVBA, Accessの独学, ExcelVBA, Excelの独学, Excel連携VBA, パソコンでの自動化, 自動化

MENU

関連記事

最近の投稿

カテゴリー

MENU

Excel2010で、『 開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ 』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）

関連記事

最近の投稿

カテゴリー

タグ

Excel2010で、『開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）