|
更新,可以跨年月合并。
- Sub ykcbf() '//2024.6.4 日期作为第一列,日期按顺序排列
- Dim arr, brr
- Application.ScreenUpdating = False
- Application.DisplayAlerts = False
- ReDim brr(1 To 10000, 1 To 10)
- bt = [{"代码","收盘"}]
- Set fso = CreateObject("scripting.filesystemobject")
- Set Sh = ThisWorkbook.Sheets("合并")
- p = ThisWorkbook.Path & ""
- m = 1: n = 1
- brr(1, 1) = "日期": brr(1, 2) = bt(1): brr(1, 3) = bt(2)
- For Each f In fso.GetFolder(p).Files
- If LCase$(f.Name) Like "*.xls*" Then
- If InStr(f.Name, ThisWorkbook.Name) = 0 Then
- fn = fso.GetBaseName(f)
- rq = CDate(Format(Right(fn, 8), "0000-00-00"))
- Set wb = Workbooks.Open(f, 0)
- With wb.Sheets(1)
- arr = .UsedRange
- End With
- wb.Close SaveChanges:=False
- For i = 2 To UBound(arr)
- If arr(i, 1) <> Empty Then
- m = m + 1
- brr(m, 1) = rq
- brr(m, 2) = Format(arr(i, 1), "000000")
- brr(m, 3) = arr(i, 2)
- End If
- Next
- End If
- End If
- Next f
- With Sh
- .UsedRange.Clear
- .[a1].Resize(1, 3).Interior.Color = 49407
- .Columns(2).NumberFormat = "@"
- With .[a1].Resize(m, 3)
- .Value = brr
- .Borders.LineStyle = 1
- .HorizontalAlignment = xlCenter
- .VerticalAlignment = xlCenter
- With .Font
- .Name = "微软雅黑"
- .Size = 11
- End With
- End With
- Set Rng = .[a2].Resize(m - 1, 3)
- Rng.Sort .[a2], 1
- ActiveWindow.DisplayZeros = False
- End With
- Application.ScreenUpdating = True
- MsgBox "OK!"
- End Sub
复制代码
|
|