vba 使用单元格路径保存excel文件的VBA宏
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20978868/
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
VBA macro to save excel file using path from cell
提问by user3009860
I'm trying to save out a file using a combination of hard line and cell value to determine the file path.
我正在尝试使用硬线和单元格值的组合来保存文件以确定文件路径。
In cell A29, I have a formula that outputs this:
在 cell 中A29，我有一个公式输出：
2014\January\High Cash 1.7.14
2014\1\高现 1.7.14
I'm getting an Expected: end of statementerror.
我收到预期：语句结束错误。
The code is:
代码是：
ActiveWorkbook.SaveAs Filename:="S:\IRD\Tamarac\Daily High Cash Reporting\& Range("A29").Text & ".xlsx", FileFormat:= _ xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False
回答by brettdj
Suggest you go a step further and ensure any invalid file name characters that will case a save error are filtered out
建议您更进一步，确保过滤掉任何会导致保存错误的无效文件名字符
This code removes
此代码删除
[]/:*?"<>
[]/:*?"<>
main code
主要代码
Sub CleanSave()
Dim fileName As String
fileName = "C:\temp\" & strClean(Range("A29").Value) & ".xlsx"
ActiveWorkbook.SaveAs fileName:=fileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
cleaning function
清洁功能
Function strClean(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Pattern = "[\[\]|\/\:\*\?""<>]"
.Global = True
strClean = .Replace(strIn, vbNullString)
End With
End Function
回答by MarkHone
I would re-write as follows:
我会重写如下：
Dim fileName As String
fileName = "S:\IRD\Tamarac\Daily High Cash Reporting\" & Range("A29").Text & ".xlsx"
ActiveWorkbook.SaveAs Filename:=fileName, FileFormat:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
I think all you are missing is the closing double-quotes after the 'Reporting\' part of your code...
我认为您所缺少的只是代码的“报告\”部分之后的双引号……
