Wildcards messing up macro?

[CaptainCsaba]

Hey Everyone!

I have a macro which uses "find and replace" with wildcards to change a few paragraphs of text on each page. There are many wildcards in it, but mostly ^13 which is the paragraph break.

At the end of the macro it generates a separate PDF file of every page. For each page it uses the the first paragraph as the file name. If I dont run the code with the wildcard find and replace it runs just fine and it creates the PDFs with the appropriate names. However If I Use the find and replace it includes all the lines I replaced int he file name.

It is a if ^13 and the regular "ENTER" button are not the same thing because if I replace it with a manual enter it runs fine. What is the problem?

 

[mole999]

post your macro, because its probably in there

 

[CaptainCsaba]

So there are many of these in the macro but this is one of the find and replace macros I have. The only thing different in the rest is that there are different adresses in this format:

    Selection.Find.ClearFormatting    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = _
            "CHASE NOMINEES LTD A/C <*>^13PO BOX 7732^13? CHASESIDE^13BOURNEMOUTH^13BH1 9XA"
        .Replacement.Text = _
            "CHASE NOMINEES LTD^13ALL DESIGNATIONS^13PO BOX 7732^13 1 CHASESIDE^13BOURNEMOUTH^13BH1 9XA^13Email: Disclosureteam@jpmchase.com"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchByte = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchFuzzy = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

After this runs There is this macro that uses the first paragraph in each page to name the excel file and the problem above happens:

Option Explicit

Sub SaveAsSeparatePDFsWithNameAsFirstParagraph()
Dim oSection As Section
Dim strName As String
Dim strDirectory As String
Dim oRng As Range
Dim vMsg As Long
    strDirectory = InputBox("Directory to save individual PDFs? " & _
                            vbNewLine & "(ex: C:\Users\Public)")
    If strDirectory = "" Then Exit Sub
    If Dir(strDirectory, vbDirectory) = "" Then
        vMsg = MsgBox("Please enter a valid directory.", vbOKCancel, "Invalid Directory")
        If vMsg = 0 Then Exit Sub
    End If
    For Each oSection In ActiveDocument.Sections
        On Error GoTo lbl_Exit
        Set oRng = oSection.Range.Paragraphs(1).Range
        oRng.End = oRng.End - 1
        strName = oRng.Text & ".pdf"
        strName = CleanFileName(strName, "pdf")
        oSection.Range.Select
        ActiveDocument.ExportAsFixedFormat OutputFileName:=strDirectory & "\" & strName, ExportFormat:=wdFormatPDF, Range:=wdExportCurrentPage
    Next oSection
lbl_Exit:
    Exit Sub
    Set oSection = Nothing
    Set oRng = Nothing
End Sub


Private Function CleanFileName(strFilename As String, strExtension As String) As String
'A function to ensure there are no illegal filename
'characters in a string to be used as a filename
'strFilename is the filename to check
'strExtension is the extension of the file
Dim arrInvalid() As String
Dim vfName As Variant
Dim lng_Name As Long
Dim lng_Ext As Long
Dim lng_Index As Long
    'Ensure there is no period included with the extension
    strExtension = Replace(strExtension, Chr(46), "")
    'Record the length of the extension
    lng_Ext = Len(strExtension)


    'Remove the path from the filename if present
    If InStr(1, strFilename, Chr(92)) > 0 Then
        vfName = Split(strFilename, Chr(92))
        CleanFileName = vfName(UBound(vfName))
    Else
        CleanFileName = strFilename
    End If


    'Remove the extension from the filename if present
    If Right(CleanFileName, lng_Ext + 1) = "." & strExtension Then
        CleanFileName = Left(CleanFileName, InStrRev(CleanFileName, Chr(46)) - 1)
    End If


    'Define illegal characters (by ASCII CharNum)
    arrInvalid = Split("9|10|11|13|34|42|47|58|60|62|63|92|124", "|")
    'Add the extension to the filename
    CleanFileName = CleanFileName & Chr(46) & strExtension
    'Remove any illegal filename characters
    For lng_Index = 0 To UBound(arrInvalid)
        CleanFileName = Replace(CleanFileName, Chr(arrInvalid(lng_Index)), Chr(95))
    Next lng_Index
lbl_Exit:
    Exit Function
End Function

 

[mole999]

are the ^13 coming out hard coded in the text?

 

[CaptainCsaba]

By hardcoded you mean that word included it after I wrote in what I wanted to change? Sorry I am not familliar with all the terms yet. I added those lines by going to the text replace function, enabling wildcards and writing them in like that (^13 inluded) Once a line was good and it was changing it I recorded the macro and added it to the code. You meant this by our question?

 

[CaptainCsaba]

So what happens is that If I write just anything into the first paragraph, the macro runs fine and it includes only that first paragraph in the title of the file. But if Use the macro with the wildcards It includes the whole wildcard part. So In the example above, normally the file name should only be "CHASE NOMINEES LTD".
But what happen is the file name becomes: "CHASE NOMINEES LTD_ALL DESIGNATIONS_PO BOX 7732_ 1 CHASESIDE_BOURNEMOUTH_BH1 9XA_Email_ Disclosureteam@jpmorganchase,com___________"

 

[CaptainCsaba]

Funnily enough, I tried around what I could do. If I delete the paragraph break after the first paragraph and do a manual one by hitting enter, now that page works just fine. Maybe the easiest solution would be just to have a code find the first paragraph break, delete it and rewrite it there. Can we do that somehow?

 

[CaptainCsaba]

Does someone have an idea?

 

[CaptainCsaba]

After trying out literally everything I figured out a simple solution to the problem. I had to go into find and replace. Find: ^13 Replace:^p. After this the macro ran fine.

 

[mole999]

Chr should have worked, just didn't get time to look up the right values