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

Question

###前提・実現したいこと
こちらで何度か質問させていただき設定シートとテキストの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

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

Answer 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

～省略～

Comments

[chanrina0920]

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

Answer 2

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

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

問題点

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

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

対策

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

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

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

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

Select
    COL_1
  , COL_1
  , COL_1
From {tableName};

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

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

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

Select 
    {columnName}
From {tableName};

置換結果

Select 
    COL_1, COL_2, COL_3
From {tableName};

となります。

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

Comments

[jawa]

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

[chanrina0920]

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