Access2000VBA・Excel2000VBA独学～SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」～Microsoft Query（QueryTableオブジェクト）を利用する場合～

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の場合はなんと「削除」ができないので対応策が必要です。
対応策はこちら。『できないこと』。（←その他の「できないこと」も書いてあります。なお、「あえて削除しない」ことも結構あります。不正対策や整合性維持のためです。基本、削除フラグを立てるだけでも対応できます。もちろん、読み込みだけに使っても構いません。）
Excel2010で、『開かれていない閉じたままのブック・Excel（xlsやxlsx・xlsm）ファイルのデータ』を読み込むだけでなく、書き込む方法の一覧表（DAOやADOにて）
『～SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」～Microsoft Query（QueryTableオブジェクト）を利用する場合～』
『ADO：Excel2010で、開かれていない閉じたままのブック・Excel（xls）データをできるだけ速く読み込む方法ーその２（ADOにて）』
『Access2000VBA・Excel2000VBA独学～別の閉じたExcelファイルを ”閉じたまま” 読み込みや書き込をする方法～５つ』
　　
　　

　　
　　
★ はじめに

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」に書き換えてから実行してください。）

'
'
Sub test300()

    
    'もしSheet2にMicrosoftQueryの結果の表（＝QueryTablesオブジェクト）が
    '無かったら、作って表示させる処理。
    '
    
    If (Worksheets("Sheet2").QueryTables.Count <= 0) Then
        
        'もしSheet2にMicrosoftQueryの結果の表が
        '無かったら以下の処理
        
        '以下の内容でSheet2にMicrosoftQueryの結果の表を作成。
        'フルパスは恐らくUNCパス、つまり、他のPCの共有フォルダのxlsでも
        '良いのではないかと思います。（ダメだったらすみません！）

        With Worksheets("Sheet2").QueryTables.Add( _
            Connection:="ODBC;DSN=Excel Files;DBQ=D:\test88\test01.xls;DefaultDir=D:\test88;DriverId=790;MaxBufferSize=2048;PageTimeout=5;", _
            Destination:=Worksheets("Sheet2").Range("B2"))
                
                'ここで書き換えるのは基本、
                '「DBQ=・・・」のフルパスと、
                '「DefaultDir」のフォルダパスと、
                '「Destination・・・」のシート名や表の起点のセル（Rangeのところ）
                'あと、次段階の「.CommandText = AArray（・・・）」の行のそのSQLの内容
                'でOKかと思います。
                '
                '今回のこの例でのSQL文では、Sheet2に対して、
                '「B2」セルを起点としてMicrosoftQueryの結果の表を作成します。
                '
                'なお、「Worksheets("Sheet2").」を「Activesheet.」に書き換えると、
                '現在表示しているシートに結果の表が生成されます。
                '
                'また、SQL文（表操作の命令文）の中では、
                'test01.xlsのSheet1（＝システムテーブル）のことを「`Sheet1$`」と
                '表現しているのですが、これは「システムテーブルと認めさせるために、
                'シート名に「$」を末尾に付けている・・・」ということになります。
                'また、Microsoft QueryのSQLの仕様として、そのテーブル名を
                '「`」バッククォートで囲んでいます。
                'バッククォートはShiftキーを押しながら「@」を押します。
                
                
                .CommandText = Array("SELECT * FROM `Sheet1$`")     'QueryTablesオブジェクトのSQLを書き換え
                .Refresh                                            '書き換えたSQLを実行して表に反映
                
                
        End With
        
    Else
    
        'もしすでに、Sheet2にMicrosoftQueryの結果の表が
        'あったら何もしないで次へ。

    End If
    
    
'    'もし、作ったMicrosoft Queryの結果の表が表示されなかったら
'    '以下の2行のコメントアウトをはずしてテストしてみます。
'    Worksheets("Sheet2").QueryTables(1).CommandText = Array("SELECT * FROM `Sheet1$`")      'SQLの再指定
'    Worksheets("Sheet2").QueryTables(1).Refresh BackgroundQuery:=False          'SQLの実行と表示
    
    
    
End Sub
'
'

Sub test300()

'もしSheet2にMicrosoftQueryの結果の表（＝QueryTablesオブジェクト）が

'無かったら、作って表示させる処理。

If (Worksheets("Sheet2").QueryTables.Count <= 0) Then

'もしSheet2にMicrosoftQueryの結果の表が

'無かったら以下の処理

'以下の内容でSheet2にMicrosoftQueryの結果の表を作成。

'フルパスは恐らくUNCパス、つまり、他のPCの共有フォルダのxlsでも

'良いのではないかと思います。（ダメだったらすみません！）

With Worksheets("Sheet2").QueryTables.Add( _

Connection:="ODBC;DSN=Excel Files;DBQ=D:\test88\test01.xls;DefaultDir=D:\test88;DriverId=790;MaxBufferSize=2048;PageTimeout=5;", _

Destination:=Worksheets("Sheet2").Range("B2"))

'ここで書き換えるのは基本、

'「DBQ=・・・」のフルパスと、

'「DefaultDir」のフォルダパスと、

'「Destination・・・」のシート名や表の起点のセル（Rangeのところ）

'あと、次段階の「.CommandText = AArray（・・・）」の行のそのSQLの内容

'でOKかと思います。

'今回のこの例でのSQL文では、Sheet2に対して、

'「B2」セルを起点としてMicrosoftQueryの結果の表を作成します。

'なお、「Worksheets("Sheet2").」を「Activesheet.」に書き換えると、

'現在表示しているシートに結果の表が生成されます。

'また、SQL文（表操作の命令文）の中では、

'test01.xlsのSheet1（＝システムテーブル）のことを「`Sheet1$`」と

'表現しているのですが、これは「システムテーブルと認めさせるために、

'シート名に「$」を末尾に付けている・・・」ということになります。

'また、Microsoft QueryのSQLの仕様として、そのテーブル名を

'「`」バッククォートで囲んでいます。

'バッククォートはShiftキーを押しながら「@」を押します。

.CommandText = Array("SELECT * FROM `Sheet1$`") 'QueryTablesオブジェクトのSQLを書き換え

.Refresh '書き換えたSQLを実行して表に反映

End With

Else

'もしすでに、Sheet2にMicrosoftQueryの結果の表が

'あったら何もしないで次へ。

End If

' 'もし、作ったMicrosoft Queryの結果の表が表示されなかったら

' '以下の2行のコメントアウトをはずしてテストしてみます。

' Worksheets("Sheet2").QueryTables(1).CommandText = Array("SELECT * FROM `Sheet1$`") 'SQLの再指定

' Worksheets("Sheet2").QueryTables(1).Refresh BackgroundQuery:=False 'SQLの実行と表示

End Sub

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

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

'
'
Option Explicit


'#####################################################################################
'SQLのUPDATE句を使って、閉じたxlsファイルを「閉じたまま」書き換えるプロシージャです。
'ここではすでに作成された、「`D:\test88\test01.xls`」のデータが書き換えられます。
'「`D:\test88\test01.xls`」に対する、Microsorf Queryの結果表が、
'事前にシートに表示されていることが前提です。
'なお、
'おそらくですが、UNCパス（すなわち他のPCのxlsファイル）も使える気がします。
'
'同時に、DoEventsを使って、
'「バックグラウンドでデータが更新中であるため、この操作は行えません」
'というエラーを回避しています。
'
'#####################################################################################

Sub test98()
    
    Dim sqlstr01 As String      'SQL文（＝セルデータの書き換えや抽出に使う命令文）を
                                '                        格納するための変数の宣言。1つめ
    Dim sqlstr02 As String      '同上。2つめ
    Dim sqlstr03 As String      '同上。3つめ
    Dim sqlstr04 As String      '同上。4つめ
    Dim sqlstr05 As String      '同上。5つめ
    
    Dim SQLArrey As Variant     '上記の5つのSQL文を一度に格納するための変数。
                                '（Variant型ですが「配列」変数として利用します。）
                                '
                                'なぜ「配列」変数を使うかというと、
                                'Microsoft Queryの命令文を格納するための
                                '「CommandText」プロパティ自体が、
                                'Variant型の配列だからです。
                                '（とヘルプに書いてあります。）
                                '
                                '操作側ファイルがExcel2000の場合は、
                                'SQL文の「句ごと」に配列に格納します。
                                '（配列の各要素ごとに、何文字までかは調べていません。）
                                '
                                '操作側ファイルがExcel2010の場合は、
                                'SQL文の「句ごと」に配列に格納しなくても大丈夫です。
                                '（こちらも配列の各要素ごとに、何文字までかは調べていませんが
                                '   300文字前後で限界がくるようです。仕様はちょっとわかりません。）

    '現在のMicrosoftQueryのSQL文をイミディエイトウィンドウに表示します。
    '操作側が2000の場合は句ごとに複数行、
    '操作側が2010の場合はひとつひとつの要素に収まるだけのできるだけ1行で表示されます。
    
    Debug.Print Worksheets("Sheet1").QueryTables(1).CommandText
    

    'SQL文（＝セルデータの書き換えや抽出に使う命令文）の作成
    '下記のようにUPDATE句を用いた「書き換えのための命令」や、
    'SELECTを用いた「抽出のための命令」などを作れます。
    '配列の中身が空文字でもエラーになりません。
        
    sqlstr01 = "UPDATE `D:\test88\test01.xls`.`Sheet1$` "
    sqlstr02 = "SET `列a` = 'qag' "
    sqlstr03 = "WHERE `列b` = 2;"
    sqlstr04 = ""
    sqlstr05 = ""
    
            'ただし、「各句ごと」の区切りには必ず半角スペースを入れます。
            '一般的にSQL文は、句ごとに半角スペースで区切らないと
            'エラーになってしまうためです。
            '
            'ここの例だと、SQL分を句ごとに「" "」で囲んだ時の、後ろの「"」の前に、
            '必ず半角スペースを入れています。
            '
            'なお、Microsoft Queryの場合、最後の「;」を省略しても
            'エラーにならない仕様になっています。
            'なぜそんな仕様なのかはわかりませんが・・・。
            'もちろん入れてもOKです。
            'というか、入れるほうがいいとは思います。
            '
            'また、シート（＝システムテーブル）の名前は、
            '「$」をつけて「`」バッククォートで囲む必要があります。
            'Microsoft QueryのSQLの場合、
            'シート名（＝テーブル名）だけは、
            '「`」バッククォートを省略したらいけないみたいです。
            '反面、シート名以外の・・・例えば列名やファイルパスなどは
            'バッククォートは省略できる場合が多いのですので、
            'どう省略できるか、ご自分でも試してみてください。
            'ここでの例だど、例えば「`列a`」のバッククォートを省略して
            '「列a」と書いてもちゃんとSQLを実行できます。
            '
            '文字列型の列を書き換える場合は、
            '書き換え後の値を「'」シングルクオーテーションで囲みます。
            '数値型の列を書き換える場合、何もつけません。
            'どの列をどのデータ型にするのかは、ODBCドライバか何かが、
            '最初の５、６行のデータを見て、自動的に決めてくれるようです。
            'Microsoft Queryの各列のデータ型は、一般的なデータベースの「型」のように
            'しっかりと決められます。
            '違う型の列同士でリレーションを組もうとするとエラーになります。
            'これはExcelの「セル書式設定」とはまったくの無関係です。
            '「見た目」「表示」ではなくて、しっかりと内部的にデータ型が区別されます。
            '
            '「UPDATE `D:\test88\test01.xls`.`Sheet1$`」の部分は
            '「UPDATE `Sheet1$` 」とファイルパスを省略してもOKです。
            '理由は、恐らくですが、すでにMicrosoft Queryの機能によって、
            'シートに「`D:\test88\test01.xls`」のクエリの結果表が
            'できてしまっているからではないか？と推察されます。
            '他方、パスが指定できるという意味では「UNCパス」も使えるかもです。
            'ただ、シートにすでに、他のPCのxlsのクエリの結果表が表示されていれば
            'その場合もおそらく、UNCパスの省略はOKだと思います。
            

    
    
    '作成したSQL文をVariant型の配列変数に代入します。
    
    SQLArrey = Array(sqlstr01, sqlstr02, sqlstr03, sqlstr04, sqlstr05)
    
    
    'SQL文が代入された配列変数を、Microsoft QueryのCommandTextプロパティに代入します。
    'つまり、Microsoft Queryの結果表のSQL内容を書き換えます。
    '（ただし、SQL文がこの時点で上書きされ、保存まで、されるのかは未チェックですので
    ' もしよければご自分でもチェックしてみてください。）
    
    Worksheets("Sheet1").QueryTables(1).CommandText = SQLArrey
    
    
    'SQL文を実行します。
    'ここではSQL文に「UPDATE句」を用いましたので、
    'ファイルの内容を「閉じたまま」、書き換えたことになります。
    
    Worksheets("Sheet1").QueryTables(1).Refresh
    
    
    'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避
    '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html
    Do While Worksheets("Sheet1").QueryTables(1).Refreshing
        DoEvents
    Loop
    
    '↑このコードが終わると、実行結果（ここでは書き換え結果）が
    'Excelのシートに反映されます。




    '他のプロシージャにて、
    '本プロシージャと「Test99()」プロシージャを呼び出すことに作り変えたので
    'ここではこのコードはコメントアウトします。
    
    'Call Test99        ’「Test99」プロシージャの実行。
    
    

