Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Does anyone have a snip of code that will parse a string for any of those characters that cannot be used in a file-save name and replace it? i.e. *."/\[]:;|=,

    I've got an idea on how it can be done, but it's getting too long and too slow.


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Is this for a Mac? In Windows, the illegal characters for a file name are / \ | < > : * ? "


  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Like this maybe?

    Code:
    Public Function IsValidFile(sFileName As String) As Boolean
    '-------------------------------------------------------------------------
    ' Procedure : IsValidFile
    ' Company   : JKP Application Development Services (c)
    ' Author    : Jan Karel Pieterse (www.jkp-ads.com)
    ' Created   : 31-10-2009
    ' Purpose   : Returns TRUE if filename contains no invalid characters
    '-------------------------------------------------------------------------
        Const sInvalidChars As String = "<>?[]:|*!"
        Dim lCt As Long
        If (Len(sFileName) > 0) Then
            For lCt = 1 To Len(sInvalidChars)
                If InStr(sFileName, Mid(sInvalidChars, lCt, 1)) > 0 Then
                    IsValidFile = False
                    Exit Function
                End If
            Next
            IsValidFile = True
        End If
    End Function
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Is this for a Mac? In Windows, the illegal characters for a file name are / \ | < > : * ? "
    Hi Hans,
    No not for my Mac, but for Windows. I did that "i.e." quickly and may have got the characters wrong.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jan Karel

    That looks perfect! I was getting into the 10s (even 100s) of line of code - got off on the wrong track!

    I'll give that a go tomorrow morning, once I'm in the office!

    Many thanks

    Bowlie

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here's an alternative version:

    Code:
    Public Function IsValidFile(sFileName As String) As Boolean
      IsValidFile = Not (sFileName Like "*[/\|<>:*?""]*")
    End Function

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Here's an alternative version:

    Code:
    Public Function IsValidFile(sFileName As String) As Boolean
      IsValidFile = Not (sFileName Like "*[/\|<>:*?""]*")
    End Function
    Hans - thanks for this. I'd like to go one step further though - If a forbidden character exists, to replace it with a "-". I'm taking the file save name from 'subject' text users enter into a text box, and I'm not going to be able to get them to change their behaviour.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You could use this function (more along the lines of Jan Karel's suggestion):

    Code:
    Function CleanName(sFileName As String) As String
     Const sInvalidChars As String = "/\|<>:*?"""
     Dim lCt As Long
     CleanName = sFileName
     For lCt = 1 To Len(sInvalidChars)
      CleanName = Replace(CleanName, Mid(sInvalidChars, lCt, 1), "-")
     Next lCt
    End Function
    Example of usage:

    Dim sName As String
    sName = CleanName(InputBox("Please enter a file name"))

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    If you have free time on your hands to learn about how to write VBScript regular expression patterns, you could do the replace without looping. See the WildReplace function in post #470690.

    == Edit ==

    The syntax I referred to is documented here: VBScript > Pattern Property on MSDN.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Here is an example of Jefferson's suggestion of using regular expressions:

    Code:
    Function CleanName(sFileName As String) As String
     With CreateObject("VBScript.RegExp")
      .Pattern = "[/\\|<>:\*\?""]"
      .Global = True
      CleanName = .Replace(sFileName, "-")
     End With
    End Function
    Warning: this way of using regular expressions is very slow - this version of the CleanName function is more than 30 times slower than the version I originally posted!

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    Quote Originally Posted by HansV View Post
    Warning: this way of using regular expressions is very slow - this version of the CleanName function is more than 30 times slower than the version I originally posted!
    Thanks for testing that. I didn't realize there would be so much overhead in using an external library.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I suspect that regexp would come into its own if you had to do a lot of expression parsing within one procedure - you'd create the VBScript.RegExp object once and get a lot of mileage out of it. But here, the object gets used only once.

    Bowlie wants to use the function to clean a filename entered by the user. Compared to the time needed to enter the filename, the execution time of either version is insignificant.

  13. #13
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Jefferson - thanks, I think but that is a little further along the learning curve for me ...

    I used Hans' suggestion and frankly see no lag time at all. I've also tested with input of up to 255 characters and still I doubt that anyone would really notice a lag.

    Hans - thanks again, It works very nicely and I learned how a couple of new (to me) VB statements work.



    Oh, a PS ... My Macro creates a document, inserts variable info into various bookmarks, saves the document and then ends. However the next time I open the document it warns me of Macros. Once the document has been saved I don't need the macro any longer. How do I get rid of the macro (and the macro warning)?

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by Bowlie View Post
    My Macro creates a document
    How does it do that?

  15. #15
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Confoederatio Helvetica
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    How does it do that?
    Hans, sorry - you are right, it doesn't. The user creates a new document using my template which autoruns my macro. The Macro displays a dialogue box, the user clicks on choices and enters text which is then inserted into bookmarks in the new document. Using one of the text fields the document is then saved and the macro ends.

    Bowlie

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •