VBA 从金蝶K3里导出数据查询物料

Sub jskc10()

输入物料名称查料号 OK 2022-10-09

Worksheets("jskc").Select

Cells.Select

Worksheets("jskc").UsedRange.ClearContents

Dim aa As String

Dim prompt As String

prompt = "请输入第一查询特征：（例如：电阻）"

aa = InputBox(prompt)

Cells(1, 1) = aa

prompt = "请输入第二查询特征：（例如：100）"

bb = InputBox(prompt)

Cells(2, 1) = bb

Dim CN As Object

Dim rs As Object

Set CN = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.RecordSet")

Dim strCn As String, strSQL As String

strCn = "Provider=sqloledb;Data Source=192.168.1.1;Initial Catalog=AIS2019122316;User ID=sa;Password=23456;Persist Security Info=False;"

strSQL = "select fnumber as 物料代码 ,fname as 物料名称 ,fmodel as 规格型号 from t_icitem where fname like %" & aa & "% and fname like %" & bb & "% " 物料表

CN.Open strCn

rs.Open strSQL, CN

Dim i As Integer, sht As Worksheet

i = 1

Set sht = ThisWorkbook.Worksheets("jskc")

For j = 0 To rs.Fields.Count - 1

sht.Cells(3, j + 1).Value = rs.Fields(j).Name

Next j

Worksheets("jskc").Range("a4").CopyFromRecordset rs

rs.Close

End Sub