End Sub

'#####################################################################################
'SQLのSELECT句を使って、閉じたxlsファイルから「閉じたまま」データを抽出するプロシージャです。
'ここではすでに作成された、「`D:\test88\test01.xls`」のデータが抽出されます。
'「`D:\test88\test01.xls`」に対する、Microsorf Queryの結果表が、
'事前にシートに表示されていることが前提です。
'
'なお、
'おそらくですが、UNCパス（すなわち他のPCのxlsファイル）も使える気がします。
'
'同時に、DoEventsを使って、
'「バックグラウンドでデータが更新中であるため、この操作は行えません」
'というエラーを回避しています。
'
'#####################################################################################

Sub test99()

    Dim sqlstr01 As String      'SQL文（＝セルデータの書き換えや抽出に使う命令文）を
                                '                        格納するための変数の宣言。1つめ
    Dim sqlstr02 As String      '同上。2つめ
    Dim sqlstr03 As String      '同上。3つめ
    Dim sqlstr04 As String      '同上。4つめ
    Dim sqlstr05 As String      '同上。5つめ
    
    Dim SQLArrey As Variant     '上記の5つのSQL文を一度に格納するための変数。
                                '（Variant型ですが「配列」変数として利用します。）
                                
    
    '現在のMicrosoftQueryのSQL文をイミディエイトウィンドウに表示します。
    '操作側が2000の場合は句ごとに複数行、
    '操作側が2010の場合はひとつひとつの要素に収まるだけのできるだけ1行で表示されます。
    
    Debug.Print Worksheets("Sheet1").QueryTables(1).CommandText
    
    
    
    'SQL文（＝セルデータの書き換えや抽出に使う命令文）の作成
    
    sqlstr01 = "SELECT `Sheet1$`.列a, `Sheet1$`.列b, `Sheet1$`.列c "
    sqlstr02 = "FROM `D:\test88\test01.xls`.`Sheet1$` `Sheet1$`;"
    sqlstr03 = ""
    sqlstr04 = ""
    sqlstr05 = ""
    
            '「SELECT `Sheet1$`.列a, `Sheet1$`.列b, `Sheet1$`.列c 」の部分は
            '「SELECT 列a, 列b, 列c 」と、普通のSQLのように書いてもOKです。
            '
            '「FROM `D:\test88\test01.xls`.`Sheet1$` `Sheet1$`;」の部分は
            '「FROM `Sheet1$`;」と省略しても動きます。
            '多分、すでにMicrosorf Queryによって、パスが通っている感じだから
            'だと思います。
            'いずれにしましても、MicrosoftQueryのSQLは少し変わった仕様になっています。
            '
            '
            'シート（＝システムテーブル）の名前は、
            '「$」をつけて「`」バッククォートで囲む必要があります。
            'シート名以外のバッククォートは省略できる場合が多いのですので、
            'どう省略できるか、ご自分でも試してみてください。
            
    
           
    
    '作成したSQL文をVariant型の配列変数に代入します。
    
    SQLArrey = Array(sqlstr01, sqlstr02, sqlstr03, sqlstr04, sqlstr05)
    
    
    
    'Microsoft Queryの結果表のSQL内容を書き換えます。
    
    Worksheets("Sheet1").QueryTables(1).CommandText = SQLArrey
    
        
    
    'SQL文を実行します。

    Worksheets("Sheet1").QueryTables(1).Refresh
    
    
    
    '今回、本プロシージャは「test98」プロシージャの実行直後に呼び出し＋実行がなされますが
    'DoEventのループでエラー回避処理を行うと無限ループに陥ってしまうのでやりません。
    'コメントアウトします。
'
'    'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避
'    '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html
'    Do While Worksheets("Sheet1").QueryTables(1).Refreshing
'        DoEvents
'    Loop

        '無限ループに陥る理由は調べていません。
        'なのでご自分でも試して、調べてみてください。
        '複数のSQLを一度に実行すると、最後のSQLの実行時に無限ループになるのか
        '位置は関係なく、無限ループになるのかはわかりません。
        '前者ならラクですが、でも後者でも事前にテストすれば
        'どのSQL実行でDoEventsをやめればいいかはわかるので、
        '大丈夫ではないかと思います。


End Sub

'#####################################################################################
'test98()プロシージャを呼び出して、「`D:\test88\test01.xls`」のデータを書き換え
'test99()プロシージャを呼び出して、その書き換わった状態をシートに再表示します。
'
'#####################################################################################


Sub test11()

    Call test98
    Call test99

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

Option Explicit

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

'SQLのUPDATE句を使って、閉じたxlsファイルを「閉じたまま」書き換えるプロシージャです。

'ここではすでに作成された、「`D:\test88\test01.xls`」のデータが書き換えられます。

'「`D:\test88\test01.xls`」に対する、Microsorf Queryの結果表が、

'事前にシートに表示されていることが前提です。

'なお、

'おそらくですが、UNCパス（すなわち他のPCのxlsファイル）も使える気がします。

'同時に、DoEventsを使って、

'「バックグラウンドでデータが更新中であるため、この操作は行えません」

'というエラーを回避しています。

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

Sub test98()

Dim sqlstr01 As String 'SQL文（＝セルデータの書き換えや抽出に使う命令文）を

' 格納するための変数の宣言。1つめ

Dim sqlstr02 As String '同上。2つめ

Dim sqlstr03 As String '同上。3つめ

Dim sqlstr04 As String '同上。4つめ

Dim sqlstr05 As String '同上。5つめ

Dim SQLArrey As Variant '上記の5つのSQL文を一度に格納するための変数。

'（Variant型ですが「配列」変数として利用します。）

'なぜ「配列」変数を使うかというと、

