'
' 전병관이 만든 거다. 웃훗. 2008-07-10
'
'
Public Sub makeInsertQuery(tableName , schema As String)
Dim schema As String
Dim sb As String
Dim i, j, si, sj As Integer
si = 5
sj = 2
i = 0
j = 0
Dim fieldType As String
Dim colCount, rowCount As Integer
'입력되는 데이타의 시작부분.
colCount = Range("B6", Range("B6").End(xlToRight)).Columns.Count
On Error GoTo WHAT_THE_ERROR
rowCount = Range("B7", Range("B7").End(xlDown)).Rows.Count
WHAT_THE_ERROR:
If rowCount = 0 And Range("B7").Value <> "" Then
rowCount = 1
End If
Dim fieldValue As String
sb = ""
Dim fieldNames As String
'항목명 구하기
For j = 0 To colCount - 1
If j = 0 Then
fieldNames = Cells(si + 1, sj + j).Value
Else
fieldNames = fieldNames & ", " & Cells(si + 1, sj + j).Value
End If
Next
For i = 0 To rowCount - 1
For j = 0 To colCount - 1
fieldType = Cells(si, sj + j).Value
fieldValue = Cells(si + 2 + i, sj + j).Value
If Trim(fieldValue) = "" Then
'fieldValue = Trim(fieldValue)
End If
If fieldType = "문자" Then
fieldValue = "'" & fieldValue & "'"
ElseIf fieldType = "숫자" Then
End If
If j > 0 Then
sb = sb & ", " & fieldValue
Else
sb = fieldValue
End If
Next
Cells(si + 2 + i, 1).Value = "insert into " & schema & "." & tableName & " ( " & fieldNames & " ) values ( " & sb & " ); "
Next
MsgBox "완료"
End Sub
Public Sub makeDeleteQuery(tableName , schema As String)
Dim schema As String
Dim sb As String
Dim i, j, si, sj As Integer
si = 5
sj = 2
i = 0
j = 0
Dim fieldType As String
Dim colCount, rowCount As Integer
colCount = Range("B6", Range("B6").End(xlToRight)).Columns.Count
rowCount = Range("B7", Range("B7").End(xlDown)).Rows.Count
Dim fieldValue As String
sb = ""
Dim fieldNames As String
For j = 0 To colCount - 1
If j = 0 Then
fieldNames = Cells(si + 1, sj + j).Value
Else
fieldNames = fieldNames & ", " & Cells(si + 1, sj + j).Value
End If
Next
Dim fieldName, str As String
For i = 0 To rowCount - 1
For j = 0 To colCount - 1
If j > 3 Then
Exit For
End If
fieldType = Cells(si, sj + j).Value
fieldName = Cells(si + 1, sj + j).Value
fieldValue = Cells(si + 2 + i, sj + j).Value
If Trim(fieldValue) = "" Then
'fieldValue = Trim(fieldValue)
End If
If fieldType = "문자" Then
str = fieldName & "='" & fieldValue & "'"
ElseIf fieldType = "숫자" Then
str = fieldName & "=" & fieldValue & ""
End If
If j > 0 Then
sb = sb & " and " & str
Else
sb = str
End If
Next
Cells(si + 2 + i, 1).Value = "delete from " & schema & "." & tableName & " where " & sb & " ; "
Next
MsgBox "완료"
End Sub
' 전병관이 만든 거다. 웃훗. 2008-07-10
'
'
Public Sub makeInsertQuery(tableName , schema As String)
Dim schema As String
Dim sb As String
Dim i, j, si, sj As Integer
si = 5
sj = 2
i = 0
j = 0
Dim fieldType As String
Dim colCount, rowCount As Integer
'입력되는 데이타의 시작부분.
colCount = Range("B6", Range("B6").End(xlToRight)).Columns.Count
On Error GoTo WHAT_THE_ERROR
rowCount = Range("B7", Range("B7").End(xlDown)).Rows.Count
WHAT_THE_ERROR:
If rowCount = 0 And Range("B7").Value <> "" Then
rowCount = 1
End If
Dim fieldValue As String
sb = ""
Dim fieldNames As String
'항목명 구하기
For j = 0 To colCount - 1
If j = 0 Then
fieldNames = Cells(si + 1, sj + j).Value
Else
fieldNames = fieldNames & ", " & Cells(si + 1, sj + j).Value
End If
Next
For i = 0 To rowCount - 1
For j = 0 To colCount - 1
fieldType = Cells(si, sj + j).Value
fieldValue = Cells(si + 2 + i, sj + j).Value
If Trim(fieldValue) = "" Then
'fieldValue = Trim(fieldValue)
End If
If fieldType = "문자" Then
fieldValue = "'" & fieldValue & "'"
ElseIf fieldType = "숫자" Then
End If
If j > 0 Then
sb = sb & ", " & fieldValue
Else
sb = fieldValue
End If
Next
Cells(si + 2 + i, 1).Value = "insert into " & schema & "." & tableName & " ( " & fieldNames & " ) values ( " & sb & " ); "
Next
MsgBox "완료"
End Sub
Public Sub makeDeleteQuery(tableName , schema As String)
Dim schema As String
Dim sb As String
Dim i, j, si, sj As Integer
si = 5
sj = 2
i = 0
j = 0
Dim fieldType As String
Dim colCount, rowCount As Integer
colCount = Range("B6", Range("B6").End(xlToRight)).Columns.Count
rowCount = Range("B7", Range("B7").End(xlDown)).Rows.Count
Dim fieldValue As String
sb = ""
Dim fieldNames As String
For j = 0 To colCount - 1
If j = 0 Then
fieldNames = Cells(si + 1, sj + j).Value
Else
fieldNames = fieldNames & ", " & Cells(si + 1, sj + j).Value
End If
Next
Dim fieldName, str As String
For i = 0 To rowCount - 1
For j = 0 To colCount - 1
If j > 3 Then
Exit For
End If
fieldType = Cells(si, sj + j).Value
fieldName = Cells(si + 1, sj + j).Value
fieldValue = Cells(si + 2 + i, sj + j).Value
If Trim(fieldValue) = "" Then
'fieldValue = Trim(fieldValue)
End If
If fieldType = "문자" Then
str = fieldName & "='" & fieldValue & "'"
ElseIf fieldType = "숫자" Then
str = fieldName & "=" & fieldValue & ""
End If
If j > 0 Then
sb = sb & " and " & str
Else
sb = str
End If
Next
Cells(si + 2 + i, 1).Value = "delete from " & schema & "." & tableName & " where " & sb & " ; "
Next
MsgBox "완료"
End Sub
