Replace Illegal Characters Off Certain Cells when Creating and Saving PDF

USFengBULLS

Board Regular
Joined
May 7, 2018
Messages
66
Office Version
  1. 365
Platform
  1. Windows
Hello,
I have a VBA Code that automatically makes a new folder inside of the folder that the work book is in, names that new folder based on what they have in 3 certain cell and then it creates a PDF, Names that new PDF based off 4 cells on the sheet and then saves that PDF into that new Folder Directory. Now I have a function that looks for and replaces special characters that are with in the strings that are naming the folder and PDF based off a series of cell on the worksheet. It is working great for the when it creates the new folder (if anyone puts a special character in that cell it automatically replaces it with and underscore) but, when it comes to naming the PDF the errHandler kicks in and says "Could not Create PDF" each time I execute these series of procedures. (Bare with me, these two procedures are calling on Public Variables further up in the module so I have added the first 2 procedures for reference to where some of these variables are coming from further down). The Function ReplaceIllegalCharacters and Public Sub PDF_Save () is what I have the issue on when it names the PDF. I think the issue is something to do with the ReplaceIllegalCharacters(strName, "_") part in the FileName portion of the PDF_Save (). Can anyone guide me on how to change this so it will name the PDF without any special characters in it, Like I said it works fine when creating and naming the folder but falls short at the naming and saving PDF. Thanks!

VBA Code:
Public P_Number As Range
Public RFI_Number As Range
Public RFI_Date As String
Public RFI_Overview As String
Public MR_Employee As String
Public P_Contact As String
Public wsA As Worksheet
Public wbA As Workbook
Public strName As String
Public strPath As String
Public strFolderName As String
Public strFolderPath As String

Public Sub Project_Info()
Set P_Number = Sheets("RFI LOG").Range("A1")
Set RFI_Number = Sheets("NEW FORM-RESET").Range("D10")
RFI_Date = Sheets("NEW FORM-RESET").Range("C14")
MR_Employee = Sheets("NEW FORM-RESET").Range("B5")
RFI_Overview = Sheets("NEW FORM-RESET").Range("B12")
P_Contact = Sheets("NEW FORM-RESET").Range("B14")
End Sub

Function ReplaceIllegalCharacters(strIn As String, strChar As String) As String
    Dim strSpecialChars As String
    Dim i As Long
    strSpecialChars = "~""#%&*:<>?{|}/\[]" & Chr(10) & Chr(13)

    For i = 1 To Len(strSpecialChars)
        strIn = Replace(strIn, Mid$(strSpecialChars, i, 1), strChar)
    Next

    ReplaceIllegalCharacters = strIn
End Function


Public Sub PDF_Save()

'Saves the New RFI Worksheet as PDF
Set wbA = ThisWorkbook
Set wsA = ActiveSheet

strPath = wbA.Path
strName = P_Number & " RFI-" & wsA.Name & " " & RFI_Overview & " " & P_Contact & " " & Format(Date, "mm-dd-yyyy") & ".pdf"
strFolderName = P_Number & " RFI-" & wsA.Name & " " & RFI_Overview
strFolderPath = wbA.Path & "\" & strFolderName

On Error GoTo errHandler
    MkDir (strPath & "\" & ReplaceIllegalCharacters(strFolderName, "_")) 'Creats a new folder in current workbook folder and names it. Also replaces Illegal Characters when naming Folder.
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strFolderPath & "\" & ReplaceIllegalCharacters(strName, "_"), _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
'confirmation message with file info
    MsgBox "PDF file has been created: " _
        & strName _
        & strFolderPath
      
exitHandler:
    Exit Sub
errHandler:
    MsgBox "Could not create PDF file"
    Resume exitHandler

End Sub
 
Last edited:

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
You're replacing illegal characters when you make the folder, but still using the unadjusted version of strFolderName for the filepath when you try to create the pdf
 
Upvote 0
Oh lord, I don't know why I overlooked that...Thank you Fluff, it's working now.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top