Replacing Reserved Characters in a Varible

Posted on 2012-08-30
Last Modified: 2012-08-30
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?
Question by:jdc1944
    LVL 51

    Expert Comment

    by:Bill Prew
    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, ":", "-")

    LVL 51

    Accepted Solution

    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

    LVL 2

    Author Closing Comment

    Thank you, both of them do just the job
    LVL 51

    Expert Comment

    by:Bill Prew


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    I would like to show you some basics you can do with Mailings in MS Word. It´s quite handy feature you can use for creating envelopes, labels, personalized letters etc. First question could be what is this feature good for? Mailing can really he…
    In this video, we show how to convert an image-only PDF file into a PDF Searchable Image file, that is, a file with both the image (typically from scanning) and text, which is created in an automated fashion with Optical Character Recognition (OCR) …
    Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now