質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

1535閲覧

Collectionsの使い方がいまいち分かりません

yuusu

総合スコア12

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2018/06/26 11:56

Collectionsについてご教授願います。

VBAで自動的にSELECT文を作成できるようにしたいのですが、Collectionsにうまく値が追記できません
入力データはexcel表で2列のデータ

部署コード品番コード
2020001
102002
123001
104003
202100

出力先を一列開けた2行目からとして

SELECT * テーブル名 where bsyo = '20' AND hinban ='2001' AND hinban ='2100' order by DESC
SELECT * テーブル名 where bsyo = '10' AND hinban ='2002' AND hinban ='4003' order by DESC
SELECT * テーブル名 where bsyo = '22' AND hinban ='3001' order by DESC

上のような出力にしたいです。

VBA

1コード 2Sub Creatsql() 3 4 Dim srcsheet As worksheet 5 Set srcsheet = Activesheet 6 Dim targetRange As Range 7 Set targetRange = srcsheet.usedRange.offset(1,0) 8 Dim targetcell As variant 9 targetcell = targetRange 10 Dim maxrow As Integer 11 maxrow = targetRange.row.count 12 13 14 15 Dim hash 16 Set hash = CreateObject("Scripting.Dictionary") 17 18 Dim x 19 For x = 1 To maxrow 20 Dim valueList 21 Dim key 22 key = targetcell(x, 1) 23 Dim value 24 value = targetcell(x, 2) 25 If hash.Exists(key) Then 26 Set valueList = hash.Item(key) 27 valueList.Add value 28 Else 29 Set valueList = CreateObject("System.Collections.ArrayList") 30 hash.Add key, valueList 31 valueList.Add value 32 End If 33 Next 34 35 Dim i 36 Dim keys 37 keys = hash.Keys 38 39 Dim returnCount 40 returnCount = hash.Count - 1 41 42 Dim returnCells() 43 ReDim returnCells(returnCount) 44 45 For i = 1 To hash.Count - 1 46 Dim line 47 line = "Select * テーブル名 where bsyo = '" & keys(i)& " ' " & " " 48 Dim myArrayList 49 Set myArrayList = hash.Item(keys(i)) 50 Dim ii 51 Dim iMax : iMax = myArrayList.Count - 1 52 For ii=1 To iMax 53 line = line & " AND hinban =' " & myArrayList(ii) & " ' "& " " 54 Next 55 returnCells(i) = line 56 Next 57 CellsToArray = returnCells 58 Dim str 59 For Each line In returnCells 60 str = str & line & " order by DESC " &vbCr 61 Next 62 63 64End sub() 65 66

そもそもやり方が間違っていた場合は、そちらを指摘していただきたいです。
稚拙な説明文で申し訳ございませんが、よろしくお願いします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

  • まず、SQLは他の方からも指摘されてますが、これでは1件も抽出されません。常に0件です。

hinban はOR条件にすべきです。
where bsyo = '20' AND (hinban ='20001' OR hinban ='2100')
これは、In演算子を使うとシンプルに記述できます。
where bsyo = '20' AND hinban In ('20001','2100')

  • Dictionaryを使うのはいいですが、System.Collections.ArrayListを使う意味はないです。

無意味に冗長になるだけです。

  • SQLのような長い文字列を生成するときは、&で繋げるよりReplaceで置換するようにしたほうが、シンプルかつ読みやすいコードになります。

  • これは人によるかも知れませんが、変数を途中で宣言するのはコードを読みにくいと思います。

コードの流れが宣言で断ち切られるので。

  • あと無駄に変数宣言が多い、一度しか使わないものはわざわざ変数に入れる必要はないでしょう。

  • 出力先が不明瞭ですが、1列空けた D2セルから下に出力することにします。

以上を考慮して、コーディングしてみました。

vba

1Sub Creatsql1() 2 Const cSQL = _ 3 "SELECT * テーブル名 where bsyo = '%1' AND hinban In (%2) order by DESC" 4 5 Dim targetRange As Range, outputCell As Range 6 Dim i As Long 7 Dim hash As Object 8 Dim key, value 9 Dim line As String 10 11 Set targetRange = ActiveSheet.Range("A1").CurrentRegion.Offset(1, 0) 12 Set hash = CreateObject("Scripting.Dictionary") 13 14 For i = 1 To targetRange.Rows.Count - 1 15 key = targetRange(i, 1).value 16 value = "'" & targetRange(i, 2).value & "'" 17 If hash.Exists(key) Then 18 hash(key) = hash(key) & "," & value 19 Else 20 hash(key) = value 21 End If 22 Next 23 24 Set outputCell = ActiveSheet.Range("D2") 25 For Each key In hash 26 line = Replace(cSQL, "%1", key) 27 line = Replace(line, "%2", hash(key)) 28 outputCell.value = line 29 Set outputCell = outputCell.Offset(1) '次行へ移動 30 Next 31 32End Sub

どうですか、だいぶスッキリしたでしょう。

投稿2018/06/26 20:34

編集2018/06/27 04:55
hatena19

総合スコア33610

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuusu

2018/06/27 10:21

ご回答ありがとうございます。 SQLに関する指摘もありがとうございます。 こんなにすっきりと書けるのだと勉強になりました。 ありがとうございました。
guest

0

SELECT * テーブル名 where bsyo = '20' AND hinban ='2001' AND hinban ='2100' order by DESC

SELECT * テーブル名 where bsyo = '10' AND hinban ='2002' AND hinban ='4003' order by DESC
SELECT * テーブル名 where bsyo = '22' AND hinban ='3001' order by DESC

は、

SELECT * テーブル名 where bsyo = '20' AND ( hinban ='20001' OR hinban ='2100') order by DESC

SELECT * テーブル名 where bsyo = '10' AND ( hinban ='2002' OR hinban ='4003') order by DESC
SELECT * テーブル名 where bsyo = '12' AND hinban ='3001' order by DESC

とすべきでは?

「hinban ='2001' AND hinban ='2100'」 とか、「hinban ='2002' AND hinban ='4003'」とかは、常にFALSEになるので、何も selectされません。また、部署='22'というのは入力データにありません。

投稿2018/06/26 12:29

coco_bauer

総合スコア6915

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

yuusu

2018/06/26 12:45

ご回答ありがとうございます。 SELECT文が間違っておりました。 SELECT * テーブル名 WHERE busyo ='20' AND(hinban ='20001') AND (hinban='2100') order by DESC にしたいと思います。 また、部署= '22'は 部署='12'の誤りでした。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問