想出一个办法:
Sub nawong() Dim Conn As New ADODB.Connection Dim Rst As New ADODB.Recordset Dim DbPath As String On Error GoTo 11 Workbooks.Open Filename:=ThisWorkbook.Path & "\sj.xls", Password:="3234" '先把密码文件打开 Windows("xiehong2.xls").Activate '切回原工作簿 DbPath = "provider=microsoft.jet.oledb.4.0;extended properties='Excel 8.0;HDR=YES';data source=sj.xls" '使用相对路径 Conn.Open DbPath For i = 3 To [a100].End(xlUp).Row dsdf = Cells(i, 2).Text Set Rst = Conn.Execute("SELECT * FROM [sj$] Where 代碼 =" & dsdf) Do While Not Rst.EOF Cells(i, 8) = Rst.Fields(3).Value Cells(i, 9) = Rst.Fields(2).Value Exit Do Loop Next MsgBox "Ok !" Workbooks("sj.xls").Close 'ADO 结束,关闭密码文件 Exit Sub 11 MsgBox Err.Description End Sub |