VBA DELETE Statements in EXCEL

wilsonwilson4

New Member
Joined
Jun 25, 2015
Messages
11
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.

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:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

kennypete

Active Member
Joined
Apr 19, 2008
Messages
260
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Check out my 11 March post in Index Match Multiple Criteria VBA which has a simple SQL query from Excel VBA illustrated.
I'm fairly sure you cannot do the DELETE query you want to on Excel data, however; it's going to return:
1584769928905.png


A workaround may be to do an UPDATE query to the rows you want to delete and then loop the table to delete those rows? Something like the code below (modifying the example above in post 544423)

VBA Code:
Sub DeleteFoodv4()
    Dim cn As Object, rs As Object, sql As String
    Dim ui As String, lngPeriod As Long, strProduct As String
    ui = InputBox("Period and Product? (e.g. 1a)")
    lngPeriod = Int(Left(Trim(ui), 1))
    strProduct = Right(Trim(ui), Len(Trim(ui)) - 1)
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=D:\_Share\20200321\index-match-multiple(v.SQL&v.Dict)v2.1126717 - mod delete.xlsm" & _
            ";" & "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
        .Open
    End With
    sql = "UPDATE [Sheet1$] SET period=0, " & _
                                "product='Delete' WHERE period=" & lngPeriod & " and " & _
                                "product='" & strProduct & "'"
    Set rs = cn.Execute(sql)
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub

..which I tried this on the data involved in that post and it works. So modifying this to loop and delete the '0, DELETE' rows would be pretty easy from there.
 

wilsonwilson4

New Member
Joined
Jun 25, 2015
Messages
11
Check out my 11 March post in Index Match Multiple Criteria VBA which has a simple SQL query from Excel VBA illustrated.
I'm fairly sure you cannot do the DELETE query you want to on Excel data, however; it's going to return: View attachment 9437

A workaround may be to do an UPDATE query to the rows you want to delete and then loop the table to delete those rows? Something like the code below (modifying the example above in post 544423)

VBA Code:
Sub DeleteFoodv4()
    Dim cn As Object, rs As Object, sql As String
    Dim ui As String, lngPeriod As Long, strProduct As String
    ui = InputBox("Period and Product? (e.g. 1a)")
    lngPeriod = Int(Left(Trim(ui), 1))
    strProduct = Right(Trim(ui), Len(Trim(ui)) - 1)
    Set cn = CreateObject("ADODB.Connection")
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = "Data Source=D:\_Share\20200321\index-match-multiple(v.SQL&v.Dict)v2.1126717 - mod delete.xlsm" & _
            ";" & "Extended Properties=""Excel 12.0 Macro;HDR=YES"";"
        .Open
    End With
    sql = "UPDATE [Sheet1$] SET period=0, " & _
                                "product='Delete' WHERE period=" & lngPeriod & " and " & _
                                "product='" & strProduct & "'"
    Set rs = cn.Execute(sql)
    cn.Close
    Set cn = Nothing
    Set rs = Nothing
End Sub

..which I tried this on the data involved in that post and it works. So modifying this to loop and delete the '0, DELETE' rows would be pretty easy from there.
Thank you for this. it seems this vba is automatically run from behind. Is it possible to open the result of the sql statements first?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,734
Messages
5,638,052
Members
417,001
Latest member
MSteel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top