|
Option Explicit
Sub test1()
Dim j&, cnn As Object, rst As Object, strSQL$, strFileName$
Application.ScreenUpdating = False
Set cnn = CreateObject("ADODB.Connection")
strFileName = ThisWorkbook.FullName
Select Case Application.Version * 1
Case Is <= 11
cnn.Open "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & strFileName & ";Extended Properties='Excel 8.0;HDR=YES;IMEX=0'"
Case Is >= 12
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties='Excel 12.0;HDR=YES;IMEX=0'"
End Select
strSQL = "SELECT TOP 16 f1,null,null,null,f2 FROM [" & Worksheets(2).Name & "$b9:c] WHERE F1<>'业务招待费' and f1<>'研究费用' ORDER BY F2 DESC"
Set rst = cnn.Execute(strSQL)
With Worksheets(1)
With .Range("A2")
.Offset(1).CopyFromRecordset rst
End With
.Activate
End With
rst.Close: cnn.Close
Set rst = Nothing: Set cnn = Nothing
Application.ScreenUpdating = True
Beep
End Sub |
评分
-
1
查看全部评分
-
|