|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
请试一下附件。如果不想打开数据来源工作簿,需要保持他们和汇总表在同一目录下。代码如:- Sub main()
- Dim criteria, destination As Worksheet, d_row As Long
- Dim flow_source As Object
- '汇总目的地:
- Set destination = ThisWorkbook.Sheets("用刀明细")
- d_row = 3
- '检索关键字:
- criteria = Format(destination.Cells(2, 15), "yyyymmdd")
- '检索字段:
- Set flow_source = CreateObject("Scripting.Dictionary")
- flow_source("用刀明细(一仓)?换刀") = "领刀日期,产品名称:刀号,料号2 As 刀具料号,领刀数量:品牌, 生产数量, 预计寿命,其他 As 备注,换刀~'' As 类别, 一仓~'' As 仓位,新刀~'' As 刀具属性"
- flow_source("用刀明细(一仓)?架机") = "领刀日期,产品名称:刀号,料号 As 刀具料号,领刀数量:品牌,EMPTY~'' As 生产数量,EMPTY~'' As 预计寿命, 备注,架机~'' As 类别, 一仓~'' As 仓位,新刀~'' As 刀具属性"
- flow_source("用刀明细(二仓)?换刀") = Replace(flow_source("用刀明细(一仓)?换刀"), "一仓", "二仓")
- flow_source("用刀明细(二仓)?架机") = Replace(flow_source("用刀明细(一仓)?架机"), "一仓", "二仓")
- '执行ExcelSpice工作流:
- For Each s In flow_source
- spice_work_flow criteria, s, flow_source(s), destination, d_row
- Next
- '修改个别标志
- With destination
- For i = 3 To d_row
- If .Cells(i, 8) Like "*返修*" Then .Cells(i, 14) = "返修刀"
- Next
- End With
- End Sub
- Sub spice_work_flow(ByVal criteria, ByVal source, ByVal fields As String, destination As Worksheet, ByRef d_row As Long)
- On Error Resume Next
- Dim sp As New ExcelSpice
- '连接数据源、检索、输出:
- sp.Link source, Index:="领刀日期"
- Set sp = sp.Some(What:=fields, Index:=criteria)
- sp.push sp.Records, destination.Cells(d_row, 1)
- '首次输出时输出标题
- If d_row = 3 Then sp.push sp.Header, destination.Cells(2, 1)
- d_row = d_row + sp.Length + 1
- If Err.Number Then d_row = d_row - 1: Err.Number = 0
- End Sub
复制代码 关于修改个别标志的问题,本来excelspice也能做到,但是在连续检索不同表格时会因为报错而中断,下一版本会修复,请使用最新版ExcelSpice。
http://club.excelhome.net/thread-1540656-1-1.html
|
|