VBA宏使用单元格路径保存Excel文件

Question

我正在尝试使用硬线和单元格值的组合来保存文件以确定文件路径。

在单元格 A29 中，我有一个输出以下内容的公式:

2014\January\High Cash 1.7.14

我收到预期:语句结束错误。

代码是:

ActiveWorkbook.SaveAs Filename:="S:\IRD\Tamarac\Daily High Cash Reporting\& Range("A29").Text & ".xlsx", FileFormat:= _  xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _ , CreateBackup:=False

Answer 1

建议您更进一步，确保过滤掉任何会导致保存错误的无效文件名字符

此代码删除

[]/:*?"<>

主要代码

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

清洁功能

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