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

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

ただいまの
回答率

88.04%

VBAマクロでselectSQLを作成

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 5,606

score 18

前提・実現したいこと

エクセルにテーブル名一覧の記載があるシートとカラム一覧の記載があるシートがあります。
カラム一覧のシートには対応するテーブルも記載があり、そのカラムがどのテーブルに属するものか
確認ができます。

【例】
*テーブル一覧記載シート
|テーブル名|
|顧客テーブル|
|顧客管理テーブル|
・・・

*カラム一覧記載シート
|テーブル名|カラム名|
|顧客テーブル|顧客名|
|顧客テーブル|顧客年齢|
|顧客管理テーブル|顧客名|
|顧客管理テーブル|顧客ID|
|顧客管理テーブル|顧客住所|
・・・

VBAマクロを利用してシートに記載のあるテーブルよりカラムを選択するSQLをループで回して
作成したいと思います。

上の例を元に作成したいイメージは 
SELECT 顧客名,顧客年齢
FROM   顧客管理テーブル
で、カラム名が指定するテーブル名のものでなくなったら次のループで

SELECT 顧客名,顧客ID,顧客住所
FROM  顧客管理テーブル
という風に次のSELECT文を作成するようにしたいです。

発生している問題・エラーメッセージ

作りたいイメージはあるのですが、どこから手を着けていいのかまったくわかりません。

該当のソースコード

特にループを回す部分で次のSQLの作成にどう切り替えればいいのかが不明です。

補足

やりたい処理の流れ
①テーブル名とカラム名を定義
②テーブルシートの一番最初のテーブル名を見に行き
テーブル名にsetする
③カラムシートの一番最初のカラムを見に行き、テーブル名が②でセットしたものと同じであれば
カラム名にsetする
④テーブル名が②と一致しなくなるまで繰り返し、カラム名は,でつなげる
⑤カラム名の一致をしなくなったら、ループを抜け、sqlを作成する
繰り返し

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+1

簡単ではありますがVBAを作成しました。
イミディエイトウインドウに結果を出しているので、必要に応じて出す場所は変更頂けたらと思います。

コードの上部の定数部分を変更すれば実際のシートの状況に適合して動くと思います。

Option Explicit

' 定数宣言(ここを実際の値に変更して下さい)
Const テーブル一覧シート名 As String = "テーブル一覧"
Const カラム一覧シート名 As String = "カラム一覧"
Const テーブル一覧シート_テーブル列 As Long = 1
Const カラム一覧シート_テーブル列 As Long = 1
Const カラム一覧シート_カラム列 As Long = 2
Const IS_HEADER As Boolean = True
' IS_HEADERは、各シートの1行目がヘッダ情報である場合(=SQL文の生成が不要な場合)に True を、そうでない場合に False を設定下さい

' SQL文を生成
Public Sub CreateSql()

    ' 変数宣言
    Dim tableSheet As Worksheet
    Dim columnSheet As Worksheet
    Dim rng As Range
    Dim r As Long
    Dim r2 As Long
    Dim tableName As String
    Dim columnNames As Collection
    Dim columnName As Variant
    Dim sql As String

    ' テーブル一覧シート と カラム一覧シートを取得
    With ThisWorkbook
        Set tableSheet = .Worksheets(テーブル一覧シート名)
        Set columnSheet = .Worksheets(カラム一覧シート名)
    End With

    ' 全テーブル走査
    Set rng = tableSheet.Columns(テーブル一覧シート_テーブル列)
    For r = IIf(IS_HEADER, 2, 1) To rng.Cells(rng.Cells.Count).End(xlUp).Row
        ' テーブル名取得
        tableName = tableSheet.Cells(r, テーブル一覧シート_テーブル列).Text

        ' 全カラム取得
        Set rng = columnSheet.Columns(カラム一覧シート_テーブル列)
        Set columnNames = New Collection
        For r2 = IIf(IS_HEADER, 2, 1) To rng.Cells(rng.Cells.Count).End(xlUp).Row
            If columnSheet.Cells(r2, カラム一覧シート_テーブル列).Text = tableName Then
                columnNames.Add columnSheet.Cells(r2, カラム一覧シート_カラム列).Text
            End If
        Next

        ' SQL文生成
        sql = ""
        sql = "SELECT"
        For Each columnName In columnNames
            sql = sql & IIf(sql = "SELECT", " ", ", ") & CStr(columnName)
        Next
        sql = sql & " FROM " & tableName

        ' 結果出力
        Debug.Print sql

    Next

End Sub

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/11 17:31

    回答ありがとうございます。試してみたところsql作成できました。カラムが多いためかsqlが文途中で改行されてしまいます。(カラム名が途切れてしまう)何か対処法はございますでしょうか。

    キャンセル

  • 2017/10/11 17:36

    まずは原因を特定する必要があります。
    文字が長いからといって勝手に改行コードが入ることはありません。
    それはセル内改行があるからではないでしょうか?

    キャンセル

+1

baseballyama様がコレクション使用での回答でしたので、配列使用で作成してみました。
お役に立てば幸いです。
一覧記載シートはどちらも2行目からのデータ始まりです。

Public Sub CreateSql()


    '1.テーブル一覧記載シート(A)

    Dim sheetA As Worksheet
    Dim endRowA As Long

    Set sheetA = ThisWorkbook.Worksheets("テーブル一覧記載シート")
    endRowA = sheetA.Cells(Rows.count, 1).End(xlUp).Row '最終行取得

    Dim tableNamesA() As String  'テーブル名格納用配列
    Dim n As Long
    Dim i As Long

    ReDim tableNamesA(endRowA - 2)  '配列初期化(要素は0始まり、ヘッダー行含めずでデータ数=>最終行-2)
    n = 0
    For i = 2 To endRowA
        tableNamesA(n) = sheetA.Cells(i, 1).Value   'テーブル名を配列に格納
        n = n + 1
    Next


    '2.カラム一覧記載シート(B)

    Dim sheetB As Worksheet
    Dim endRowB As Long

    Set sheetB = ThisWorkbook.Worksheets("カラム一覧記載シート")
    endRowB = sheetB.Cells(Rows.count, 1).End(xlUp).Row '最終行取得

    Dim tableNamesB() As String 'テーブル名格納用配列
    Dim columnNamesB() As String 'カラム名格納用配列

    ReDim tableNamesB(endRowB - 2)  '配列初期化(要素は0始まり、ヘッダー行含めずでデータ数=>最終行-2
    ReDim columnNamesB(endRowB - 2)  '同上
    n = 0
    For i = 2 To endRowB
        tableNamesB(n) = sheetB.Cells(i, 1).Value   'テーブル名を配列に格納
        columnNamesB(n) = sheetB.Cells(i, 2).Value  'カラム名を配列に格納
        n = n + 1
    Next


    '3.テーブル名でマッチング、カラム名取得、Sql作成

    Dim foundNames() As String  '取得カラム名格納用配列
    Dim j As Long
    Dim k As Long
    Dim query As String

    For j = LBound(tableNamesA) To UBound(tableNamesA)  'テーブル一覧記載シートの取得データ数分ループ

        query = "SELECT "  'Sql文字列初期化
        n = 0

        For k = LBound(tableNamesB) To UBound(tableNamesB)  'カラム一覧記載シートの取得データ数分ループ

            If tableNamesA(j) = tableNamesB(k) Then 'テーブル名が一致したらカラム名を格納

                ReDim Preserve foundNames(n)
                foundNames(n) = columnNamesB(k)                
                n = n + 1

            End If
        Next

        query = query & Join(foundNames, ",") & " FROM " & tableNamesA(j) & ";" 'Sql文字列作成

        Debug.Print query

        ReDim foundNames(0)  '次のループのために取得カラム名格納用配列を初期化

    Next

End Sub

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/11 17:29

    ご回答ありがとうございます。確認させていただきます!

    キャンセル

  • 2017/10/11 17:49

    下から3つめの「Redim ~ '次のループのために ・・・」の部分を修正しました

    キャンセル

0

列名の列挙が必要なければ、

="select * from " & テーブル一覧記載シート!A2


みたいに項目を*で指定することで、全項目取得するSQLになります。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/10/10 17:44

    回答ありがとうござます。カンマで区切り列挙したいです。

    キャンセル

  • 2017/10/11 17:32

    因みに、どういった利用目的でしょうか?

    キャンセル

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

  • ただいまの回答率 88.04%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る