Useful Code

useful code

Operation verification environment Microsoft® Excel® for Microsoft 365 MSO (version 2301 build 16.0.16026.20196) 32-bit Last updated Sunday, March 12, 2023 0:58:13

Insert query generation function

This code is an Insert query generation function that adds records to a MySQL Table from Excel VBA. Pass the table name, collection type column name, and corresponding value as arguments. Inside the function, we use the argument columns and values ​​to create an Insert statement.

Function CreateInsertQuery(table_name as String, cols As Collection, vals As Collection)

    Dim idx As Integer

    Dim column_names As String
    For idx = 1 To cols.Count
      If idx > 1 Then
        column_names = column_names & ","
      End If
      column_names = column_names & ("`" & cols(idx) & "`")
    Next
    
    
    Dim column_values As String
    For idx = 1 To vals.Count
      If idx > 1 Then
        column_values = column_values & ","
      End If
      column_values = column_values & ("`" & vals(idx) & "`")
    Next
    
    Dim query As String
    query = "insert into table_name (column_names) values (column_values);"
    query = Replace(query, "table_name", table_name)
    query = Replace(query, "column_names", column_names)
    query = Replace(query, "column_values", column_values)
    
    CreateUpdateQuery = query

End Function

To call the above query generation function, write code like the following.

Private Sub Sample()

    Dim query As String

    Dim cols As Collection
    Set cols = New Collection
    
    Dim vals As Collection
    Set vals = New Collection

    Dim table_name as String
    table_name = "tablename"

    cols.Add "Col1"
    vals.Add "Val1"
    
    cols.Add "Col2"
    vals.Add "Val2"
    
    cols.Add "Col3"
    vals.Add "Val3"

    query = CreateInsertQuery(table_name, cols, vals)

End Sub

Update query generation function

This function is an Update query generation function that updates a MySQL Table from Excel VBA. When you run the sample below, you will get the following result:

update tablename set `Col1`='Val1',`Col2`='Val2',`Col3`='Val3' where `whereCol1`='whereVal1' and`whereCol2`='whereVal2' and`whereCol3`='whereVal3';

Pass the table name, Where condition, collection type column name, and corresponding value as function arguments. Inside the function, we use the argument columns and values ​​to create an Update statement.

Function CreateUpdateQuery(table_name As String, cols As Collection, vals As Collection, whereCols As Collection, whereVals As Collection)

    Dim idx As Integer

    Dim cols_vals As String
    For idx = 1 To cols.Count
      If idx > 1 Then
        cols_vals = cols_vals & ","
      End If
      cols_vals = cols_vals & "`" & cols(idx) & "`='" & vals(idx) & "'"
    Next
    
    Dim where_joken As String
    For idx = 1 To whereCols.Count
      If idx > 1 Then
        where_joken = where_joken & " and"
      End If
      where_joken = where_joken & "`" & whereCols(idx) & "`='" & whereVals(idx) & "'"
    Next
    
    Dim query As String
    query = "update table_name set cols_vals where where_joken;"
    query = Replace(query, "table_name", table_name)
    query = Replace(query, "cols_vals", cols_vals)
    query = Replace(query, "where_joken", where_joken)
    
    CreateUpdateQuery = query

End Function

To call the above query generation function, write code like the following.

Sub UpdateSample()
    Dim query As String

    Dim table_name As String
    table_name = "tablename"
    
    Dim cols As Collection
    Set cols = New Collection
    
    Dim vals As Collection
    Set vals = New Collection

    Dim whereCols As Collection
    Set whereCols = New Collection
    
    Dim whereVals As Collection
    Set whereVals = New Collection
    
    cols.Add "Col1"
    vals.Add "Val1"
    
    cols.Add "Col2"
    vals.Add "Val2"
    
    cols.Add "Col3"
    vals.Add "Val3"
    
    whereCols.Add "whereCol1"
    whereVals.Add "whereVal1"
    
    whereCols.Add "whereCol2"
    whereVals.Add "whereVal2"
    
    whereCols.Add "whereCol3"
    whereVals.Add "whereVal3"

    query = CreateUpdateQuery(table_name, cols, vals, whereCols, whereVals)

End Sub

Select query generation function

This function is a Select query generation function that searches MySQL Table from Excel VBA. When you run the sample below, you will get the following result:

select * from tablename where `Col1`='Val1' and `Col2`='Val2' and `Col3`='Val3';

Pass the table name, collection type column name, and corresponding value as function arguments. Inside the function, we use the argument columns and values ​​to create a Select statement.

Function CreateSelectQuery(table_name As String, cols As Collection, vals As Collection)

    Dim idx As Integer

    Dim cols_vals As String
    For idx = 1 To cols.Count
      If idx > 1 Then
        cols_vals = cols_vals & " and "
      End If
      cols_vals = cols_vals & "`" & cols(idx) & "`='" & vals(idx) & "'"
    Next
    
    Dim query As String
    query = "select * from table_name where cols_vals;"
    query = Replace(query, "table_name", table_name)
    query = Replace(query, "cols_vals", cols_vals)
    
    CreateSelectQuery = query

End Function

To call the above query generation function, write code like the following.

Sub UpdateSample()
    Dim query As String
    Dim cols As Collection
    Set cols = New Collection
    
    Dim vals As Collection
    Set vals = New Collection
    
    Dim table_name As String
    table_name = "tablename"
    
    cols.Add "Col1"
    vals.Add "Val1"
    
    cols.Add "Col2"
    vals.Add "Val2"
    
    cols.Add "Col3"
    vals.Add "Val3"
    
    query = CreateSelectQuery(table_name, cols, vals)
    
End Sub