VBA:每天从文件末尾删除格式化日期并重新保存

Question

每天我都会收到一个以DailyFile 09-12-18.xlsx格式保存的自动文件。

我编写了一个 VBA 模块来查找一个名为(在本例中(DailyFile 的文件，并使用新的每日数据更新我必要的电子表格。

但是，我需要一种方法来从每日文件中剪切日期部分，以便我的模块可以识别它，但我还需要保留每个每日文件的副本(文件名中包含日期(以供记录。

换句话说，我的主电子表格需要从每日文件中提取最新数据(可以从昨天开始覆盖(，但我也想保存原始文件的副本。

这是我到目前为止所拥有的...它不起作用：

Option Explicit

Sub changefilename()
Dim tdate As Variant
Dim ofile As Workbook
Dim TestFile As String

tdate = Date
tdate.NumberFormat = "mm-dd-yy"
Set ofile = Workbooks.Open("C:UsersResearchDocumentsDaily File " & tdate & ".xlsx")
ofile.SaveAs Filename:=TestFile

End Sub

Answer 1

Option Explicit 
Sub changefilename()
Dim ofile As Workbook
Dim TestFile As String 

'##What we want the new save file to be called, with path:
TestFile = "C:UsersResearchDocumentsTEST.xlsx"

'##Searches for a file that concats the file name with today's date.
'##Use format(Date,____) for how your date is formatted. No need to assign a variable to the Date.
Set ofile = Workbooks.Open("C:UsersResearchDocumentsDailyFile" & Format(Date, "mm-dd-yy") & ".xlsx")
'##Saves the old file in the desired path!
ofile.SaveAs Filename:=TestFile

End Sub

Answer 2

.NumberFormat是一个单元格属性。tdate不是单元格，而是变体，因此没有该属性。

改用Format函数设置其格式：

Option Explicit
Sub changefilename()
Dim tdate As Variant
Dim ofile As Workbook
Dim TestFile As String
tdate = Format(Date, "mm-dd-yy")
Set ofile = Workbooks.Open("C:UsersResearchDocumentsDaily File " & tdate & ".xlsx")
ofile.SaveAs Filename:=TestFile
End Sub

Answer 3

Sub changefilename()
Dim tdate As String
Dim ofile As Workbook
Dim ofile_Name As String
tdate = Format(Date, "MM-DD-YY")
Set ofile = Workbooks.Open("C:UsersResearchDocumentsDaily File " & tdate & ".xlsx")
'change the character 9 to whatever the length of the file name
ofile_Name = Left(ofile.Name, 9)
ofile.SaveAs Filename:=ofile_Name   
End Sub