SOLVED

Need help to replace certain string like "/" in the cell to underscore during save as pdf

Copper Contributor

Hi everyone, 
I'm not very good at excel VBA and hoping you guys can help me to replace certain characters like "/" to "_" if the character exists in the cell that I wanted to print, like in the code example below.

 

this code will function properly if cell f10 just contains the normal string to be save as file name and will giving an error if characters such as "/" exists in it. How do I fix it?

Thanks

 

 

 

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

Set wbA = ActiveWorkbook
Set wsA = ActiveSheet

'get active workbook folder, if saved
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If
strPath = strPath & "\"

'Help needed here
strName = wsA.Range("f10").Value _
          & " - " & wsA.Range("d3").Value
'create default name for savng file
strFile = strName & ".pdf"
strPathFile = strPath & strFile

'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    
   Next i
    MsgBox "All PDF files have been created: "
    
Else
End If
Range("d10").Select
End Sub

 

 

 

 

2 Replies
best response confirmed by shaifful (Copper Contributor)
Solution

@shaifful 

Change

strName = wsA.Range("f10").Value _
          & " - " & wsA.Range("d3").Value

to

strName = Replace(wsA.Range("f10").Value _
          & " - " & wsA.Range("d3").Value, "/", "_")

Thank you very much @Hans Vogelaar , it works like charm. This really helps me and my fellow friend. Thanks again for your help. 

1 best response

Accepted Solutions
best response confirmed by shaifful (Copper Contributor)
Solution

@shaifful 

Change

strName = wsA.Range("f10").Value _
          & " - " & wsA.Range("d3").Value

to

strName = Replace(wsA.Range("f10").Value _
          & " - " & wsA.Range("d3").Value, "/", "_")

View solution in original post