VBA to Export CSV

Question

我发现这段代码非常有用，但我需要稍作修改，以确保它使用新名称保存副本，而不是覆盖。它是否可以自动输入日期作为文件名的一部分？

Sub ExportAsCSV()
Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook
Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy
Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
MyFileName = CurrentWB.Path & "" & Left(CurrentWB.Name, InStrRev(CurrentWB.Name, ".") - 1) & ".csv"
'Optionally, comment previous line and uncomment next one to save as the current sheet name
'MyFileName = CurrentWB.Path & "" & CurrentWB.ActiveSheet.Name & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

Answer 1

VBANow将无法工作，因为Windows API具有文件名约定，这将阻止以下字符：

< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
 (backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)

所以我们需要调整日期格式。Now返回如下：

9/29/2020 12:45:26 PM

我们可以这样调整：

9-29-2020

然后使用Format()功能：

MyFileName = CurrentWB.Path & "" & Left(CurrentWB.Name, InStrRev(CurrentWB.Name, ".") - 1) & Format(Now,"mm-dd-yyyy") & ".csv"

VBA格式((-Microsoft Docs

Answer 2

我没有尝试过，但它能工作吗？

MyFileName = CurrentWB.Path & "" & Left(CurrentWB.Name, InStrRev(CurrentWB.Name, ".") - 1) & Now & ".csv"