我有一个文件夹,白天可以用根据这些模式命名的文件("D0_yyyymmdd.xlsx"(和("D1_yyyymddd"(更新。例如,我们可以找到"D0_20200506"one_answers"D1_20200506"。
我正在编写一个程序,根据用户的请求("D0"或"D1"(,我必须获得文件夹中可用的最新版本的信息。我已经有了一个可以打开最新文件的代码,但该代码不尊重用户请求"D0"或"D1"。话虽如此,如果用户请求是"D0",我必须从遵循"D0_yyyymmdd.xlsx"模式的最新版本的文件中获取信息。
有人能帮我吗?
'Force the explicit declaration of variables
Option Explicit
Sub OpenLatestFile()
'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim closedBook As Object
Dim GetBookName As String
GetBookName = ActiveWorkbook.Name
'Specify the path to the folder
MyPath = "\testingcode"
'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""
'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xlsx", vbNormal)
'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If
'Loop through each Excel file in the folder
Do While Len(MyFile) > 0
'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)
'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If
'Get the next Excel file from the folder
MyFile = Dir
Loop
'Open the latest file
Application.ScreenUpdating = False
Workbooks.Open MyPath & LatestFile
Sheets(1).Copy After:=Workbooks(GetBookName).Sheets(Sheets.Count)
Workbooks(LatestFile).Close SaveChanges:=False
Application.ScreenUpdating = True
选择任一使用按钮
对于CommandButton1
和CommandButton2
,将它们的Caption
更改为D0
和D1
。然后在Sheet Code
中使用以下内容,例如Sheet1
:
Option Explicit
Private Sub CommandButton1_Click()
OpenLatestFile Me.CommandButton1.Caption
End Sub
Private Sub CommandButton2_Click()
OpenLatestFile Me.CommandButton2.Caption
End Sub
现在以以下方式使子"有争议":
Sub OpenLatestFile(CmdCaption As String)
通过以下操作更改相应的行:
如果文件名以D0
或D1
开头,请使用:
'Get the first Excel file from the folder
MyFile = Dir(MyPath & CmdCaption & "*.xlsx", vbNormal)
如果文件名仅包含D0
或D1
,请使用:
'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*" & CmdCaption & "*.xlsx", vbNormal)
子节点应保持在standard module
中,例如Module1
。