[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

Replacing Reserved Characters in a Varible

I have a table in word where one of the cells in each row holds a name of a file to be created.

Currently my VB code goes through each row looking in this cell, gets the contents and stores this in a varible, opens a word template and then saves the template as whatever has been written in the cell.

No matter how many times i tell the users, they always forget they cannot use characters like ?\/<>: in this cell because they are not allowed in file names.  This obvisouly causes my code to error.

What i want it to do is check each character in the string for these reserved characters and if it finds one replace it with a hyphen so the code can carry on and save the template.

Im been messing around with InStr and Replace methods but im not getting very far.

Anyone able to help me?
  • 3
1 Solution
Bill PrewCommented:
Given that you have a manageable set of characters to exclude, a simple REPLACE approach might work the best, for example:

strFile = Replace(strFile, "?", "-")
strFile = Replace(strFile, "\", "-")
strFile = Replace(strFile, "/", "-")
strFile = Replace(strFile, "<", "-")
strFile = Replace(strFile, ">", "-")
strFile = Replace(strFile, ":", "-")

Bill PrewCommented:
Or as a slightly more compact version:

strSpecial = "?/\<>:"
For i = 1 to Len(strSpecial)
    strFile = Replace(strFile, Mid(strSpecial, i, 1), "-")

Open in new window

jdc1944Author Commented:
Thank you, both of them do just the job
Bill PrewCommented:


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now