wilsonwilson4
New Member
- Joined
- Jun 25, 2015
- Messages
- 13
Hi All,
I am pretty new with VBA. I am just wondering if there is anyway to create macro VBA to create SQL query DELETE Statement?
I just know how to use formula but sometimes the formula needs to be fixed depending on the header.
="DELETE FROM TABLE NAME WHERE "&$A$1&"= '"&A2&"' AND "&$B$1&"= '"&B2&"'"
There is vba existing already for below Insert but not sure how to modify it.
I am pretty new with VBA. I am just wondering if there is anyway to create macro VBA to create SQL query DELETE Statement?
I just know how to use formula but sometimes the formula needs to be fixed depending on the header.
="DELETE FROM TABLE NAME WHERE "&$A$1&"= '"&A2&"' AND "&$B$1&"= '"&B2&"'"
There is vba existing already for below Insert but not sure how to modify it.
VBA Code:
Sub GenerateSql()
Const DataTab = "Data"
Const ControlTab = "Control"
Dim szDbName As String, szTableName As String, szLastCol As String, szLastColumn As String, iLastRow As Integer
Dim iColumnCount As Integer, iRowCount As Integer
Dim rCols As Range, rData As Range, szCols As String, szData As String
Dim szSql As String, szSqlRowPreface As String, szSqlRow As String, szSqlCols As String, szSqlDataRow As String
Dim bSettings_ExtraLines As Boolean, bSettings_NoEscape As Boolean
szDbName = Trim(Sheets(ControlTab).Range("C3").Value)
szTableName = Trim(Sheets(ControlTab).Range("C4").Value)
szLastColumn = Trim(Sheets(ControlTab).Range("C6").Value)
iLastRow = Trim(Sheets(ControlTab).Range("C7").Value)
iColumnCount = 0
iRowCount = 0
' Handle settings
If Sheets(ControlTab).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
bSettings_ExtraLines = True
Else
bSettings_ExtraLines = False
End If
If Sheets(ControlTab).Shapes("Check Box 3").OLEFormat.Object.Value = 1 Then
bSettings_NoEscape = True
Else
bSettings_NoEscape = False
End If
' Build ranges
szCols = "A1:" & szLastColumn & "1"
szData = "A2:" & szLastColumn & iLastRow
Set rCols = Sheets(DataTab).Range(szCols)
Set rData = Sheets(DataTab).Range(szData)
CopyTextToClipboard ("Error, import failed") ' In case we encounter problems
' Build insert, destination and columns:
For Each col In rCols.Columns
szSqlCols = szSqlCols + (col.Value & "= ")
iColumnCount = iColumnCount + 1
Next col
szSqlCols = Left(szSqlCols, Len(szSqlCols) - 1) ' remove trailing comma
szSqlRowPreface = "DELETE FROM " & szDbName & szTableName & " WHERE " & szSqlCols & ""
' Build values
For Each rw In rData.Rows
iRowCount = iRowCount + 1
szSqlDataRow = "" ' Reset this for each row
For Each col In rw.Columns
szSqlDataRow = szSqlDataRow + ("'" & FormatSql(col.Value, bSettings_NoEscape) & "',")
Next col
szSqlDataRow = Left(szSqlDataRow, Len(szSqlDataRow) - 1) ' remove trailing comma
szSql = szSql + szSqlRowPreface & szSqlDataRow & ")" & vbNewLine
If bSettings_ExtraLines = True Then
szSql = szSql & vbNewLine
End If
Next rw
' Add to clipboard and complete:
CopyTextToClipboard (szSql)
MsgBox ("Completed with " & iColumnCount & " data columns and " & iRowCount & " rows." & vbNewLine & vbNewLine & _
"Results are in the clipboard.")
End Sub
Last edited by a moderator: