回答編集履歴
6
注意事項の追記
answer
CHANGED
|
@@ -30,6 +30,16 @@
|
|
|
30
30
|
|
|
31
31
|
ここで書いたのはあくまで参考例ですので・・・。
|
|
32
32
|
|
|
33
|
+
※コメントがやたら長いので、不要なら全部消して見てみてください。
|
|
34
|
+
コード自体はかなり少ないです。
|
|
35
|
+
|
|
36
|
+
なお、他のブックに書き込みたいなら・・・、
|
|
37
|
+
・CreateObject関数・GetObject関数（実行時バインディング）、
|
|
38
|
+
あるいは、Newキーワード（事前バインディング）を使って
|
|
39
|
+
ファイルを非表示のまま開いて書き込む方法。
|
|
40
|
+
・閉じたファイルに書き込みたいなら、DAOやADOにて、
|
|
41
|
+
ループ＋Update系メソッド又はSQL＋Execute系メソッドを使って書き込む方法。
|
|
42
|
+
・・・などがあります。
|
|
33
43
|
```Excel
|
|
34
44
|
|
|
35
45
|
Sub test301()
|
5
コードの抜けの追加
answer
CHANGED
|
@@ -104,7 +104,7 @@
|
|
|
104
104
|
'ここではじめて、表が表示されます。
|
|
105
105
|
'（もともと何も表示されてない場合は、表が自動的に新規作成されます。）
|
|
106
106
|
objQtbl01.Refresh
|
|
107
|
-
|
|
107
|
+
Exit Sub
|
|
108
108
|
|
|
109
109
|
Else
|
|
110
110
|
|
4
コメントの追記
answer
CHANGED
|
@@ -48,7 +48,13 @@
|
|
|
48
48
|
strCnn01 = strCnn01 & "MaxBufferSize=2048;"
|
|
49
49
|
strCnn01 = strCnn01 & "PageTimeout=5;"
|
|
50
50
|
|
|
51
|
-
|
|
51
|
+
'ここで書き換えるのは基本、
|
|
52
|
+
'「DBQ=・・・」のフルパスと、
|
|
53
|
+
'「DefaultDir」のフォルダパスと、
|
|
54
|
+
'「Destination・・・」のシート名や表の起点のセル（Rangeのところ）
|
|
55
|
+
'あと、次段階の「.CommandText = Array（・・・）」の行のそのSQLの内容
|
|
56
|
+
'でOKかと思います。
|
|
57
|
+
'
|
|
52
58
|
|
|
53
59
|
'もしSheet2にMicrosoftQueryの結果の表（＝QueryTablesオブジェクト）が
|
|
54
60
|
'無かったら、作って表示させる処理。
|
|
@@ -63,21 +69,18 @@
|
|
|
63
69
|
'フルパスは恐らくUNCパス、つまり、他のPCの共有フォルダのxlsでも
|
|
64
70
|
'良いのではないかと思います。（ダメだったらすみません！）
|
|
65
71
|
|
|
66
|
-
|
|
72
|
+
’空っぽいのMicrosoft QueryオブジェクトをSheet2のB2セルを起点に作る処理。
|
|
73
|
+
|
|
67
74
|
Set objQtbl01 = Worksheets("Sheet2").QueryTables.Add( _
|
|
68
75
|
Connection:=strCnn01, _
|
|
69
76
|
Destination:=Worksheets("Sheet2").Range("B2"))
|
|
70
77
|
|
|
71
|
-
|
|
78
|
+
|
|
72
|
-
'「DBQ=・・・」のフルパスと、
|
|
73
|
-
'「DefaultDir」のフォルダパスと、
|
|
74
|
-
'「Destination・・・」のシート名や表の起点のセル（Rangeのところ）
|
|
75
|
-
'あと、次段階の「.CommandText = Array（・・・）」の行のそのSQLの内容
|
|
76
|
-
'でOKかと思います。
|
|
77
|
-
'
|
|
78
79
|
'今回のこの例でのSQL文では、Sheet2に対して、
|
|
79
80
|
'「B2」セルを起点としてMicrosoftQueryの結果の表を作成します。
|
|
80
|
-
'
|
|
81
|
+
'この時点では、オブジェクトしては作成されているのですが、
|
|
82
|
+
'まだ条件設定とかしてないので、Sheet2には何も表示されません。
|
|
83
|
+
|
|
81
84
|
'なお、「Worksheets("Sheet2").」を「Activesheet.」に書き換えると、
|
|
82
85
|
'現在表示しているシートに結果の表が生成されます。
|
|
83
86
|
'
|
|
@@ -91,10 +94,14 @@
|
|
|
91
94
|
|
|
92
95
|
|
|
93
96
|
|
|
94
|
-
'QueryTablesオブジェクトのSQLを書き換え
|
|
97
|
+
'QueryTablesオブジェクトのSQLを書き換え。
|
|
98
|
+
'（このサンプルでは、毎回、空っぽの状態にSQL文が入ります。）
|
|
95
99
|
objQtbl01.CommandText = Array("SELECT * FROM `Sheet1$`")
|
|
96
100
|
|
|
97
101
|
'書き換えたSQLを実行して表に反映。
|
|
102
|
+
'空っぽのMicrosoftQueryのオブジェクトの中に、SQL文で指定した内容の
|
|
103
|
+
'表ができあがる・・・みたいな雰囲気です。
|
|
104
|
+
'ここではじめて、表が表示されます。
|
|
98
105
|
'（もともと何も表示されてない場合は、表が自動的に新規作成されます。）
|
|
99
106
|
objQtbl01.Refresh
|
|
100
107
|
|
3
CommanTextが配列かどうかについての追記
answer
CHANGED
|
@@ -10,6 +10,9 @@
|
|
|
10
10
|
長いSQL文は、各句ごとに、Arrayにてカンマで区切って、CommanText（配列）に格納します。
|
|
11
11
|
もし、CommanTextが配列じゃなかったら、カンマで区切らずに1行の長い命令文として格納します。
|
|
12
12
|
|
|
13
|
+
CommanTextが配列かそうじゃないかは、一つ何らかの複雑な条件のMicrosoftQueryの結果の表を手動で作ってみて、それのCommanTextの内容をVBAでイミディエイトに表示させ、複数行に分かれて出てきたら配列と思っていいです。1行だけなら配列じゃないかもです。
|
|
14
|
+
配列だったらArreyを使うし、配列じゃなかったら、Arreyは要りません。
|
|
15
|
+
|
|
13
16
|
周囲に、AccessやSQLがわかる人が居ましたら、こちらの方法も今後便利かと思います。
|
|
14
17
|
複数の条件であっても、1つ1つが複雑でないとか、リレーション（VlookUp関数のような紐つけ）も同時に使いながらとか、でしたら、通常のVBAでデータ抽出するよりははるかにラクです。
|
|
15
18
|
|
2
SQLの追加
answer
CHANGED
|
@@ -16,6 +16,17 @@
|
|
|
16
16
|
※こちらのテストでは、xlsにも、xlsmに対しても、覗きにいくことができました。
|
|
17
17
|
（ダメだったらすみません！）
|
|
18
18
|
|
|
19
|
+
なお、もし、何らかの条件で（K,Q）の列のみを取り出したいとこは、Arreyのところで、
|
|
20
|
+
|
|
21
|
+
SELECT K列に相当する列名, Q列に相当する列名 FROM `シート名$` WHERE 条件としたい列名 >= 10;
|
|
22
|
+
|
|
23
|
+
みたいな感じで書きます。（SELECTやFROMなどの命令語句の前後には半角スペースが必要です。）
|
|
24
|
+
WHEREの条件句のところでは、OrやAndなども使って複数の条件が設定できます。
|
|
25
|
+
|
|
26
|
+
詳しくは、Webや先輩、分かる人などに聞いてみてください。
|
|
27
|
+
|
|
28
|
+
ここで書いたのはあくまで参考例ですので・・・。
|
|
29
|
+
|
|
19
30
|
```Excel
|
|
20
31
|
|
|
21
32
|
Sub test301()
|
1
コードの書き方練習
answer
CHANGED
|
@@ -6,10 +6,18 @@
|
|
|
6
6
|
そこでSQLでの命令文も使えますので、ワークシート関数を多用したり、ループなどを使ったプログラムを作るよりはらくちんではないかと思います。
|
|
7
7
|
|
|
8
8
|
以下のコードは「D:\test88\test01.xls」というファイルの「Sheet1]を覗きに行って、Sheet2に表示させます。
|
|
9
|
-
覗きに行く「Sheet1」は「"SELECT * FROM `Sheet1$`"」で指定してます。
|
|
9
|
+
覗きに行く「Sheet1」は「"SELECT * FROM `Sheet1$`"」のところで指定してます。
|
|
10
|
+
長いSQL文は、各句ごとに、Arrayにてカンマで区切って、CommanText（配列）に格納します。
|
|
11
|
+
もし、CommanTextが配列じゃなかったら、カンマで区切らずに1行の長い命令文として格納します。
|
|
10
12
|
|
|
13
|
+
周囲に、AccessやSQLがわかる人が居ましたら、こちらの方法も今後便利かと思います。
|
|
14
|
+
複数の条件であっても、1つ1つが複雑でないとか、リレーション（VlookUp関数のような紐つけ）も同時に使いながらとか、でしたら、通常のVBAでデータ抽出するよりははるかにラクです。
|
|
11
15
|
|
|
16
|
+
※こちらのテストでは、xlsにも、xlsmに対しても、覗きにいくことができました。
|
|
17
|
+
（ダメだったらすみません！）
|
|
12
18
|
|
|
19
|
+
```Excel
|
|
20
|
+
|
|
13
21
|
Sub test301()
|
|
14
22
|
|
|
15
23
|
Dim objQtbl01 As QueryTable
|
|
@@ -72,13 +80,11 @@
|
|
|
72
80
|
'QueryTablesオブジェクトのSQLを書き換え
|
|
73
81
|
objQtbl01.CommandText = Array("SELECT * FROM `Sheet1$`")
|
|
74
82
|
|
|
75
|
-
|
|
83
|
+
'書き換えたSQLを実行して表に反映。
|
|
76
84
|
'（もともと何も表示されてない場合は、表が自動的に新規作成されます。）
|
|
77
85
|
objQtbl01.Refresh
|
|
78
86
|
|
|
79
|
-
|
|
80
|
-
|
|
87
|
+
|
|
81
|
-
|
|
82
88
|
Else
|
|
83
89
|
|
|
84
90
|
'もしすでに、Sheet2にMicrosoftQueryの結果の表が
|
|
@@ -95,4 +101,5 @@
|
|
|
95
101
|
|
|
96
102
|
|
|
97
103
|
End Sub
|
|
98
|
-
'
|
|
104
|
+
'
|
|
105
|
+
```
|