vba Excel VBA无法使用保存日期值的变量保存文件名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15476215/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Can't save file name with variable holding date value
提问by user1571463
I have some code that copys and pastes the current worksheet to a blank new workbook, and then saves it depending on the values of some cells (stored in variables).
我有一些代码可以将当前工作表复制并粘贴到一个空白的新工作簿，然后根据某些单元格的值（存储在变量中）进行保存。
Specifically, these are Site, Client, and Date visited.
具体来说，它们是站点、客户和访问日期。
It all works fine with site and client, however when I include the date variable in the filename to save, it throws an error: Runtime error 76 - Path not found.
站点和客户端都可以正常工作，但是当我在要保存的文件名中包含日期变量时，它会引发错误：运行时错误 76 - 找不到路径。
I'd appreciate any help/advise.
我很感激任何帮助/建议。
Sub Pastefile()
Dim client As String
Dim site As String
Dim visitdate As String
client = Range("B3").Value
site = Range("B23").Value
screeningdate = Range("B7").Value
Dim SrceFile
Dim DestFile
SrceFile = "C:13 Recieved Schedules\schedule template.xlsx"
DestFile = "C:13 Recieved Schedules" & "\" & client & " " & site & " " & visitdate & ".xlsx"
FileCopy SrceFile, DestFile
ActiveWindow.SmallScroll Down:=-12
Range("A1:I37").Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-30
Workbooks.Open Filename:= _
"C:\Schedules13 Recieved Schedules" & "\" & client & " " & site & " " & visitdate & ".xlsx", UpdateLinks:= _
0
Range("A1:I37").PasteSpecial Paste:=xlPasteValues
Range("C6").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
回答by GSerg
When using dates in file names, you never want to rely on the default textual representation of the date, because that depends on the current locale.
在文件名中使用日期时，永远不要依赖日期的默认文本表示，因为这取决于当前的语言环境。
You should store the date as date in the first place, and explicitly format it in a safe way for the file name:
您应该首先将日期存储为日期，并以安全的方式将其明确格式化为文件名：
Dim visitdate As Date
visitdate = Range("b7").Value
dim visitdate_text as string
visitdate_text = Format$(visitdate, "yyyy\-mm\-dd")
You might also consider removing any special characters like \from your other values, such as clientand site. Otherwise the problem might arise again.
您还可以考虑\从其他值（例如client和 ）中删除任何特殊字符site。否则问题可能会再次出现。
回答by Octopus
Here is my suggestion of code rewrite:
这是我对代码重写的建议：
Sub Pastefile()
Dim client As String
Dim site As String
Dim visitdate As String
client = Range("B3").Value
site = Range("B23").Value
visitdate = Range("B7").Value
Dim SrceFile
Dim DestFile
If IsDate(visitdate) Then
SrceFile = "C:13 Received Schedules\schedule template.xlsx"
DestFile = "C:13 Received Schedules" & "\" & Trim(client) & " " & Trim(site) & " " & Str(Format(Now(), "yyyymmdd")) & ".xlsx"
If Trim(Dir("C:13 Received Schedules\schedule template.xlsx")) <> "" Then
FileCopy SrceFile, DestFile
Else
MsgBox (SrceFile & " is not available in the folder")
GoTo EndCode
End If
Range("A1:I37").Select
Selection.Copy
Workbooks.Open Filename:= _
"C:\Schedules13 Received Schedules" & "\" & client & " " & site & " " & visitdate & ".xlsx", UpdateLinks:= 0
Range("A1:I37").PasteSpecial Paste:=xlPasteValues
Range("C6").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Else
MsgBox ("Please input the correct date in cell B7")
ActiveSheet.Range("B7").Activate
End If
EndCode:
End Sub
