回答編集履歴
6
注意事項の追記
test
CHANGED
|
@@ -62,6 +62,26 @@
|
|
|
62
62
|
|
|
63
63
|
|
|
64
64
|
|
|
65
|
+
※コメントがやたら長いので、不要なら全部消して見てみてください。
|
|
66
|
+
|
|
67
|
+
コード自体はかなり少ないです。
|
|
68
|
+
|
|
69
|
+
|
|
70
|
+
|
|
71
|
+
なお、他のブックに書き込みたいなら・・・、
|
|
72
|
+
|
|
73
|
+
・CreateObject関数・GetObject関数（実行時バインディング）、
|
|
74
|
+
|
|
75
|
+
あるいは、Newキーワード（事前バインディング）を使って
|
|
76
|
+
|
|
77
|
+
ファイルを非表示のまま開いて書き込む方法。
|
|
78
|
+
|
|
79
|
+
・閉じたファイルに書き込みたいなら、DAOやADOにて、
|
|
80
|
+
|
|
81
|
+
ループ＋Update系メソッド又はSQL＋Execute系メソッドを使って書き込む方法。
|
|
82
|
+
|
|
83
|
+
・・・などがあります。
|
|
84
|
+
|
|
65
85
|
```Excel
|
|
66
86
|
|
|
67
87
|
|
5
コードの抜けの追加
test
CHANGED
|
@@ -210,7 +210,7 @@
|
|
|
210
210
|
|
|
211
211
|
objQtbl01.Refresh
|
|
212
212
|
|
|
213
|
-
|
|
213
|
+
Exit Sub
|
|
214
214
|
|
|
215
215
|
|
|
216
216
|
|
4
コメントの追記
test
CHANGED
|
@@ -98,7 +98,19 @@
|
|
|
98
98
|
|
|
99
99
|
|
|
100
100
|
|
|
101
|
-
|
|
101
|
+
'ここで書き換えるのは基本、
|
|
102
|
+
|
|
103
|
+
'「DBQ=・・・」のフルパスと、
|
|
104
|
+
|
|
105
|
+
'「DefaultDir」のフォルダパスと、
|
|
106
|
+
|
|
107
|
+
'「Destination・・・」のシート名や表の起点のセル（Rangeのところ）
|
|
108
|
+
|
|
109
|
+
'あと、次段階の「.CommandText = Array（・・・）」の行のそのSQLの内容
|
|
110
|
+
|
|
111
|
+
'でOKかと思います。
|
|
112
|
+
|
|
113
|
+
'
|
|
102
114
|
|
|
103
115
|
|
|
104
116
|
|
|
@@ -128,7 +140,9 @@
|
|
|
128
140
|
|
|
129
141
|
|
|
130
142
|
|
|
131
|
-
|
|
143
|
+
’空っぽいのMicrosoft QueryオブジェクトをSheet2のB2セルを起点に作る処理。
|
|
144
|
+
|
|
145
|
+
|
|
132
146
|
|
|
133
147
|
Set objQtbl01 = Worksheets("Sheet2").QueryTables.Add( _
|
|
134
148
|
|
|
@@ -138,32 +152,24 @@
|
|
|
138
152
|
|
|
139
153
|
|
|
140
154
|
|
|
155
|
+
|
|
156
|
+
|
|
141
|
-
'こ
|
|
157
|
+
'今回のこの例でのSQL文では、Sheet2に対して、
|
|
158
|
+
|
|
142
|
-
|
|
159
|
+
'「B2」セルを起点としてMicrosoftQueryの結果の表を作成します。
|
|
160
|
+
|
|
143
|
-
'
|
|
161
|
+
'この時点では、オブジェクトしては作成されているのですが、
|
|
162
|
+
|
|
144
|
-
|
|
163
|
+
'まだ条件設定とかしてないので、Sheet2には何も表示されません。
|
|
164
|
+
|
|
165
|
+
|
|
166
|
+
|
|
167
|
+
'なお、「Worksheets("Sheet2").」を「Activesheet.」に書き換えると、
|
|
168
|
+
|
|
145
|
-
'
|
|
169
|
+
'現在表示しているシートに結果の表が生成されます。
|
|
146
|
-
|
|
147
|
-
'「Destination・・・」のシート名や表の起点のセル（Rangeのところ）
|
|
148
|
-
|
|
149
|
-
'あと、次段階の「.CommandText = Array（・・・）」の行のそのSQLの内容
|
|
150
|
-
|
|
151
|
-
'でOKかと思います。
|
|
152
170
|
|
|
153
171
|
'
|
|
154
172
|
|
|
155
|
-
'今回のこの例でのSQL文では、Sheet2に対して、
|
|
156
|
-
|
|
157
|
-
'「B2」セルを起点としてMicrosoftQueryの結果の表を作成します。
|
|
158
|
-
|
|
159
|
-
'
|
|
160
|
-
|
|
161
|
-
'なお、「Worksheets("Sheet2").」を「Activesheet.」に書き換えると、
|
|
162
|
-
|
|
163
|
-
'現在表示しているシートに結果の表が生成されます。
|
|
164
|
-
|
|
165
|
-
'
|
|
166
|
-
|
|
167
173
|
'また、SQL文（表操作の命令文）の中では、
|
|
168
174
|
|
|
169
175
|
'test01.xlsのSheet1（＝システムテーブル）のことを「`Sheet1$`」と
|
|
@@ -184,7 +190,9 @@
|
|
|
184
190
|
|
|
185
191
|
|
|
186
192
|
|
|
187
|
-
'QueryTablesオブジェクトのSQLを書き換え
|
|
193
|
+
'QueryTablesオブジェクトのSQLを書き換え。
|
|
194
|
+
|
|
195
|
+
'（このサンプルでは、毎回、空っぽの状態にSQL文が入ります。）
|
|
188
196
|
|
|
189
197
|
objQtbl01.CommandText = Array("SELECT * FROM `Sheet1$`")
|
|
190
198
|
|
|
@@ -192,6 +200,12 @@
|
|
|
192
200
|
|
|
193
201
|
'書き換えたSQLを実行して表に反映。
|
|
194
202
|
|
|
203
|
+
'空っぽのMicrosoftQueryのオブジェクトの中に、SQL文で指定した内容の
|
|
204
|
+
|
|
205
|
+
'表ができあがる・・・みたいな雰囲気です。
|
|
206
|
+
|
|
207
|
+
'ここではじめて、表が表示されます。
|
|
208
|
+
|
|
195
209
|
'（もともと何も表示されてない場合は、表が自動的に新規作成されます。）
|
|
196
210
|
|
|
197
211
|
objQtbl01.Refresh
|
3
CommanTextが配列かどうかについての追記
test
CHANGED
|
@@ -22,6 +22,12 @@
|
|
|
22
22
|
|
|
23
23
|
|
|
24
24
|
|
|
25
|
+
CommanTextが配列かそうじゃないかは、一つ何らかの複雑な条件のMicrosoftQueryの結果の表を手動で作ってみて、それのCommanTextの内容をVBAでイミディエイトに表示させ、複数行に分かれて出てきたら配列と思っていいです。1行だけなら配列じゃないかもです。
|
|
26
|
+
|
|
27
|
+
配列だったらArreyを使うし、配列じゃなかったら、Arreyは要りません。
|
|
28
|
+
|
|
29
|
+
|
|
30
|
+
|
|
25
31
|
周囲に、AccessやSQLがわかる人が居ましたら、こちらの方法も今後便利かと思います。
|
|
26
32
|
|
|
27
33
|
複数の条件であっても、1つ1つが複雑でないとか、リレーション（VlookUp関数のような紐つけ）も同時に使いながらとか、でしたら、通常のVBAでデータ抽出するよりははるかにラクです。
|
2
SQLの追加
test
CHANGED
|
@@ -34,6 +34,28 @@
|
|
|
34
34
|
|
|
35
35
|
|
|
36
36
|
|
|
37
|
+
なお、もし、何らかの条件で（K,Q）の列のみを取り出したいとこは、Arreyのところで、
|
|
38
|
+
|
|
39
|
+
|
|
40
|
+
|
|
41
|
+
SELECT K列に相当する列名, Q列に相当する列名 FROM `シート名$` WHERE 条件としたい列名 >= 10;
|
|
42
|
+
|
|
43
|
+
|
|
44
|
+
|
|
45
|
+
みたいな感じで書きます。（SELECTやFROMなどの命令語句の前後には半角スペースが必要です。）
|
|
46
|
+
|
|
47
|
+
WHEREの条件句のところでは、OrやAndなども使って複数の条件が設定できます。
|
|
48
|
+
|
|
49
|
+
|
|
50
|
+
|
|
51
|
+
詳しくは、Webや先輩、分かる人などに聞いてみてください。
|
|
52
|
+
|
|
53
|
+
|
|
54
|
+
|
|
55
|
+
ここで書いたのはあくまで参考例ですので・・・。
|
|
56
|
+
|
|
57
|
+
|
|
58
|
+
|
|
37
59
|
```Excel
|
|
38
60
|
|
|
39
61
|
|
1
コードの書き方練習
test
CHANGED
|
@@ -14,11 +14,27 @@
|
|
|
14
14
|
|
|
15
15
|
以下のコードは「D:\test88\test01.xls」というファイルの「Sheet1]を覗きに行って、Sheet2に表示させます。
|
|
16
16
|
|
|
17
|
-
覗きに行く「Sheet1」は「"SELECT * FROM `Sheet1$`"」で指定してます。
|
|
17
|
+
覗きに行く「Sheet1」は「"SELECT * FROM `Sheet1$`"」のところで指定してます。
|
|
18
|
+
|
|
18
|
-
|
|
19
|
+
長いSQL文は、各句ごとに、Arrayにてカンマで区切って、CommanText（配列）に格納します。
|
|
20
|
+
|
|
19
|
-
|
|
21
|
+
もし、CommanTextが配列じゃなかったら、カンマで区切らずに1行の長い命令文として格納します。
|
|
22
|
+
|
|
23
|
+
|
|
24
|
+
|
|
20
|
-
|
|
25
|
+
周囲に、AccessやSQLがわかる人が居ましたら、こちらの方法も今後便利かと思います。
|
|
26
|
+
|
|
21
|
-
|
|
27
|
+
複数の条件であっても、1つ1つが複雑でないとか、リレーション（VlookUp関数のような紐つけ）も同時に使いながらとか、でしたら、通常のVBAでデータ抽出するよりははるかにラクです。
|
|
28
|
+
|
|
29
|
+
|
|
30
|
+
|
|
31
|
+
※こちらのテストでは、xlsにも、xlsmに対しても、覗きにいくことができました。
|
|
32
|
+
|
|
33
|
+
（ダメだったらすみません！）
|
|
34
|
+
|
|
35
|
+
|
|
36
|
+
|
|
37
|
+
```Excel
|
|
22
38
|
|
|
23
39
|
|
|
24
40
|
|
|
@@ -146,7 +162,7 @@
|
|
|
146
162
|
|
|
147
163
|
|
|
148
164
|
|
|
149
|
-
|
|
165
|
+
'書き換えたSQLを実行して表に反映。
|
|
150
166
|
|
|
151
167
|
'（もともと何も表示されてない場合は、表が自動的に新規作成されます。）
|
|
152
168
|
|
|
@@ -154,11 +170,7 @@
|
|
|
154
170
|
|
|
155
171
|
|
|
156
172
|
|
|
157
|
-
|
|
158
|
-
|
|
159
|
-
|
|
173
|
+
|
|
160
|
-
|
|
161
|
-
|
|
162
174
|
|
|
163
175
|
Else
|
|
164
176
|
|
|
@@ -193,3 +205,5 @@
|
|
|
193
205
|
End Sub
|
|
194
206
|
|
|
195
207
|
'
|
|
208
|
+
|
|
209
|
+
```
|