求助——如何实现一个总的管理表格进行所有子表格到期提醒

jimikings

各位大侠，本人的工作中经常涉及到各种的合同到期前需要续签的，包括员工劳动合同、各外包单位合同、各种租赁合同以及特殊工种年审到期提醒，请问能否在一张总的管理表格中实现以上各种管理表中到期日提前X天通知。最好是可以实现即将到期的项目自动显示在总的管理表格里面。以上。谢谢各位大侠。

求助.rar (31.37 KB, 下载次数: 57)

2012-3-7 10:09 上传

点击文件名下载附件

skyzxh

用vba可以实现.

jimikings

skyzxh 发表于 2012-3-7 10:44
用vba可以实现.

请问具体的思路是怎么样？

静静2008

等待高手出现吧,
以子表1为例
F3=IF(ISERROR(DATEDIF(TODAY(),E3,"d")),"已过期",DATEDIF(TODAY(),E3,"d")&"天后到期")
我是将每个子表先计算出小于20天到期的数据设置条件格式颜色提示，
如果汇总到总表数据量大时运行会慢

jimikings

静静2008 发表于 2012-3-7 11:43
等待高手出现吧,
以子表1为例
F3=IF(ISERROR(DATEDIF(TODAY(),E3,"d")),"已过期",DATEDIF(TODAY(),E3,"d" ...

谢谢，先学习一下这种方法，看能否做到。

sunya_0529

这个问题不论是用函数还是VBA，都要先做好数据规范的工作，当然，用VBA的效率会更高，更简便。

你的子表格式不尽相同，这里也只是列举了几个例子。建议所有子表都做成“数据清单”的格式（”数据清单“中字段名尽量统一，比如都叫”合同到期日“或者”到期日“），然后统一在管理表里做汇总和统计分析。

BTW，所谓”数据清单“就是第一行为字段名（不重复，无合并单元格），第二行开始为记录。”数据清单“对于数据的统计分析有重大意义，一定要规范。

sunya_0529

对了，再补充一下，各子表中每一条记录应该有一个唯一的号码来对应（术语叫”索引字段“），比如叫流水号，或者合同编号，这些号码是不重复的，这样有利于快速定位到相应记录。做数据清单应该养成一个好习惯，就是一定要增加一个”索引字段“，哪怕叫”ID"，按行号对应每条记录也行。

另外，子表最好能集中放在一个固定的目录下，这样方便用程序检索所有的子表。或者在汇总表里将所要统计的子表对应的详细路径做一个列表，程序根据列表中的子表进行数据读取。

做好了子表中的数据清单，再一起打个包上传上来，给你写个宏，点一个按钮就可以汇总所有子表中即将到期的记录信息。

jimikings

sunya_0529 发表于 2012-3-7 12:36
对了，再补充一下，各子表中每一条记录应该有一个唯一的号码来对应（术语叫”索引字段“），比如叫流水号， ...

我把每一个表的第一列设为ID列，加入相应的编号。到时候所有的子表都是和总表放在同一个文件夹的。请帮忙编一个程序，以及指导一下我如何使用，以及以后如果有新的子表，应该如何修改这个程序代码，谢谢。
求助2.rar (31.74 KB, 下载次数: 28)

2012-3-7 12:59 上传

点击文件名下载附件

sunya_0529

jimikings 发表于 2012-3-7 13:02
我把每一个表的第一列设为ID列，加入相应的编号。到时候所有的子表都是和总表放在同一个文件夹的。请帮忙 ...

你附件里的数据清单还不标准，不要表头，不要合并单元格，如我附件所示。

另外，要解决你的问题，建议所有子表遵循如下规范：

1、子表的文件名无所谓，但要跟“总管理表”放在同一目录下，并且此目录下不要放其他文件，以免程序出错；

2、子表中存放数据清单的工作表名称应该一致，如附件所示，全部改成“Sheet1”，方便程序调用；

3、子表中必须有的三个字段：“ID”、“项目”、“到期日期”，位置不限，如附件中高亮显示部分；


附件中因示例数据不多，因此设定查询天数为330天以内的记录，你可以自己去修改这个数值，如下面代码注释部分——

1. Private Sub CommandButton1_Click()
   
2. On Error Resume Next
   
3. Dim fso As Object, fld As Object, fls As Object, fle As Object
   
4. Dim cn As Object, intDays%
   
5. Set fso = CreateObject("Scripting.FileSystemObject")
   
6. Set fld = fso.GetFolder(ThisWorkbook.Path)
   
7. Set fls = fld.Files
   
8. Set cn = CreateObject("ADODB.CONNECTION")
   
9. intDays = 330 '设置需要提醒的天数，可根据需要修改
   
10. With ActiveSheet
    
11.   .Cells.Clear
    
12.   .[A1:D1] = Array("ID", "项目", "到期日", "到期天数") '书写表头
    
13.   For Each fle In fls '循环处理目录下各个子表
    
14.     If fle.Name <> ThisWorkbook.Name Then '搜索子表时，排除“总管理表”本身
    
15.       cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0;HDR=yes;IMEX=2';Data Source=" & fle.Path
    
16.       .[A65536].End(xlUp).Offset(1, 0).CopyFromRecordset cn.Execute("SELECT * FROM (SELECT ID,项目,到期日期,DateDiff('d',date(),到期日期) as 到期天数 FROM [Sheet1$]) WHERE 到期天数<=" & intDays)
    
17.       cn.Close
    
18.     End If
    
19.   Next
    
20.   MsgBox "查询完成！"
    
21. End With
    
22. '以下代码打扫战场
    
23. Set cn = Nothing
    
24. Set fls = Nothing
    
25. Set fld = Nothing
    
26. Set fso = Nothing
    
27. End Sub

复制代码

jimikings

sunya_0529 发表于 2012-3-7 15:57
你附件里的数据清单还不标准，不要表头，不要合并单元格，如我附件所示。

另外，要解决你的问题，建议 ...

真的太感谢您了，虽然还没看懂，但今晚必定回去好好研究。希望能够学习好excel，以后为坛友解决力所能及的问题。