学步园

工作中处理EXCEL电子表格数据时，可能经常需要用工作薄的一个表的数据去更新另外一个表，手工处理是最笨的方法。下面演示的是VBA编写自定义函数，用SQL的方式，通过UPDATE查询，更新表格数据，大大改善效率。

Option Explicit

Dim ExcelDB As New ADODB.Connection
Dim rst1 As New ADODB.Recordset

Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command

' 调用这个过程来更新电子表
Sub UpdateTable()
  Dim wkBook As String
  wkBook = ThisWorkbook.FullName
  Dim sql As String
 
 
  If ExcelConnect(wkBook) Then

    '  更改SQL语句，就可以用几个表来更新一个表
    'sql = "UPDATE color_table SET color_name = 'red@' WHERE id_num = '123'"
    sql = "update [sheet2$] set 序号='0' "
    MsgBox sql
    If Not WQry(rst1, ExcelDB, sql) Then Exit Sub
  End If
End Sub

' 以数据库方式访问电子表格

Function ExcelConnect(dsnWkBook As String) As Boolean

  On Error GoTo ErrorHandler:  ' Enable error-handling routine.
  If ExcelDB.State = adStateOpen Then
    ExcelDB.Close
    Set ExcelDB = Nothing
  End If
  If Len(dsnWkBook) > 0 Then
    ExcelDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & dsnWkBook & ";" & _
                 "Extended Properties=Excel 8.0;"
    ExcelConnect = True
  End If
  Exit Function       ' Exit to avoid handler.
ErrorHandler:    ' Error-handling routine.
  ExcelConnect = False
End Function

' 查询函数

Function WQry(gRST As ADODB.Recordset, xDB, qS As String) As Boolean

  If gRST.State = adStateOpen Then
    gRST.Close
  End If

  On Error GoTo ErrorHandler  ' Enable error-handling routine.

  gRST.Open qS, xDB

  WQry = True

  Exit Function     ' Exit to avoid handler.
ErrorHandler:    ' Error-handling routine.

  Err.Clear
  WQry = False
End Function