更换工作表名称中的多个无效字符 [英] Replacing multiple invalid characters in a sheet name
问题描述
我正在编写一个相当大的宏
I am writing a fairly large macro
- ,以保存用户之前输入的名称保存工作簿。 li>
- 我正在使用表格的标题,所以我需要删除任何会丢失文件系统错误的保留字符
(/ \ * | ?: $>
我想避免使用大量的 Replace()
语句,所以有一种方法可以在一个语句中用下划线替换多个字符 - 更像是一个 regexp ?
I would like to avoid having to use a large number of Replace()
statements, so is there any way to replace multiple characters with an underscore in one statement - something more like a regexp?
感谢所有!
推荐答案
幸好你会发现大部分这些字符已经作为工作表名称字符无效,即 *?[] / code> etc
Happily you will find that most of these characters are invalid already as sheet name characters, ie *?[]
etc
但正如你已经说过的,使用 regexp :)
But as you have said already, use a regexp :)
下面的东西返回 True
为有效的字符串, False
为无效。
Something like below to return True
for valid strings, False
for invalid ones.
我已经添加了您的项目,转义特殊字符
I have added your items above, escaping special characters as needed.
[已更新:添加替换功能]
[Updated: added replacement Function]
Sub TestCleanNames()
Dim StrArr()
Dim StrTst
StrArr = Array("Am i ok", "Sheet||a", "audited??", "Sheet1")
For Each StrTst In StrArr
MsgBox StrTst & " = " & ValidSheetName(CStr(StrTst)) & vbNewLine & CleanSheetName(CStr(StrTst))
Next
End Sub
有效的名称函数
Function ValidSheetName(strIn As String) As Boolean
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
objRegex.Pattern = "[\<\>\*\\\/\?|]"
ValidSheetName = Not objRegex.test(strIn)
End Function
清除函数
Function CleanSheetName(strIn As String) As String
Dim objRegex As Object
Set objRegex = CreateObject("vbscript.regexp")
With objRegex
.Global = True
.Pattern = "[\<\>\*\\\/\?|]"
CleanSheetName = .Replace(strIn, "_")
End With
End Function
这篇关于更换工作表名称中的多个无效字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!