SQL动态路径

Public oCNN As New ADODB.Connection
Public oRST As New ADODB.Recordset
Public oRSTemp As ADODB.Recordset
Public iLastRow As Long, iLastCol As Long

'ThisWorkBook模块
Private Sub Workbook_Open()
    oCNN.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\jihua.mdb;"
    Application.EnableEvents = False
    oRST.Open "select * from 技术开发部", oCNN, adOpenKeyset, adLockPessimistic
    Sheet1.[A2].CopyFromRecordset oRST
    iLastRow = oRST.RecordCount + 1
    iLastCol = oRST.Fields.Count
    Application.EnableEvents = True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    oCNN.Close
End Sub

'Sheet1模块
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Rows.Count = 1 And Target.Columns.Count = 1 And Target.Row < iLastRow _
       And Target.Row > 1 And Target.Column < iLastCol Then
        With oRST
            .Move Target.Row - 2, adBookmarkFirst
            .Fields(Target.Column) = CStr(Target.Value)
            .Update
        End With
    End If
End Sub

'Sheet2模块
Private Sub Worksheet_Activate()
    Sheet2.[DATASOR2].ClearContents
    Set oRSTemp = oRST.Clone()
    oRSTemp.Requery
    Sheet2.[A2].CopyFromRecordset oRSTemp
    oRSTemp.Close
End Sub

附件地址 http://www.excelpx.com/forum.php?mod=redirect&goto=findpost&ptid=157001&pid=2444337&fromuid=49405