便利なコード #
動作検証環境
Microsoft® Excel® for Microsoft 365 MSO (バージョン 2301 ビルド 16.0.16026.20196) 32 ビット
最終更新日
2023年3月12日 日曜日 0:58:13
Insert クエリ生成関数 #
このコードは、Excel VBA より MySQL の Table にレコードを追加する Insert クエリー生成関数です。 引数には、テーブル名とコレクション型の列名と対応する値を渡します。関数内部では引数の列と値を使って Insert 文を作成します。
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
上記クエリ生成関数を呼び出すには、次のようなコードを記述します。
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 クエリ生成関数 #
この関数は、Excel VBA より MySQL の Table を更新する Update クエリー生成関数です。 下記サンプルを実行すると、次の結果が得られます。
update tablename set `Col1`='Val1',`Col2`='Val2',`Col3`='Val3' where `whereCol1`='whereVal1' and`whereCol2`='whereVal2' and`whereCol3`='whereVal3';
関数引数には、テーブル名とWhere条件、コレクション型の列名と対応する値を渡します。関数内部では引数の列と値を使って Update 文を作成します。
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
上記クエリ生成関数を呼び出すには、次のようなコードを記述します。
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 クエリ生成関数 #
この関数は、Excel VBA より MySQL の Table を検索する Select クエリー生成関数です。 下記サンプルを実行すると、次の結果が得られます。
select * from tablename where `Col1`='Val1' and `Col2`='Val2' and `Col3`='Val3';
関数引数には、テーブル名とコレクション型の列名と対応する値を渡します。関数内部では引数の列と値を使って Select 文を作成します。
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
上記クエリ生成関数を呼び出すには、次のようなコードを記述します。
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