'Microsoft Queryの命令文を格納するための

'「CommandText」プロパティ自体が、

'Variant型の配列だからです。

'（とヘルプに書いてあります。）

'操作側ファイルがExcel2000の場合は、

'SQL文の「句ごと」に配列に格納します。

'（配列の各要素ごとに、何文字までかは調べていません。）

'操作側ファイルがExcel2010の場合は、

'SQL文の「句ごと」に配列に格納しなくても大丈夫です。

'（こちらも配列の各要素ごとに、何文字までかは調べていませんが

' 300文字前後で限界がくるようです。仕様はちょっとわかりません。）

'現在のMicrosoftQueryのSQL文をイミディエイトウィンドウに表示します。

'操作側が2000の場合は句ごとに複数行、

'操作側が2010の場合はひとつひとつの要素に収まるだけのできるだけ1行で表示されます。

Debug.Print Worksheets("Sheet1").QueryTables(1).CommandText

'SQL文（＝セルデータの書き換えや抽出に使う命令文）の作成

'下記のようにUPDATE句を用いた「書き換えのための命令」や、

'SELECTを用いた「抽出のための命令」などを作れます。

'配列の中身が空文字でもエラーになりません。

sqlstr01 = "UPDATE `D:\test88\test01.xls`.`Sheet1$` "

sqlstr02 = "SET `列a` = 'qag' "

sqlstr03 = "WHERE `列b` = 2;"

sqlstr04 = ""

sqlstr05 = ""

'ただし、「各句ごと」の区切りには必ず半角スペースを入れます。

'一般的にSQL文は、句ごとに半角スペースで区切らないと

'エラーになってしまうためです。

'ここの例だと、SQL分を句ごとに「" "」で囲んだ時の、後ろの「"」の前に、

'必ず半角スペースを入れています。

'なお、Microsoft Queryの場合、最後の「;」を省略しても

'エラーにならない仕様になっています。

'なぜそんな仕様なのかはわかりませんが・・・。

'もちろん入れてもOKです。

'というか、入れるほうがいいとは思います。

'また、シート（＝システムテーブル）の名前は、

'「$」をつけて「`」バッククォートで囲む必要があります。

'Microsoft QueryのSQLの場合、

'シート名（＝テーブル名）だけは、

'「`」バッククォートを省略したらいけないみたいです。

'反面、シート名以外の・・・例えば列名やファイルパスなどは

'バッククォートは省略できる場合が多いのですので、

'どう省略できるか、ご自分でも試してみてください。

'ここでの例だど、例えば「`列a`」のバッククォートを省略して

'「列a」と書いてもちゃんとSQLを実行できます。

'文字列型の列を書き換える場合は、

'書き換え後の値を「'」シングルクオーテーションで囲みます。

'数値型の列を書き換える場合、何もつけません。

'どの列をどのデータ型にするのかは、ODBCドライバか何かが、

'最初の５、６行のデータを見て、自動的に決めてくれるようです。

'Microsoft Queryの各列のデータ型は、一般的なデータベースの「型」のように

'しっかりと決められます。

'違う型の列同士でリレーションを組もうとするとエラーになります。

'これはExcelの「セル書式設定」とはまったくの無関係です。

'「見た目」「表示」ではなくて、しっかりと内部的にデータ型が区別されます。

'「UPDATE `D:\test88\test01.xls`.`Sheet1$`」の部分は

'「UPDATE `Sheet1$` 」とファイルパスを省略してもOKです。

'理由は、恐らくですが、すでにMicrosoft Queryの機能によって、

'シートに「`D:\test88\test01.xls`」のクエリの結果表が

'できてしまっているからではないか？と推察されます。

'他方、パスが指定できるという意味では「UNCパス」も使えるかもです。

'ただ、シートにすでに、他のPCのxlsのクエリの結果表が表示されていれば

'その場合もおそらく、UNCパスの省略はOKだと思います。

'作成したSQL文をVariant型の配列変数に代入します。

SQLArrey = Array(sqlstr01, sqlstr02, sqlstr03, sqlstr04, sqlstr05)

'SQL文が代入された配列変数を、Microsoft QueryのCommandTextプロパティに代入します。

'つまり、Microsoft Queryの結果表のSQL内容を書き換えます。

'（ただし、SQL文がこの時点で上書きされ、保存まで、されるのかは未チェックですので

' もしよければご自分でもチェックしてみてください。）

Worksheets("Sheet1").QueryTables(1).CommandText = SQLArrey

'SQL文を実行します。

'ここではSQL文に「UPDATE句」を用いましたので、

'ファイルの内容を「閉じたまま」、書き換えたことになります。

Worksheets("Sheet1").QueryTables(1).Refresh

'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避

'参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html

Do While Worksheets("Sheet1").QueryTables(1).Refreshing

DoEvents

Loop

'↑このコードが終わると、実行結果（ここでは書き換え結果）が

'Excelのシートに反映されます。

'他のプロシージャにて、

'本プロシージャと「Test99()」プロシージャを呼び出すことに作り変えたので

'ここではこのコードはコメントアウトします。

'Call Test99 ’「Test99」プロシージャの実行。

End Sub

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

'SQLのSELECT句を使って、閉じたxlsファイルから「閉じたまま」データを抽出するプロシージャです。

'ここではすでに作成された、「`D:\test88\test01.xls`」のデータが抽出されます。

'「`D:\test88\test01.xls`」に対する、Microsorf Queryの結果表が、

'事前にシートに表示されていることが前提です。

'なお、

'おそらくですが、UNCパス（すなわち他のPCのxlsファイル）も使える気がします。

'同時に、DoEventsを使って、

'「バックグラウンドでデータが更新中であるため、この操作は行えません」

'というエラーを回避しています。

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

Sub test99()

Dim sqlstr01 As String 'SQL文（＝セルデータの書き換えや抽出に使う命令文）を

' 格納するための変数の宣言。1つめ

Dim sqlstr02 As String '同上。2つめ

Dim sqlstr03 As String '同上。3つめ

Dim sqlstr04 As String '同上。4つめ

Dim sqlstr05 As String '同上。5つめ

Dim SQLArrey As Variant '上記の5つのSQL文を一度に格納するための変数。

'（Variant型ですが「配列」変数として利用します。）

'現在のMicrosoftQueryのSQL文をイミディエイトウィンドウに表示します。

'操作側が2000の場合は句ごとに複数行、

'操作側が2010の場合はひとつひとつの要素に収まるだけのできるだけ1行で表示されます。

Debug.Print Worksheets("Sheet1").QueryTables(1).CommandText

'SQL文（＝セルデータの書き換えや抽出に使う命令文）の作成

sqlstr01 = "SELECT `Sheet1$`.列a, `Sheet1$`.列b, `Sheet1$`.列c "

sqlstr02 = "FROM `D:\test88\test01.xls`.`Sheet1$` `Sheet1$`;"

sqlstr03 = ""

sqlstr04 = ""

sqlstr05 = ""

'「SELECT `Sheet1$`.列a, `Sheet1$`.列b, `Sheet1$`.列c 」の部分は

'「SELECT 列a, 列b, 列c 」と、普通のSQLのように書いてもOKです。

'「FROM `D:\test88\test01.xls`.`Sheet1$` `Sheet1$`;」の部分は

'「FROM `Sheet1$`;」と省略しても動きます。

'多分、すでにMicrosorf Queryによって、パスが通っている感じだから

'だと思います。

'いずれにしましても、MicrosoftQueryのSQLは少し変わった仕様になっています。

'シート（＝システムテーブル）の名前は、

'「$」をつけて「`」バッククォートで囲む必要があります。

'シート名以外のバッククォートは省略できる場合が多いのですので、

'どう省略できるか、ご自分でも試してみてください。

'作成したSQL文をVariant型の配列変数に代入します。

SQLArrey = Array(sqlstr01, sqlstr02, sqlstr03, sqlstr04, sqlstr05)

'Microsoft Queryの結果表のSQL内容を書き換えます。

Worksheets("Sheet1").QueryTables(1).CommandText = SQLArrey

'SQL文を実行します。

Worksheets("Sheet1").QueryTables(1).Refresh

'今回、本プロシージャは「test98」プロシージャの実行直後に呼び出し＋実行がなされますが

'DoEventのループでエラー回避処理を行うと無限ループに陥ってしまうのでやりません。

'コメントアウトします。

' 'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避

' '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html

' Do While Worksheets("Sheet1").QueryTables(1).Refreshing

' DoEvents

' Loop

'無限ループに陥る理由は調べていません。

'なのでご自分でも試して、調べてみてください。

'複数のSQLを一度に実行すると、最後のSQLの実行時に無限ループになるのか

'位置は関係なく、無限ループになるのかはわかりません。

'前者ならラクですが、でも後者でも事前にテストすれば

'どのSQL実行でDoEventsをやめればいいかはわかるので、

'大丈夫ではないかと思います。

End Sub

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

'test98()プロシージャを呼び出して、「`D:\test88\test01.xls`」のデータを書き換え

'test99()プロシージャを呼び出して、その書き換わった状態をシートに再表示します。

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

Sub test11()

Call test98

Call test99

End Sub

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

'
'
Option Explicit


'#######################################################################################
'SQL文などを受け取って、MicrosoftQuery（クエリ）のSQLを実行するプロシージャです。
'同時に、「バックグラウンドでデータが更新中であるため、この操作は行えません」
'というエラーも回避します。
'
'引数の指定については以下のとおりです。
'
'sqlstr01 ～ sqlstr10 まで：SQL文を「句ごと」に指定します。
'ObjQry01                 ：MicrosoftQueryを表すオブジェクト式を指定します。
'DoevtFlg                 ：エラーを回避するためのDoEventsのループをやるかやらないかのフラグです。
'　　　　　　　　　　　　　　「1」=やる、「2」=やらない、の区分です。
'                             複数のクエリを一度に実行する際、最後のクエリは
'                             DoEventsのループが無限ループになるかもなので
'                             「2」を指定して、「やらない」ほうがいいかもしれません｡
'                             もしくは、それぞれ個別に、やる、やらない、をテストしないと
'                             いけないかもしれません。
'                             その点はご自分でもチェックしてみてください｡
'#######################################################################################

Sub test200(sqlstr01 As String, _
            sqlstr02 As String, _
            sqlstr03 As String, _
            sqlstr04 As String, _
            sqlstr05 As String, _
            sqlstr06 As String, _
            sqlstr07 As String, _
            sqlstr08 As String, _
            sqlstr09 As String, _
            sqlstr10 As String, _
            ObjQry01 As QueryTable, _
            DoevtFlg As Integer)



'    '以下のSQL文用の変数（＝書き換えや抽出の命令文の作成用の変数）は、
'    'その変数宣言を、呼び出し元のプロシージャの中で書くかたちに作り変えたので、
'    'ここではコメントアウトします｡
'    Dim sqlstr01 As String
'    Dim sqlstr02 As String
'    Dim sqlstr03 As String
'    Dim sqlstr04 As String
'    Dim sqlstr05 As String



    'SQL文を代入するための変数を用意（ここでは「配列として使います。）
    
    Dim SQLArrey As Variant
    
    
    
'    'シートやMicrosoftQueryの結果表（オブジェクト）の変数設定や代入は、
'    'このプロシージャの最初の部分でやってしまうので
'    'ここではコメントアウトします。
'    '（呼び出し元から受け取るように作り変えたので）
'    Dim ObjQry01 As QueryTable
'    Set ObjQry01 = Worksheets("Sheet1").QueryTables(1)


    
    '受け取ったSQL文（＝書き換えや抽出の命令文の作成のコード）を
    'イミディエイトウィンドウに表示
    
    Debug.Print ObjQry01.CommandText
    



