根据文件的动态日期属性将最新CSV导入Excel

Question

Stackbros

我有一些VBA，我正在使用它将.csv文件从指定的文件夹导入Excel。导入后，脚本将执行一些其他转换，例如定界和偏移。请参见下文。

Sub OpenTextFile ()
Dim FilePath As String
FilePath = "C:FoldernameFoldernameFoldernameFile_name.CSV"
Open FIlePath For Input As #1
row_number = 0
Do Until EOF(1)
    Line Input #1, LineFromFile
    LineItems = Split(LineFromFile, ",")
    ActiveCell.Offset (row_number, 0).Value = LineItems (2)
    ActiveCell.Offset (row_number, 1).value = LineItems (1)
    ActiveCell.Offset (row_number, 2).Value = LineItems (0)
    row_number = row_number + 1 
    Loop
    Close #1
End Sub 

我真正想做的是修改它，这样当我运行它时，我也会根据文件夹中文件的日期属性从文件夹中导入最新的文件。

提前谢谢。

Answer 1

看看这个例程。它相当古老，但我认为它符合你的要求。如果没有，它应该会给你一些想法。

Function NewestFileName(ByVal Path As String, ByVal FileTemplate As String) As String
  ' * Path          Folder in which to search for files
  ' * FileTemplate  File name specification of the file required.  For example:
  '                     MyFile*.xls
  ' * Finds, and returns the name of, the newest file in folder Path with a name
  '   that matches FileTemplate.  Returns "" if no matching file is found.
  ' 25Jul11  Copied from RiskRegisterControl V43.xls.
  ' 22Nov11  Name changed from NewestFile to NewestFileName to match NextFileName.
  ' 20Apr12  Minor improvements
  Dim FileDateCrnt              As Date
  Dim FileDateNewest            As Date
  Dim FileNameCrnt              As String
  Dim FileNameNewest            As String
  If Right(Path, 1) <> "" Then
    Path = Path & ""
  End If
  FileNameCrnt = Dir$(Path & FileTemplate)
  If FileNameCrnt = "" Then
    NewestFileName = ""
    Exit Function
  End If
  FileNameNewest = FileNameCrnt
  FileDateNewest = FileDateTime(Path & FileNameCrnt)
  Do While True
    FileNameCrnt = Dir$
    If FileNameCrnt = "" Then Exit Do
    FileDateCrnt = FileDateTime(Path & FileNameCrnt)
    If FileDateCrnt > FileDateNewest Then
      FileNameNewest = FileNameCrnt
      FileDateNewest = FileDateCrnt
    End If
  Loop
  NewestFileName = FileNameNewest
End Function