(11)工作簿作为数据库 参考

欢迎大家来到IT世界,在知识的湖畔探索吧!

ADO-SQL(注意版本, Propertities=’Excel 8.0′ 指的是Excel2003版)

Set cnn=CreateObject(“adodb.connection”)

cnn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=’Excel 8.0;hdr=no’;Data Source=” & “路径/文件名”

Sql=sql 语句

Sql=”SELECT * FROM [表名$区域]”

Sql=”SELECT * FROM [” & 变量 & “$区域]”

Sql=”SELECT 字段名1 FROM [表名$区域]”

Sql=”SELECT 字段名1,SUM(字段名2) FROM [表名$区域] GROUP BY 字段名1″

Sql=”SELECT * FROM [表名$区域] where 字段名1=条件”

[a1].CopyFromRecordset cnn.Execute(Sql)

cnn.Close

Set cnn=Nothing

Excel连接到MSSQL的实例(自己使用的):

–创建连接对象

Set Conn = CreateObject(“ADODB.Connection”)

Set Rst = CreateObject(“ADODB.Recordset”)

Conn.Open “DRIVER=SQL Server;SERVER=192.168.100.6;DATABASE=TestData;UID=sa;password=123456”

–构建查询语句

sqlStr = “SELECT DISTINCT FSN FROM dbo.t_easy_TestDataV1 WHERE FTotalResault = ‘1’ “

sqlStr = sqlStr & ” and (CONVERT(varchar(100), FCreateDate, 23) > CONVERT(varchar(100), DATEADD(day, -” & days & “, GETDATE()), 23))”

–操作数据

If Range(“J2”) <> “” Then

sqlStr = sqlStr & ” and (FItemNo = ‘” & Range(“J2”) & “‘)”

End If

If Range(“J9”) <> “” Then

sqlStr = sqlStr & ” and (FWorkBillNo = ‘” & Range(“J9”) & “‘)”

End If

If Range(“J14”) <> “” Then

sqlStr = sqlStr & ” and (FMachineID = ‘” & Range(“J14”) & “‘”

End If

If Range(“J16”) <> “” Then

sqlStr = sqlStr & ” or FMachineID = ‘” & Range(“J14”) & “‘”

End If

If Range(“J18”) <> “” Then

sqlStr = sqlStr & ” or FMachineID = ‘” & Range(“J18”) & “‘”

End If

If Range(“J14”) <> “” Or Range(“J14”) <> “” Or Range(“J14”) <> “” Then

sqlStr = sqlStr & “)”

End If

‘Debug.Print sqlStr

‘CONVERT(varchar(100), DATEADD(day, -7, GETDATE()), 23))

‘CONVERT(varchar(100), DATEADD(hh, -10, GETDATE()), 20))

Rst.Open sqlStr, Conn

Range(“A3:B1000000”).ClearContents

Range(“A3”).CopyFromRecordset Conn.Execute(sqlStr)

r = Range(“a1000000”).End(xlUp).Row ‘获取最大行号

Debug.Print r

Range(“B3:B” & r) = “OK”

–关闭记录集关闭连接

Rst.Close

Conn.Close