ADOでマスタ更新（SQL）｜ExcelマクロVBAサンプル集

最終更新日：2016-09-08

ADOでマスタ更新（SQL）

VBAでADOを使用し、マスターデータを更新します。

ADOではSQL文が必要になりますが、

ここではSQL文の詳細については説明を料略します。

自身のブックの、他シートを更新します。

シート「顧客マスタ」の、A列が顧客番号、B列が顧客名で、1行目が見出しになっているものとします。

Sub sample1()
　　Dim objCn As New ADODB.Connection
　　Dim strSQL As String

　　With objCn
　　　　.Provider = "Microsoft.Jet.OLEDB.4.0"
　　　　.Properties("Extended Properties") = "Excel 8.0"
　　　　.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name
　　End With
　　
　　strSQL = ""
　　strSQL = strSQL & " UPDATE [顧客マスタ2$]"
　　strSQL = strSQL & " SET"
　　strSQL = strSQL & " 顧客名 = 'お名前'"
　　strSQL = strSQL & " WHERE"
　　strSQL = strSQL & " 顧客番号 = 12345"
　　
　　objCn.Execute (strSQL)
　　
　　objCn.Close
　　Set objCn = Nothing
End Sub

※「参照設定」で「Microsoft ActiveX Data Objects 2.X Library」を追加して下さい。

※Excel2007以降
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0"
こちらを使用してください。

SQL文は、UPDATEになります。

SET 顧客名 = 'お名前'

顧客名のフィールドを、"お名前'"で更新します。

文字定数は、「'」シングルクォーテーションで囲みます。

WHERE 顧客番号 = 12345

これで対象のレコードを指定しています。

しかし、単に同一ブックなら、以下の方が、はるかに簡単で、処理速度も速いです。

Sub sample2()
　　Dim i As Long
　　On Error Resume Next
　　i = Application.WorksheetFunction.Match(12345, Range("A:A"), 0)
　　If i > 0 Then
　　　　Cells(i, 2) = "お名前"
　　End If
　　On Error GoTo 0
End Sub

または、Findメソッドを使うかでしょう。

エラー処理等は、適時対応して下さい。

普通は、これで良いですね。

ただ、これが他の開いていないブックだと、話が変わってくるでしょう。

その場合には、ADOでの更新も選択肢にはいってくると思います。

.Open ThisWorkbook.Path & "\" & ThisWorkbook.Name

この部分に、他の更新対象のブックのフルパスを指定すれば、それで更新できてしまいます。