使用VBA滤波器功能将SQL结果分离到单独的张纸上

Question

我正在尝试查看我是否可以将当前3个单独的查询整合到一个查询中，并在VBA中使用 filter 函数以分离每个数据床单这样我在一张纸上有TEL（COMM001），另一张手机（COMM004）在另一个纸上，依此类推。但是，数据最终都以每张表显示的所有内容（Comm001，Comm004，Comm007）。

任何人都可以建议这是否可能，如果是这样，我需要在以下代码中更改？

Const sqlconnection = "Provider=visoledb;"
Dim conn As New Connection
conn.ConnectionString = sqlconnection
conn.Open
Dim rs As Recordset
Dim a1 As String
a1 = "SELECT id, sysdate, number, category FROM telephone s WHERE s.master_ty = 1 AND s.category IN ('COM001', 'COM004','COM007') "
Set rs = conn.Execute(a1)
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "TEL"
rs.Filter = "category='COMM001'"
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
.Refresh
End With
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "MOB"
rs.Filter = "category='COMM004'"
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
.Refresh
End With
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "EMAIL"
rs.Filter = "category='COMM007'"
With ActiveSheet.QueryTables.Add(Connection:=rs, Destination:=Range("A1"))
.Refresh
End With

Answer 1

您需要在过滤后更新记录集

尝试以下操作：

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "TEL"
rs.Filter = "category='COMM001'"
Set rsFiltered = rs.OpenRecordset
With ActiveSheet.QueryTables.Add(Connection:=rsFiltered ,Destination:=Range("A1"))
.Refresh
End With