'    '以下のコード（＝SQL文作成のコード）は、
'    '呼び出し元のプロシージャで書くかたちに作り変えたので
'    'ここではコメントアウトします｡
'    sqlstr01 = "UPDATE `Sheet1$` "
'    sqlstr02 = "SET a = '9fvds' "
'    sqlstr03 = "WHERE b = 2;"
'    sqlstr04 = ""
'    sqlstr05 = ""



    'CommandTextプロパティはVariant型（特に配列）の値なので
    'ここで、Variant型の配列変数に、
    '「句ごと」に SQL文を分けて代入します。
    'ここで Variant型ではなくString型にするとエラーになります。
    
    SQLArrey = Array(sqlstr01, _
                     sqlstr02, _
                     sqlstr03, _
                     sqlstr04, _
                     sqlstr05, _
                     sqlstr06, _
                     sqlstr07, _
                     sqlstr08, _
                     sqlstr09, _
                     sqlstr10)
                     
                     '※重要注意事項
                     'なお、Variant型なのは、xlsmじゃなくてxlsだからなのか、
                     'ピボットテーブルのCommandTextじゃなくて
                     'MicrosoftQueryのそれだからなのか
                     'それともExcelのバージョンの違いなのか、未チェックです。
                     'なので、ご自分でも色々と調べてください。
                     '「エクセルのバージョン」、
                     '「拡張子」、
                     '「ピボット/MicrosoftQuery」
                     '等々の違いによっては、
                     'SQL文をVariant型（特に配列）でなくても、ただのString型でも
                     'OKかもしれません。
                     
                     
                     
    
    'オブジェクトとして受け取った「MicrosoftQueryの結果表」の、
    'SQL文の入れ替えをします。
    
    ObjQry01.CommandText = SQLArrey
    
    
    
    '入れ替えたSQL文をそのまま実行します。
    'MicrosoftQueryの場合は、Excecuteなどではなく、
    '「Refresh」が「実行と同じ意味」らしいです。
    
    ObjQry01.Refresh
    
            'ただ、「SQL文の上書き保存」の意味があるのかまでは未チェックです。
            'もしかしたら1つ前のコードの
            '「ObjQry01.CommandText = SQLArrey」で
            '上書き保存しているのかもしれません。
            'なので、ご自分でも調べてみてください。
            
    
    
'    '以下のコードは「QryRefDoEvt01()」プロシージャでやる形に作りかえたので
'    'ここではコメントアウトします。
'    'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避
'    '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html
'    Do While Worksheets("Sheet1").QueryTables(1).Refreshing
'        DoEvents
'    Loop



    '「バックグラウンドでデータが更新中であるため、この操作は行えません」
    'というエラーを回避します。
    'ただし、クエリによってはCall先のLoopのプログラムが
    '無限ループになってしまうので、
    '1=ループする：2=ループ処理自体をしない、
    'ということで進めます。
    
    If DoevtFlg = 1 Then
        Call QryRefDoEvt01(ObjQry01)
    ElseIf DoevtFlg = 2 Then
    
    Else
    
    End If


End Sub


'##############################################################
'クエリの更新（Refresh）に処理が移った際に、
'「バックグラウンドでデータが更新中であるため、
'                                   この操作は行えません」
'・・・というエラーが出る場合の回避をするプロシージャです。
'##############################################################

Sub QryRefDoEvt01(ObjMsqTbl As QueryTable)

    Do While ObjMsqTbl.Refreshing
        DoEvents
    Loop
    
End Sub


'######################################################################################
'すでに作成してある「D:\test88\test01.xls」のMicrosoftQueryの結果表に対する
'SQL文を生成して、それを「test200」プロシージャに送ってSQL文を実行するプロシージャです。
'ただし、列の入れ替えなどをしたときに、日付データのセルの書式が数値などになったままだと、
'日付がシリアル値に変わって表示されてしまうことがあります。
'ですので、
'結果表のセル範囲の書式をすべて「標準」に直す、などの処理を追加したほうがいいかもしれません。
'「列を減らしたり、入れ替えることは絶対にない、増減は行のみ」というような場合は
'このままでもある程度使えるかもしれません。
'列幅が自動調整されるのも、もしいやだったらそのようにコードを書くか、
'MicrosoftQueryの結果表を右クリックしてプロパティにて「列の幅を調整する」のチェックを
'はずします。
'######################################################################################

Sub test201()

    '一応 配列にしなくても動くのですが、
    '変数の数が多くて のちのちの改変がめんどっちくなりそうなので
    '配列を宣言しています。
    
    Dim StrSql01(9) As String



    '1つめのSQLの生成。（表の内容を閉じたまま書き換えするための命令文の作成。）
    'SQL文の「句ごと」に配列に代入しますが、
    '各句ごとに最後に半角スペースを入れることを忘れないようにします。
    'もともとSQL文は、各句ごとの境目は半角スペースで区切らないと
    'エラーになってしまうためです。
    'シート名（というかシステムテーブル名）は、バッククォートで囲む必要があります。
    
    StrSql01(0) = "UPDATE `Sheet1$` "
    StrSql01(1) = "SET 列a = 'あf' "
    StrSql01(2) = "WHERE 列b = 2;"
    StrSql01(3) = ""
    StrSql01(4) = ""



    '1つめのSQLの実行。
    
    Call test200(StrSql01(0), _
                 StrSql01(1), _
                 StrSql01(2), _
                 StrSql01(3), _
                 StrSql01(4), _
                 "", _
                 "", _
                 "", _
                 "", _
                 "", _
                 Worksheets("Sheet1").QueryTables(1), _
                 1)
            
                '※↑の注意事項
                '「Worksheets("Sheet1").QueryTables(1)」の部分は
                '「Worksheets("Sheet1").QueryTables(1).name」で名前を調べて
                '「Worksheets("Sheet1").QueryTables("Excel Files からのクエリ_3")」
                'といったようなオブジェクト式の書き方をしてもOKです。
                '「Worksheets("Sheet1").」を「Activesheet.」と書きかえれば
                'Sheet1だけでなく、どのシートに作られたMicrosoftQueryの結果の表にも
                '使えます。
                    
                    
                    
    '2つめのSQLの生成。（書き換えた内容を再表示するための命令文の作成。）
    'シート名（というかシステムテーブル名）は、バッククォートで囲む必要があります。

    StrSql01(5) = "SELECT `Sheet1$`.列a, `Sheet1$`.列b, `Sheet1$`.列c "
    StrSql01(6) = "FROM `D:\test88\test01.xls`.`Sheet1$` `Sheet1$`"
    StrSql01(7) = ""
    StrSql01(8) = ""
    StrSql01(9) = ""
    
        '    'SQL文は、以下のようにSELECT句でテーブル名を省いたり、
        '    '部分的にバッククォートで囲むことを省いても大丈夫です。
        '    '操作対象のExcelファイルの拡張子を省略してもダイジョブな場合もあります。
        '    'どう省略しても大丈夫か？はご自分でもチェックしてみてください。
        '    StrSql01(5) = "SELECT a, b, c "
        '    StrSql01(6) = "FROM `Sheet1$`"

        '    'また、ここでは一応、StrSql01(5)～(9)に代入しましたが、長いSQL文の場合は
        '    'いったん、(0)～(9)までをクリアしてからまた、(0)～(9)を使ってもいいかもです。

    
    '2つめのSQLの実行。
    
    Call test200(StrSql01(5), _
                 StrSql01(6), _
                 StrSql01(7), _
                 StrSql01(8), _
                 StrSql01(9), _
                 "", _
                 "", _
                 "", _
                 "", _
                 "", _
                 Worksheets("Sheet1").QueryTables(1), _
                 2)
            
            
            
