ajhez
05-15-2015, 07:19 AM
Hi all,
I have a column in excel that contains text which I want to use in the file name / file path when my code automatically saves the documents it generates. The process will create approximately 900 documents (1 document for each cell in my text column).
My problem is that I have noticed an error on on those documents where the text cell contains "/" because this is not a permitted character for the file path when saving documents. I have created the below code to remove the "/" from the cells I select, but i wondered if there was a way to remove all special characters OR to take my existing code and to make it search & replace for more than just "/" but for example to also include "?" "!" "*" for example.
Sub FindandReplace()
Dim fnd As Variant
Dim rplc As Variant
fnd = "/"
rplc = ""
For Each c In Selection
c.Replace What:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next
End Sub
Thanks!
I have a column in excel that contains text which I want to use in the file name / file path when my code automatically saves the documents it generates. The process will create approximately 900 documents (1 document for each cell in my text column).
My problem is that I have noticed an error on on those documents where the text cell contains "/" because this is not a permitted character for the file path when saving documents. I have created the below code to remove the "/" from the cells I select, but i wondered if there was a way to remove all special characters OR to take my existing code and to make it search & replace for more than just "/" but for example to also include "?" "!" "*" for example.
Sub FindandReplace()
Dim fnd As Variant
Dim rplc As Variant
fnd = "/"
rplc = ""
For Each c In Selection
c.Replace What:=fnd, Replacement:=rplc, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
Next
End Sub
Thanks!