作者:乔山办公网日期:
返回目录:excel表格制作
例如简单的从zhidaosheet1表中的数据取到表回二:
Sub 简单查询()
Set cn = CreateObject("adodb.connection")
Set rs = CreateObject("adodb.recordset")
cn.Open "provider=microsoft.jet.oledb.4.0;Extended Properties='Excel 8.0';data source=" & ThisWorkbook.FullName
Sql = "select * from [sheet1$]"
Sheets(2).[A2].CopyFromRecordset cn.Execute(Sql)
MsgBox "取数据成功答"
Sheets("sheet2").Select
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
给个示知例
Sub 去重()
Dim cnn, SQL$
Set cnn = CreateObject("adodb.connection")
cnn.Open "Provider=Microsoft.jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.FullName
SQL = "SELECT DISTINCT [aac001],[aac003],部门代码道,但未编号,基数,个人,单位,金额 FROM [明细专$A3:H65536] "
Range("A2:I65536").ClearContents
Range("A2").CopyFromRecordset cnn.Execute(SQL)
cnn.Close
Set cnn = Nothing
End Sub
你可以参考一下。属
sub test()
Dim Conn As New ADODB.Connection
Dim strConn As String
Dim strSQL As String
Dim rs As New ADODB.Recordset
strConn = "Provider=sqloledb;Server=192.168.1.111;Database=db2014;Uid=用户百名度;Pwd=密码知;"
Conn.Open strConn
Max_row = Range("A1").CurrentRegion.Rows.Count
for i=1 to Max_row
strSQL = "select name from sales2014 where id='" & range("A" & i).value & "'"
Conn.Execute strSQL
rs.Open strSQL, Conn, 1, 1
range("B" & i).value= rs(0)
rs.Close
Set rs = Nothing
next i
'关闭道数回据库答
Conn.Close
end sub
单纯的如上的语句是不能执行的,你必须配上一系列的打开数据库的操作(譬如vb),而后编译成exe文件,链接到任何地方都能执行。