Excel VBA如何按文件的最新日期提取文件 VBA中如何打开一个文件夹内的所有EXCEL文件?
EXCEL VBA \u83b7\u53d6\u6587\u4ef6\u5939\u53ca\u5b50\u6587\u4ef6\u5939\u4e0b\u6240\u6709\u6587\u4ef6\u5e76\u5efa\u7acb\u8d85\u94fe\u63a5\u8fd8\u6ca1\u6709\u89e3\u51b3\u5417\uff1f\u90a3\u6211\u5c31\u6765\u8bd5\u4e00\u4e0b\u3002
\uff081\uff09\u9996\u5148\uff0c\u697c\u4e3b\u5148\u65b0\u5efa\u4e00\u4e2aexcel\uff0c\u540d\u79f0\u697c\u4e3b\u81ea\u5df1\u5199\uff0c\u600e\u6837\u90fd\u884c\uff0c\u7136\u540e\u5c06\u5176\u6253\u5f00\u3002
\uff082\uff09\u6253\u5f00\u540e\u6309\u4f4fAlt\u518d\u6309F11\uff0c\u8fd9\u6837\u5c31\u4f1a\u6253\u5f00vba\u4ee3\u7801\u7f16\u8f91\u5668
\uff083\uff09\u70b9\u83dc\u5355\u680f\u4e0a\u7684\u201c\u63d2\u5165\u201d\uff0c\u9009\u62e9\u201c\u6a21\u5757\u201d\uff0c\u8fd9\u6837\u5728\u5de6\u4e0b\u89d2\u5c31\u4f1a\u51fa\u73b0\u4e00\u4e2a\u201c\u6a21\u57571\u201d
\uff084\uff09\u53cc\u51fb\u201c\u6a21\u57571\u201d\uff0c\u53f3\u8fb9\u5c31\u4f1a\u51fa\u73b0\u201c\u6a21\u57571\u201d\u7684\u7f16\u8f91\u754c\u9762\uff0c\u5c06\u4ee5\u4e0b\u4ee3\u7801\u590d\u5236\uff0c\u5e76\u7c98\u8d34\u5230\u8fd9\u4e2a\u754c\u9762\u4e2d\uff0c\u6309F5\u8fd0\u884c\u3002
Sub \u904d\u5386\u6587\u4ef6\u5939()'Columns(1).Delete On Error Resume NextDim f As StringDim file() As StringDim i, k, xx = 1i = 1: k = 1ReDim file(1 To i)file(1) = InputBox("\u8bf7\u8f93\u5165\u8981\u67e5\u627e\u7684\u6587\u4ef6\u5939\uff1a") & "\"Do Until i > k f = Dir(file(i), vbDirectory) Do Until f = "" If InStr(f, ".") = 0 Then k = k + 1 ReDim Preserve file(1 To k) file(k) = file(i) & f & "\" End If f = Dir Loop i = i + 1LoopFor i = 1 To k f = Dir(file(i) & "*.*") Do Until f = "" 'Range("a" & x) = f Range("a" & x).Hyperlinks.Add Anchor:=Range("a" & x), Address:= _ file(i) & f, TextToDisplay:=f x = x + 1 f = Dir LoopNextEnd Sub\uff085\uff09\u5728\u51fa\u73b0\u7684\u5bf9\u8bdd\u6846\u4e2d\uff0c\u5c06\u4f60\u8981\u67e5\u627e\u7684\u6587\u4ef6\u7684\u5730\u5740\u590d\u5236\u5230\u5bf9\u8bdd\u6846\u4e2d\uff0c\u6309\u786e\u5b9a\uff0c\u5c31\u5b8c\u6210\u4e86\u3002
\u6ce8\uff1a\u8fd9\u91cc\u7528\u7684\u662fexcel2007\u7248\uff0c\u5982\u679c\u697c\u4e3b\u7528\u7684\u662f\u5176\u4ed6\u7248\u672c\uff0c\u6709\u53ef\u80fd\u5b58\u5728\u4ee3\u7801\u4e0d\u517c\u5bb9\u95ee\u9898\u3002
\u56fe1.\u6253\u5f00vba\u7f16\u8f91\u5668\u3002
\u56fe2.\u8fd0\u884c\u4ee3\u7801\u3002
\u56fe3.\u7ed3\u679c\u3002
\u6ce8\u610f c:\a \u76ee\u5f55\u4e0b\u7684\u6587\u4ef6\u4e0d\u8981\u592a\u591a,\u514d\u5f97\u6b7b\u673a
Sub \u6253\u5f00excel\u8868\u683c()
Dim myPath$, myFile$, AK As Workbook
Application.ScreenUpdating = False '\u51bb\u7ed3\u5c4f\u5e55\uff0c\u4ee5\u9632\u5c4f\u5e55\u6296\u52a8
myPath = "c:\a\" '\u628a\u6587\u4ef6\u8def\u5f84\u5b9a\u4e49\u7ed9\u53d8\u91cf
myFile = Dir(myPath & "*.xls") '\u4f9d\u6b21\u627e\u5bfb\u6307\u5b9a\u8def\u5f84\u4e2d\u7684*.xls\u6587\u4ef6
Do While myFile "" '\u5f53\u6307\u5b9a\u8def\u5f84\u4e2d\u6709\u6587\u4ef6\u65f6\u8fdb\u884c\u5faa\u73af
If myFile ThisWorkbook.Name Then
Set AK = Workbooks.Open(myPath & myFile) '\u6253\u5f00\u7b26\u5408\u8981\u6c42\u7684\u6587\u4ef6
End If
myFile = Dir '\u627e\u5bfb\u4e0b\u4e00\u4e2a*.xls\u6587\u4ef6
Loop
Application.ScreenUpdating = True '\u51bb\u7ed3\u5c4f\u5e55,\u6b64\u7c7b\u8bed\u53e5\u4e00\u822c\u6210\u5bf9\u4f7f\u7528
End Sub
首先呢,得获取所有文件名称,然后呢,根据文件名称年月日部分挑出最新日期,再然后呢,根据最新日期生成文件名,再然后呢,进行复制。
注:你的源数据文件名和目录结构应该如图
样本文件见附件
代码如下
Sub test()
Dim pth As String, fn As String, ary(), tmpMax As Long, i As Integer
Dim wb As Workbook
pth = "D:\data\" '设置路径
fn = Dir(pth & "*.xlsx") '遍历该路径下的.xlsx文件
i = 0: tmpMax = 0
Do While fn <> ""
If fn <> ThisWorkbook.Name Then
i = i + 1
ReDim Preserve ary(i) '声明动态数组,在数组后面追加元素
ary(i) = --Left(Right(fn, 13), 8) '假设文件名称形式为“namelist20140918.xlsx”这样的格式,将年月日8位数装入数组
If ary(i) > tmpMax Then tmpMax = ary(i) '不断将找到的最大日期值放入变量tmpmax
End If
fn = Dir
Loop
Set wb = Workbooks.Open(pth & "namelist" & tmpMax & ".xlsx", , True) '打开tmpmax指定的文件
wb.Worksheets("Sheet2").Cells.Copy ThisWorkbook.Worksheets("Sheet3").Cells '复制目标相关内容
wb.Close
End Sub
扩展阅读:excel vba 打开文件夹 ... excel vba基础入门 ... vba打开指定excel文件 ... excel vba增加图表控件 ... excel vba其实很简单 ... vba编程必背50个程序 ... 快学excel vba入门教程 ... vba 存成文件 fileformat ... excel vba 粘贴 ...