Solved VBA to exchange any "/" with "-" to create folder name

[Morphies]

Good morning all,

I have a little bit of VBA that auto creates a folder for a user. I want to extend this to another area of our system and crate a folder based on a field, the only issue I have is in many cases this field will contain one or more "/" and possibly an "&" which I'll need to handle in the code to create the folder. Is this even possible?

Example data field for Serial No: 68877/2/1X would need to be 68877-2-1X for the folder creation

Here is my current code:

Private Sub CmdJobFolder_Click()
On Error GoTo Err_cmdExplore_Click

Dim stAppName As String
Dim CreateFolder As Integer

'check if folder exists

    If FolderExists("R:\Repairs\" & "Serial No" & "\") = True Then
    stAppName = "C:\windows\explorer.exe R:\Repairs\" & "Me.Serial No" & "\"
    Call Shell(stAppName, 1)
    Exit Sub
        
        Elseif
        CreateFolder = MsgBox("Folder does not exist! Create it?", vbYesNo)
        If CreateFolder = vbNo Then
        Exit Sub
        
        ElseIf CreateFolder = vbYes Then
        MkDir "R:\Repairs\" & "Me.Serial No"
        stAppName = "C:\windows\explorer.exe R:\Techniques\" & "Me.Serial No" & "\"
        Call Shell(stAppName, 1)
        Exit Sub
Elseif

        stAppName = "C:\windows\explorer.exe R:\repairs\" & "Me.Serial No" & "\"
        Call Shell(stAppName, 1)
        Exit Sub
End If



Exit_cmdExplore_Click:
Exit Sub

Err_cmdExplore_Click:
MsgBox Err.Description
Resume Exit_cmdExplore_Click
End Sub

Is this possible in VBA?

TIA

 

[Moosak]

I use this function to replace the symbols from the path or directory by any character you want.

Public Function RemoveSymbolsFromText(strGivenTxt As String, Optional ReplacmentTxt As String = " ") As String

Dim Txt As String
Dim R As String

R = ReplacmentTxt

Txt = strGivenTxt
Txt = Replace(Txt, "!", R)
Txt = Replace(Txt, "@", R)
Txt = Replace(Txt, "#", R)
Txt = Replace(Txt, "$", R)
Txt = Replace(Txt, "%", R)
Txt = Replace(Txt, "^", R)
Txt = Replace(Txt, "*", R)
Txt = Replace(Txt, "\", R)
Txt = Replace(Txt, "|", R)
Txt = Replace(Txt, "/", R)
Txt = Replace(Txt, ".", R)
Txt = Replace(Txt, "?", R)
Txt = Replace(Txt, """", R)
Txt = Replace(Txt, "'", R)
Txt = Replace(Txt, "<", R)
Txt = Replace(Txt, ">", R)
Txt = Replace(Txt, "؟", R)
Txt = Replace(Txt, "~", R)
Txt = Replace(Txt, "&", R)

RemoveSymbolsFromText = Txt

End Function

 

[arnelgp]

you add another variable:

Dim sFolder As String
sFolder = Replace$(Replace$(Me![Serial No] & "", "/","-"), "&", "-")

then use sFolder in your code instead of Me![serial no]

 

[Morphies]

Thanks for the replies, I utilised the additional string suggest by arnelgp.