エクセルVBAで、INSERT文を生成

エクセルのデータよりDBにINSERTするためのINSERT文を生成するマクロを作っています。
INSERT文生成のVBAはできたのですが、少し仕様がいつもと違い困っております。皆様のお知恵を拝借できればと思っております。

【仕様】
1.1行ずつセルに文字列があるかどうかチェックする
2.セルに文字列が入ってる場合、例えば"z_1"が入ってる場合、一番左端のセル(A列)に記載されているID、例えば"001"、とともにinsert文を生成。
→ INSERT INTO TBL名 id,code VALUES ('001','z_1')
3.1行には複数文字列が入っている場合があるので、文字列がある限りinsert文を生成。
4.文字列がないと判明した場合、次の行へ移動。

【具体例】
A1のセルにID：001
1行目の行、C1のセルに"z_1"、F1のセルに"j_5"、M1のセルに"c_3"などといった文字列があります。
この行のセルを一個ずつチェックして文字列があると、A1のIDを参照して、
INSERT INTO TBL名 id,code VALUES ('001','z_1')
などといったインサート文を生成。
文字列がある限りINSERT文を生成します。

お知恵を拝借できれば幸いです。

No.2 ベストアンサー 回答者： kenpon24 回答日時： 2008/07/09 14:05

Functionで結果を返したいなら、配列を返さないとできませんね。

insert関数で配列を返すようにして、呼び出す関数でINSERT文を出力していく
サンプルを書いてみました。

「INSERT文出力」っていうシートがあること、IDがA列、文字列がB列から始まっていることを前提とします。
B列から文字列を読み始めて、C,D..と文字列があるだけA列のIDと結合し、INSERT文を
INSERT文出力シートに出力するようにしました。

Sub test() '上位の関数
Dim SQL As Variant
Dim i As Long
Dim j As Long
Dim Row As Long

Const InputSheet As String = "INSERT文出力" '出力シート名

Row = 1
Worksheets(InputSheet).Range("A" & Row) = ActiveSheet.Name
Row = Row + 1

For i = 1 To Range("A65536").End(xlUp).Row
SQL = insert(i)

Worksheets(InputSheet).Range("A" & Row) = i & "行目"
Row = Row + 1

For j = 1 To UBound(SQL)
Worksheets(InputSheet).Range("A" & Row) = SQL(j)
Row = Row + 1
Next j
Next i

Worksheets(InputSheet).Activate
End Sub

'配列を返すように変更
Function insert(i) As Variant

Dim strSQL() As Variant
Dim nCol As Integer
Dim nColEnd As Integer

Const FirstCol As Integer = 2 'ID:xxxが書かれている列番号

'右端の列番号を格納
nColEnd = Cells(i, 256).End(xlToLeft).Column
'配列を切る
If nColEnd < FirstCol Then
ReDim strSQL(1)
Else
ReDim strSQL(1 To nColEnd - FirstCol + 1)
End If

For nCol = FirstCol To FirstCol + UBound(strSQL) - 1
'insert into id,code values( 欲しい文と違えば修正すること
insertsql = "insert into " & Cells(i, 1) & "," & Cells(i, nCol) & " values("

If Cells(i, nCol) <> "" Then
'insert into id,code values('id','code');　欲しい文と違えば修正すること
strSQL(nCol - FirstCol + 1) = insertsql & "'" & Cells(i, 1) & "','" & Cells(i, nCol) & "');"
Else
'insert into id,code values(NULL);
strSQL(nCol - FirstCol + 1) = insertsql & "NULL" & ");"
End If
Next nCol

insert = strSQL
End Function

この回答へのお礼

kenpon24さん

まさにこの仕様です。大変ありがとうございました。
kenpon24さんのコードの記述大変参考になります。
また、私の勉強不足さも痛感させられました。

ありがとうございました!!

お礼日時：2008/07/09 15:06

No.1 回答者： kenpon24 回答日時： 2008/07/09 11:32

そんなに難しいコードとも思えませんが。

。
どのようなコードを書いてどんな結果が出てきて、
どこが仕様と違うからどう直したいんですか？

を書いていただけるとすぐ答えが出てくると思いますよ。

この回答への補足

kenpon24さん

なにぶん、VBA初心者でもあり勉強不足で申し訳ございません。
結果としては、
どこのセルにでもいいので、
INSERT INTO TBL名 id,code VALUES ('001','z_1')といった
INSERT文字列を生成して表示させたいと思っております。

今までは、1行は全て一つのINSERT文で問題ありませんでした。
下記の関数を実行すればよかったのですが、1行に複数文字列がある場合、例えばN個ある場合は、N回インサート文を生成しないといけない、という箇所につまずいています。

【参考】
Function insert(i)
insertsql = "insert into " & Range("B1") & " values("
strvalue = Cells(i, 2)

If strvalue <> "" Then
strsql = insertsql & "'" & Cells(i, 2) & "'"
Else
strsql = insertsql & "NULL"
End If

For j = 3 To Colcount
strvalue = Cells(i, j)
If strvalue <> "" Then
strsql = strsql & ",'" & Cells(i, j) & "'"
Else
strsql = strsql & ",NULL"
End If
Next
strsql = strsql & ");"
insert = strsql
End Function

補足日時：2008/07/09 11:59