时间戳

首页 > 解决方案 > 从文件夹中最新的 excel 文件导入工作表？第一次使用VBA

excel - 从文件夹中最新的 excel 文件导入工作表？第一次使用VBA

问题描述

我花了一整天的时间试图理解 VBA，我尝试了很多不同的网站来找到正确的代码，但我就是无法让它工作。我现在使用的代码会导入我文件夹中所有 excel 文件的所有工作表。我只需要导入最新的。正如我获得此代码的网站中所建议的那样，我添加了一个命令按钮。从长远来看，我希望能够将导入的数据应用到我已经在主工作表上的表中，然后打印模板然后删除信息，以便我可以重新开始下一个最近的电子表格。但现在我只想知道如何只获取导入到我的工作表中的最新文件。

Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

directory = "C:\ExcelPract\"
fileName = Dir(directory & "*.xl??")


Do While fileName <> ""
    Workbooks.Open (directory & fileName)

    For Each sheet In Workbooks(fileName).Worksheets
        total = Workbooks("Docket .xls").Worksheets.count
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("Docket .xls").Worksheets(total)
    Next sheet

    Workbooks(fileName).Close
    fileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

标签： excelvba

解决方案

您可以使用以下代码并从CommandButton1_Click()调用NewestFile函数。我只替换了您的 Sub 中的以下行。

文件名 = 最新文件（目录，“*.xls”）

Function NewestFile(directory, FileSpec)
' Returns the name of the most recent file in a Directory
' That matches the FileSpec (e.g., "*.xls").
' Returns an empty string if the directory does not exist or
' it contains no matching files
    Dim fileName As String
    Dim MostRecentFile As String
    Dim MostRecentDate As Date
    If Right(directory, 1) <> "\" Then directory = directory & "\"

    fileName = Dir(directory & FileSpec, 0)
    If fileName <> "" Then
        MostRecentFile = fileName
        MostRecentDate = FileDateTime(directory & fileName)
        Do While fileName <> ""
            If FileDateTime(directory & fileName) > MostRecentDate Then
                 MostRecentFile = fileName
                 MostRecentDate = FileDateTime(directory & fileName)
             End If
             fileName = Dir
        Loop
    End If
    NewestFile = MostRecentFile
End Function


Private Sub CommandButton1_Click()

Dim directory As String, fileName As String, sheet As Worksheet, total As Integer

directory = "C:\ExcelPract\"
fileName = NewestFile(directory, "*.xls")


Do While fileName <> ""
    Workbooks.Open (directory & fileName)

    For Each sheet In Workbooks(fileName).Worksheets
        total = Workbooks("Docket .xls").Worksheets.Count
        Workbooks(fileName).Worksheets(sheet.Name).Copy _
        after:=Workbooks("Docket .xls").Worksheets(total)
    Next sheet

    Workbooks(fileName).Close
    fileName = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

推荐阅读