VBA - replace a character in a range to save

[The Ruff Report]

In the below I'm saving a file with a name based on the values in cells A1 and B1. Cell B1 though contains prohibited characters. In my case, they are "[", "]" and of course "/"

I would like to remove the prohibited characters from the file name but not alter the text in cell B1. Any help is appreciated.
------------------------------------------------------------------------------------------------------------------------------------------------

Dim X As Integer
Dim astrLinks As Variant
Dim Path As String
Dim filename As String

For X = 2 To 200

If Sheets("Suppliers").Range("A" & X).Value <> "" Then

Sheets("Suppliers").Range("A" & X).Copy
Sheets("DA Summary").Range("B1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Sheets("Suppliers").Range("A" & X).Copy
Sheets("Price Point Data").Range("E1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Sheets(Array("Invoice", "DA Summary", "Price Point Data")).Copy
Sheets("DA Summary").Select
Range("A1").Select

astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks

Path = "C:\Test Reports\"
filename = Range("A1") & " " & Range("B1")
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.CommandBars("Workbook Links").Visible = False
Sheets("Invoice").Select
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("DA Chopper New.xlsm").Activate

End If

Next X


End Sub

 

[RobP]

Hi, perhaps adding this into your routine solves your problem. Its essentially an Array of "illegal characters" that you can add to or remove as you see fit. These are then removed from a variable in the following lines.

Dim X As Integer
Dim astrLinks As Variant
Dim Path As String
Dim filename As String


For X = 2 To 200

If Sheets("Suppliers").Range("A" & X).Value <> "" Then

Sheets("Suppliers").Range("A" & X).Copy
Sheets("DA Summary").Range("B1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Sheets("Suppliers").Range("A" & X).Copy
Sheets("Price Point Data").Range("E1").PasteSpecial (xlPasteValues)
Application.CutCopyMode = False

Sheets(Array("Invoice", "DA Summary", "Price Point Data")).Copy
Sheets("DA Summary").Select
Range("A1").Select

astrLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
ActiveWorkbook.BreakLink _
Name:=astrLinks(1), _
Type:=xlLinkTypeExcelLinks

'Remove special Chars from filename in B1
CellB1 = Range("B1")
IllegalArray = Array("[", "]", "<", ">", "|", "/", "*", "\", ".", "?", """")
For Y = LBound(IllegalArray) To UBound(IllegalArray)
    CellB1 = Replace(CellB1, IllegalArray(Y), "", 1)
Next Y
            

Path = "C:\Test Reports\"
filename = Range("A1") & " " & CellB1 'note variablename change here from your original cell Ref
ActiveWorkbook.SaveAs filename:=Path & filename & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.CommandBars("Workbook Links").Visible = False
Sheets("Invoice").Select
Range("A1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Windows("DA Chopper New.xlsm").Activate

End If

Next X

 

[The Ruff Report]

Worked perfectly. Thank you for your expertise.

 

[rlv01]

If it is always the same three illegal characters then another option is something like this.

    filename = Range("A1") & " " & Replace(Replace(Replace(Range("B1").Value, "[", ""), "]", ""), "/", "")

 

[The Ruff Report]

Thank you. That is helpful. I was playing around with replace and was wondering how to approach for multiple replaces. I didn't know it was nested like that. Makes sense.

 

[RobP]

Worked perfectly. Thank you for your expertise.

youre welcome. Glad it worked for you. Thanks for the feedback
Rgds
Rob