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

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