End Sub




'##############################################################
'テスト用のプロシージャです。今回は特には使いません。
'##############################################################

Sub test100()
    
    Dim sqlstr01 As String
    Dim sqlstr02 As String
    Dim sqlstr03 As String
    Dim sqlstr04 As String
    Dim sqlstr05 As String
    Dim SQLArrey As Variant
    
    Dim ObjQry01 As QueryTable
    
    Set ObjQry01 = Worksheets("Sheet1").QueryTables(1)
    
    Debug.Print ObjQry01.CommandText
    
    
    sqlstr01 = "UPDATE `Sheet1$` "
    sqlstr02 = "SET a = '9fvds' "
    sqlstr03 = "WHERE b = 2;"
    sqlstr04 = ""
    sqlstr05 = ""
    
    SQLArrey = Array(sqlstr01, sqlstr02, sqlstr03, sqlstr04, sqlstr05)
    
    ObjQry01.CommandText = SQLArrey
    ObjQry01.Refresh
    
    
'    'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避
'    '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html
'    Do While Worksheets("Sheet1").QueryTables(1).Refreshing
'        DoEvents
'    Loop

    Call QryRefDoEvt01(ObjQry01)

    'Call Test99

End Sub

'##############################################################
'テスト用のプロシージャです。今回は特には使いません。
'##############################################################
Sub test12()

    Call test100
    Call test99

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

Option Explicit

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

'SQL文などを受け取って、MicrosoftQuery（クエリ）のSQLを実行するプロシージャです。

'同時に、「バックグラウンドでデータが更新中であるため、この操作は行えません」

'というエラーも回避します。

'引数の指定については以下のとおりです。

'sqlstr01 ～ sqlstr10 まで：SQL文を「句ごと」に指定します。

'ObjQry01 ：MicrosoftQueryを表すオブジェクト式を指定します。

'DoevtFlg ：エラーを回避するためのDoEventsのループをやるかやらないかのフラグです。

'　　　　　　　　　　　　　　「1」=やる、「2」=やらない、の区分です。

' 複数のクエリを一度に実行する際、最後のクエリは

' DoEventsのループが無限ループになるかもなので

' 「2」を指定して、「やらない」ほうがいいかもしれません｡

' もしくは、それぞれ個別に、やる、やらない、をテストしないと

' いけないかもしれません。

' その点はご自分でもチェックしてみてください｡

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

Sub test200(sqlstr01 As String, _

sqlstr02 As String, _

sqlstr03 As String, _

sqlstr04 As String, _

sqlstr05 As String, _

sqlstr06 As String, _

sqlstr07 As String, _

sqlstr08 As String, _

sqlstr09 As String, _

sqlstr10 As String, _

ObjQry01 As QueryTable, _

DoevtFlg As Integer)

' '以下のSQL文用の変数（＝書き換えや抽出の命令文の作成用の変数）は、

' 'その変数宣言を、呼び出し元のプロシージャの中で書くかたちに作り変えたので、

' 'ここではコメントアウトします｡

' Dim sqlstr01 As String

' Dim sqlstr02 As String

' Dim sqlstr03 As String

' Dim sqlstr04 As String

' Dim sqlstr05 As String

'SQL文を代入するための変数を用意（ここでは「配列として使います。）

Dim SQLArrey As Variant

' 'シートやMicrosoftQueryの結果表（オブジェクト）の変数設定や代入は、

' 'このプロシージャの最初の部分でやってしまうので

' 'ここではコメントアウトします。

' '（呼び出し元から受け取るように作り変えたので）

' Dim ObjQry01 As QueryTable

' Set ObjQry01 = Worksheets("Sheet1").QueryTables(1)

'受け取ったSQL文（＝書き換えや抽出の命令文の作成のコード）を

'イミディエイトウィンドウに表示

Debug.Print ObjQry01.CommandText

' '以下のコード（＝SQL文作成のコード）は、

' '呼び出し元のプロシージャで書くかたちに作り変えたので

' 'ここではコメントアウトします｡

' sqlstr01 = "UPDATE `Sheet1$` "

' sqlstr02 = "SET a = '9fvds' "

' sqlstr03 = "WHERE b = 2;"

' sqlstr04 = ""

' sqlstr05 = ""

'CommandTextプロパティはVariant型（特に配列）の値なので

'ここで、Variant型の配列変数に、

'「句ごと」に SQL文を分けて代入します。

'ここで Variant型ではなくString型にするとエラーになります。

SQLArrey = Array(sqlstr01, _

sqlstr02, _

sqlstr03, _

sqlstr04, _

sqlstr05, _

sqlstr06, _

sqlstr07, _

sqlstr08, _

sqlstr09, _

sqlstr10)

'※重要注意事項

'なお、Variant型なのは、xlsmじゃなくてxlsだからなのか、

'ピボットテーブルのCommandTextじゃなくて

'MicrosoftQueryのそれだからなのか

'それともExcelのバージョンの違いなのか、未チェックです。

'なので、ご自分でも色々と調べてください。

'「エクセルのバージョン」、

'「拡張子」、

'「ピボット/MicrosoftQuery」

'等々の違いによっては、

'SQL文をVariant型（特に配列）でなくても、ただのString型でも

'OKかもしれません。

'オブジェクトとして受け取った「MicrosoftQueryの結果表」の、

'SQL文の入れ替えをします。

ObjQry01.CommandText = SQLArrey

'入れ替えたSQL文をそのまま実行します。

'MicrosoftQueryの場合は、Excecuteなどではなく、

'「Refresh」が「実行と同じ意味」らしいです。

ObjQry01.Refresh

'ただ、「SQL文の上書き保存」の意味があるのかまでは未チェックです。

'もしかしたら1つ前のコードの

'「ObjQry01.CommandText = SQLArrey」で

'上書き保存しているのかもしれません。

'なので、ご自分でも調べてみてください。

' '以下のコードは「QryRefDoEvt01()」プロシージャでやる形に作りかえたので

' 'ここではコメントアウトします。

' 'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避

' '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html

' Do While Worksheets("Sheet1").QueryTables(1).Refreshing

' DoEvents

' Loop

'「バックグラウンドでデータが更新中であるため、この操作は行えません」

'というエラーを回避します。

'ただし、クエリによってはCall先のLoopのプログラムが

'無限ループになってしまうので、

'1=ループする：2=ループ処理自体をしない、

'ということで進めます。

If DoevtFlg = 1 Then

Call QryRefDoEvt01(ObjQry01)

ElseIf DoevtFlg = 2 Then

Else

End If

End Sub

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

'クエリの更新（Refresh）に処理が移った際に、

'「バックグラウンドでデータが更新中であるため、

' この操作は行えません」

'・・・というエラーが出る場合の回避をするプロシージャです。

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

Sub QryRefDoEvt01(ObjMsqTbl As QueryTable)

Do While ObjMsqTbl.Refreshing

DoEvents

Loop

End Sub

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

'すでに作成してある「D:\test88\test01.xls」のMicrosoftQueryの結果表に対する

'SQL文を生成して、それを「test200」プロシージャに送ってSQL文を実行するプロシージャです。

'ただし、列の入れ替えなどをしたときに、日付データのセルの書式が数値などになったままだと、

'日付がシリアル値に変わって表示されてしまうことがあります。

'ですので、

'結果表のセル範囲の書式をすべて「標準」に直す、などの処理を追加したほうがいいかもしれません。

'「列を減らしたり、入れ替えることは絶対にない、増減は行のみ」というような場合は

'このままでもある程度使えるかもしれません。

'列幅が自動調整されるのも、もしいやだったらそのようにコードを書くか、

'MicrosoftQueryの結果表を右クリックしてプロパティにて「列の幅を調整する」のチェックを

'はずします。

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

Sub test201()

'一応配列にしなくても動くのですが、

'変数の数が多くてのちのちの改変がめんどっちくなりそうなので

'配列を宣言しています。

Dim StrSql01(9) As String

'1つめのSQLの生成。（表の内容を閉じたまま書き換えするための命令文の作成。）

'SQL文の「句ごと」に配列に代入しますが、

'各句ごとに最後に半角スペースを入れることを忘れないようにします。

'もともとSQL文は、各句ごとの境目は半角スペースで区切らないと

'エラーになってしまうためです。

'シート名（というかシステムテーブル名）は、バッククォートで囲む必要があります。

StrSql01(0) = "UPDATE `Sheet1$` "

StrSql01(1) = "SET 列a = 'あf' "

StrSql01(2) = "WHERE 列b = 2;"

StrSql01(3) = ""

StrSql01(4) = ""

'1つめのSQLの実行。

Call test200(StrSql01(0), _

StrSql01(1), _

StrSql01(2), _

StrSql01(3), _

StrSql01(4), _

"", _

Worksheets("Sheet1").QueryTables(1), _

'※↑の注意事項

'「Worksheets("Sheet1").QueryTables(1)」の部分は

'「Worksheets("Sheet1").QueryTables(1).name」で名前を調べて

'「Worksheets("Sheet1").QueryTables("Excel Files からのクエリ_3")」

'といったようなオブジェクト式の書き方をしてもOKです。

'「Worksheets("Sheet1").」を「Activesheet.」と書きかえれば

'Sheet1だけでなく、どのシートに作られたMicrosoftQueryの結果の表にも

'使えます。

'2つめのSQLの生成。（書き換えた内容を再表示するための命令文の作成。）

'シート名（というかシステムテーブル名）は、バッククォートで囲む必要があります。

StrSql01(5) = "SELECT `Sheet1$`.列a, `Sheet1$`.列b, `Sheet1$`.列c "

StrSql01(6) = "FROM `D:\test88\test01.xls`.`Sheet1$` `Sheet1$`"

StrSql01(7) = ""

StrSql01(8) = ""

StrSql01(9) = ""

' 'SQL文は、以下のようにSELECT句でテーブル名を省いたり、

' '部分的にバッククォートで囲むことを省いても大丈夫です。

' '操作対象のExcelファイルの拡張子を省略してもダイジョブな場合もあります。

' 'どう省略しても大丈夫か？はご自分でもチェックしてみてください。

' StrSql01(5) = "SELECT a, b, c "

' StrSql01(6) = "FROM `Sheet1$`"

' 'また、ここでは一応、StrSql01(5)～(9)に代入しましたが、長いSQL文の場合は

' 'いったん、(0)～(9)までをクリアしてからまた、(0)～(9)を使ってもいいかもです。

'2つめのSQLの実行。

Call test200(StrSql01(5), _

StrSql01(6), _

StrSql01(7), _

StrSql01(8), _

StrSql01(9), _

"", _

Worksheets("Sheet1").QueryTables(1), _

End Sub

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

'テスト用のプロシージャです。今回は特には使いません。

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

Sub test100()

Dim sqlstr01 As String

Dim sqlstr02 As String

Dim sqlstr03 As String

Dim sqlstr04 As String

Dim sqlstr05 As String

Dim SQLArrey As Variant

Dim ObjQry01 As QueryTable

Set ObjQry01 = Worksheets("Sheet1").QueryTables(1)

Debug.Print ObjQry01.CommandText

sqlstr01 = "UPDATE `Sheet1$` "

sqlstr02 = "SET a = '9fvds' "

sqlstr03 = "WHERE b = 2;"

sqlstr04 = ""

sqlstr05 = ""

SQLArrey = Array(sqlstr01, sqlstr02, sqlstr03, sqlstr04, sqlstr05)

ObjQry01.CommandText = SQLArrey

ObjQry01.Refresh

' 'Test99に処理が移った際に、「バックグラウンドでデータが更新中であるため、この操作は行えません」エラーの回避

' '参考Webページ→http://itpro-blogger.blogspot.com/2008/12/vbaquerytable.html

' Do While Worksheets("Sheet1").QueryTables(1).Refreshing

' DoEvents

' Loop

Call QryRefDoEvt01(ObjQry01)

'Call Test99

End Sub

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

'テスト用のプロシージャです。今回は特には使いません。

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

Sub test12()

Call test100

Call test99

End Sub

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

MENU

関連記事

最近の投稿

カテゴリー

MENU

Access2000VBA・Excel2000VBA独学～SQLにて「閉じたExcelファイルを ”閉じたまま” 書き込む方法01」～Microsoft Query（QueryTableオブジェクト）を利用する場合～

関連記事

最近の投稿

カテゴリー

タグ