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

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

ただいまの
回答率

90.50%

  • SQL

    2394questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • VBA

    1802questions

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

  • Excel

    1539questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • マクロ

    227questions

    定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

  • ループ

    53questions

    ループとは、プログラミングにおいて、条件に合致している間、複数回繰り返し実行される箇所や、その制御構造を指します

VBAマクロ 置き換えループが空振りしてしまう

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 708

chanrina0920

score 10

前提・実現したいこと

こちらで何度か質問させていただき設定シートとテキストのSQLフォーマットを読み込み、SQLを大量作成する
マクロを作成しています。全部で100テーブル以上を対象にSQL作成をするのですが、出力されたSQLがすべて最初の1テーブル分となっており、それぞれのテーブル毎のSQLが出力されるようにしたいです。

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

テーブル一覧を元にループを回し、それぞれのSQLを作成したいのですが
一番最初に取得したテーブル情報のSQLが大量に作成されてしまいます。
strSQLの{変数}を処理の中で置き換えしているのですが
次のテーブルの値や、カラム値の置き換えができていないようです。
1回目のループで置き換えしてしまっているためでしょうか?
この場合、どのような対応をすればよいのでしょうか。

該当のソースコード

一旦現状のソース全体を貼ります。
ループ2回目より{変数}の置き換えが空振りしている気がします。

Option Explicit

' 定数宣言
Const テーブル名シート名 As String = "テーブル名"
Const カラム名シート名 As String = "カラム名"
Const 固定値シート名 As String = "固定値"
Const テーブル名シート_テーブル列 As Long = 2
Const カラム名シート_テーブル列 As Long = 2
Const カラム名シート_カラム列 As Long = 6
Const カラム名シート_XXXXXXXX As Long = 11
Const IS_HEADER As Boolean = True
'IS_HEADERは、各シートの1行目がヘッダ情報である場合(=SQL文の生成が不要な場合)に True

'SQL文を生成
Public Sub CreateSql()

'変数宣言
Dim tableSheet As Worksheet
Dim columnSheet As Worksheet
Dim koteiSheet As Worksheet
Dim rng As Range
Dim r As Long
Dim r2 As Long
'Dim r3 As Long
Dim tableName As String
Dim columnNames As Collection
Dim difColumn As String
Dim columnName As Variant
'Dim intNo As Integer
'Dim strFileName As String
Dim countColumn As String
Dim compareColumn As String

'---------------------------------------
'サンプル①フォーマット読込処理
'---------------------------------------
'Const F_PATH = "c:\temp\testFormat.txt"
'Dim f_num As Integer  ' ファイル番号
'Dim rcd As String  ' テキストファイルの1行分文字列(1レコード)
'Dim strSQL As String
'f_num = FreeFile
'Open F_PATH For Input As f_num
'Do Until EOF(f_num)
' Line Input #f_num, rcd
'Debug.Print rcd
'Loop
'Close f_num

'---------------------------------------
'サンプル①'フォーマット読込処理(http://www.tipsfound.com/vba/04001)
'---------------------------------------
Const F_PATH = "c:\temp\testFormat.txt"
Dim f_num As Integer  ' ファイル番号
Dim rcd As String  ' テキストファイルの1行分文字列(1レコード)
Dim strSQL As String
f_num = FreeFile
Open F_PATH For Input As f_num
Do Until EOF(f_num)
Line Input #f_num, rcd
strSQL = strSQL + vbCrLf & rcd
'Debug.Print rcd
Loop
'Debug.Print strSQL
Close f_num

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

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

strSQL = Replace(strSQL, "{tableName}", tableName)
Debug.Print tableName
'---------------------------------------
'テーブル名(論理名)を取得する処理
'---------------------------------------
'【TODO】論理名取得の処理

'【TODO】{tableName_JP}をテーブル名シートより取得した論理名格納変数に置き換え
'---------------------------------------
'サンプル②固定値部分の置き換え処理(http://www.moug.net/tech/exvba/0050117.html)
'---------------------------------------
'①{INSTANCENAME}を固定値シートの値に置き換える
strSQL = Replace(strSQL, "{INSTANCENAME}", Range("B1").Value)

'②{PACKAGENAME}を固定値シートの値に置き換える
strSQL = Replace(strSQL, "{PACKAGENAME}", Range("B2").Value)

'③{DBLINK}を固定値シートの値に置き換える
strSQL = Replace(strSQL, "{DBLINKNAME}", Range("B3").Value)

'④{DATE}を実行日に置き換える
strSQL = Replace(strSQL, "{DATE}", Format(Date, "yyyymmdd"))

'⑤{NAME}を固定値シートの値に置き換える
strSQL = Replace(strSQL, "{NAME}", Range("B4").Value)

'⑥{PATH}を固定値シートの値に置き換える
strSQL = Replace(strSQL, "{PATH}", Range("B5").Value)

'{tableName}をTBL_Aに置き換える
'strSQL = Replace(strSQL, "{tableName}", "TBL_A")
Debug.Print strSQL
'---------------------------------------
'サンプル③カラム名シートよりカラム名取得処理
'---------------------------------------
' 全カラム取得
Set rng = columnSheet.Columns(カラム名シート_テーブル列)
Set columnNames = New Collection
For r2 = IIf(IS_HEADER, 3, 1) To rng.Cells(rng.Cells.Count).End(xlUp).Row
'XXXXXXX欄に"○"がついている場合【TODO】条件文を追加する
'If columnSheet.Cells(r3, カラム一覧シート_XXXXXXXXX目列).Text = "○" Then
'テーブル名が一致する場合
'コレクションに追加
If columnSheet.Cells(r2, カラム名シート_テーブル列).Text = tableName Then
columnNames.Add columnSheet.Cells(r2, カラム名シート_カラム列).Text
End If
Next
'初期化
compareColumn = ""
difColumn = ""
For Each columnName In columnNames
compareColumn = compareColumn & IIf(compareColumn = "", " ", ",") & CStr(columnName) '改行入れたい
Next
Debug.Print compareColumn
'⑥{columnName}をカラムシートの値に置き換える
strSQL = Replace(strSQL, "{columnName}", compareColumn)

Next
'---------------------------------------
'サンプル⑨SQL(ストアドプロシージャ)の出力処理
'---------------------------------------
Dim intNo As Integer
Dim strFileName As String
' ファイルの書き込み
strFileName = "C:\temp\sample.sql"  '出力ファイル名を日付時間で一意にする
Debug.Print strSQL
intNo = FileSystem.FreeFile()           ' フリーファイルNoを取得
'Open strFileName For Output As #intNo   ' 上書きモードでファイルをオープン
Open strFileName For Append As #intNo   ' 追加モードでファイルをオープン
'Print #intNo, "ABCDE"
'Print #intNo, "あいうえお"
Print #intNo, strSQL
Close #intNo                            ' ファイルをクローズ
End Sub

試したこと

ループ箇所変えたりしてみましたが、初期化みたいなことができないか模索してます。。。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

回答 2

checkベストアンサー

+1

1回目のループで置き換えしてしまっているためでしょうか?

正にこれだと思います。
ファイルから読み込んだものと、置き換える変数は別々で用意するとよいと思います。
以下、動作確認はしていませんが、修正案です。

~省略~

    Dim strTempSQL As String        ' ←追加
    Dim strSQL As String
    f_num = FreeFile
    Open F_PATH For Input As f_num
        ' ここではstrTempSQLを編集
    Do Until EOF(f_num)
        Line Input #f_num, rcd
        strTempSQL = strTempSQL + vbCrLf & rcd
    Loop
    Close f_num

~省略~

    ' 全テーブル走査
    Set rng = tableSheet.Columns(テーブル名シート_テーブル列)
    For r = IIf(IS_HEADER, 3, 1) To rng.Cells(rng.Cells.Count).End(xlUp).Row
        ' テンプレートのSQLを適用
        strSQL = strTempSQL      ' ←追加
        ' テーブル名取得
        tableName = tableSheet.Cells(r, テーブル名シート_テーブル列).Text

~省略~

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/10/31 12:32

    遅くなりましたが、回答ありがとうございました!!!

    キャンセル

+1

テンプレートとなるSQL文をファイルから取得し、それをもとに「テーブル名シート_テーブル列」に指定したテーブルの数だけSQLを量産したい、ということかと思います。

上記の場合、現在の処理ではさしあたり2つの問題があります。

問題点

問題①
テンプレートとして取得したstrSQLの内容を直接置換してしまっているため、2周目を行うときには既にstrSQL内にタグ文字は残っていないため空振りする。

問題②
{columnName}タグの置換を複数回行っているが、一回目で全ての{columnName}タグが置換されてしまうため2周目以降は空振りする。

対策

問題①について
テンプレートとして取得したSQL文を加工用の変数であるstrSQLに直接格納するのではなく、別変数(strTemplateなど)に保管しておきます。
ループの先頭でその変数(strTemplateなど)から加工用の変数strSQLに値をセットしなおしてあげることで対応できると思います。

問題②について
カラム名のタグがテンプレートにどのように記述されているのかわかりませんが、例えば

Select
    {columnName}
  , {columnName}
  , {columnName}
From {tableName};


のようなテンプレートだとすると、最初のカラム名の置換で{columnName}COL_1に置換した際に

Select
    COL_1
  , COL_1
  , COL_1
From {tableName};


とすべての{columnName}が置換されてしまい、2つ目以降のカラム名の置換の際にはすでに{columnName}タグがなくなっているため空振りすることになります。

これは{columnName}の置換を1カラムごとに行うのではなく、必要なカラムをすべて連結してから置換することで対応可能です。
※この場合テンプレート側も1回だけ記述するかたちになります。

例:{columnName}COL_1, COL_2, COL_3に置換する
テンプレート

Select 
    {columnName}
From {tableName};


置換結果

Select 
    COL_1, COL_2, COL_3
From {tableName};


となります。

推測込みのアドバイスなのではずしているかもしれませんが、参考になれば幸いです。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2017/10/24 15:21

    問題点②はネストを見間違えていたようで、すでに連結で置換しているようですね。
    失礼しましたm(__)m

    キャンセル

  • 2017/10/31 12:32

    いえいえ、回答ありがとうございました!

    キャンセル

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

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

関連した質問

  • 解決済

    【マクロ】検索に一致した条件とその下のセルを抽出

    エクセルマクロで質問です。 マクロを学びたいため、例のマクロを教えていただきたいです。 マンションのデータをまとめたいため、スーモのサイトからエクセルにコピペしました。 元データ

  • 解決済

    エクセルのセルに入っている数字とその座標を出力するマクロ(VBA)

    シート上の指定したセルのアドレスを(1,A)→(1,1),(1,B)→(1,2)...というふうに (列番号,行番号) の形にし、さらにセルに入っている数字をその後に付け足して(列

  • 解決済

    VBAでCSVから読み込んだものをすべてセルに入れる

    csvを読み込んだものを配列にし、セルの範囲を指定しないで入れたいのですがどうしたらよろしいでしょうか? 「Range("A10:D10") = tmp」このようにセルの範囲を指

  • 解決済

    エクセル VBAのソートに関して

    エクセルのVBAに関して質問させて頂きます。 使用しているエクセルは2003です。 現在下記の動作をさせるものを作成したいのですが、 効率のいい方法が思いつきません。

  • 解決済

    エクセル VBA  同一グラフ内で範囲が異なっているグラフ範囲を同じにしたい

    いつもお世話になっております。 エクセルのグラフの範囲一括変更について教えてください。  以前、グラフ範囲を一括で変更かける方法をマクロにて伝授していただいたのですが  そのマク

  • 解決済

    Excel VBA Withステートメントの使い方

    前提・実現したいこと エクセルのSheet6にあるセルのうち、 Like で指定した文字を含まないセルを行ごと削除するマクロを作成しています。 発生している問題・エラーメッ

  • 解決済

    VBA高速化について

    20個のエクセルファイルを読み込み、特定のシートにあるテーブルから特定の値を探し出し、その右横にあるセルの値を取り出します。 集計用のエクセルのテーブルでも、同じ特定の値をテーブル

  • 解決済

    VBAマクロでselectSQLを作成

    前提・実現したいこと エクセルにテーブル名一覧の記載があるシートとカラム一覧の記載があるシートがあります。 カラム一覧のシートには対応するテーブルも記載があり、そのカラムがどのテー

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

  • SQL

    2394questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • VBA

    1802questions

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

  • Excel

    1539questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

  • マクロ

    227questions

    定義された処理手続きに応じて、どのような一連の処理を行うのかを特定させるルールをマクロと呼びます。

  • ループ

    53questions

    ループとは、プログラミングにおいて、条件に合致している間、複数回繰り返し実行される箇所や、その制御構造を指します