Office中国论坛/Access中国论坛

标题: 请大家帮忙看个VBA编程. [打印本页]

作者: daly_0204 时间: 2008-9-6 13:28
标题: 请大家帮忙看个VBA编程.
我希望在同一个表格中的A sheet 查询 B sheet中的内容,程序编写如下:

Private Sub CommandButton1_Click()

Dim cnn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim strsq1 As String, str1 As String, i As Long
Dim A As String
Dim B As String
Dim C As String

On Error Resume Next

Set cnn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.recordset")

Set A = ThisWorkbook.ActiveCell.Value

Set B = A.Offset(0, 9)

With cnn
.CBom筛选数据";Uid=De56;Trusted_Connection=Yes;"
.Open
End With

strsq1 = "select 物料,数量,项目,筛选标准,损耗,客供量 from " & Bom筛选数据 & " where 物料 Like '%" & A & "%'"

rs.Open strsq1, cnn, adOpenKeyset, adLockOptimistic

i = 4
Set C = ThisWorkbook.Worksheets(Bom筛选数据)

C.Cells(2, 2) = rs.Fields("物料")
C.Cells(2, 3) = rs.Fields("数量")
Do While Not rs.EOF
C.Cells(i, 1) = rs.Fields("项目")
C.Cells(i, 2) = rs.Fields("筛选标准")
C.Cells(i, 3) = rs.Fields("损耗")
C.Cells(i, 4) = rs.Fields("客供量")

rs.MoveNext
i = i + 1
Loop
rs.Close
End Sub
我的问题是:
1.运行时会提醒:"用户定义类型未定义".(错误出现的语句为"Dim cnn As ADODB.Connection") ,什么原因啊?

2..CBom筛选数据";Uid=De56;Trusted_Connection=Yes;"
这句语句中的UID指什么?

新手编程,请高手帮我看一下这个程序有何问题?谢谢!

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)