|
发表于 2011-8-14 01:41
|
显示全部楼层
本楼为最佳答案
本帖最后由 fjmxwrs 于 2011-8-14 10:29 编辑
回复 opelwang 的帖子
- Sub 数据合并()
- Dim Qssj, Lj As String, Wj As String, Xm As String, Sj As Double
- Dim Rq As Long, Xrl As Long, Xrh As Long, Qsh As Long, i3 As Date
- Dim i1 As String, i2 As String, ttt As String, k As Long
- On Error Resume Next
- Qssj = Timer
- Lj = ThisWorkbook.Path & "\00"
- i1 = InputBox("请输入开始日期:d")
- If i1 = "" Then Exit Sub
- i2 = InputBox("请输入结束日期:d")
- If Len(i2) = 0 Then Exit Sub
- i3 = Range("D2").Value
- Application.ScreenUpdating = False
- Range("D3:AI59").ClearContents
- For x = i1 To i2
- tt = Format(i3, "yyyy-mm") & "-" & Format(x, "00")
- Wj = Dir(Lj & tt & ".xls")
- If Wj = "" Then
- If ttt = "" Then
- ttt = tt
- Else
- ttt = ttt & Chr(10) & tt
- End If
- k = k + 1
- GoTo aa
- End If
- Workbooks.Open (Lj & Wj)
- Rq = Mid(tt, 9, 2)
- Xrl = Rq + 3
- With ActiveWorkbook.Sheets("花名冊")
- For i = 40 To 55
- Qsh = Range("B2:B50").Find(.Cells(1, i)).Row
- For j = 2 To 7
- Sj = .Cells(j, i)
- Xm = .Cells(j, 39)
- Select Case Xm
- Case "生產數量"
- Xrh = Qsh
- Cells(Xrh, Xrl) = Cells(Xrh, Xrl) + Sj
- Cells(Xrh, 35) = Cells(Xrh, 35) + Sj
- Case "生產時間"
- Xrh = Qsh + 1
- Cells(Xrh, Xrl) = Cells(Xrh, Xrl) + Sj
- Cells(Xrh, 35) = Cells(Xrh, 35) + Sj
- Case "生產面積"
- Xrh = Qsh + 2
- Cells(Xrh, Xrl) = Cells(Xrh, Xrl) + Sj
- Cells(Xrh, 35) = Cells(Xrh, 35) + Sj
- End Select
- Next j
- Next i
- End With
- ActiveWorkbook.Close True
- aa:
- Next x
- Application.ScreenUpdating = True
- If ttt = "" Then
- MsgBox "共用时" & Round((Timer - Qssj), 0) & "秒!"
- Else
- MsgBox ttt & Chr(10) & k & "个文件不存在" & Chr(10) & "共用时" & Round((Timer - Qssj), 0) & "秒!"
- End If
- End Sub
复制代码 1.解决了输入日期问题:只需要输入日
2.解决了没找到文件时出错的问题
3.解决了提示没有找到的文件。
|
评分
-
查看全部评分
|