Remove a Specific Character of file name

[Priyantha]

Dear All,

There are many PDF files in a folder & some of them have "N" at the end of the name (e.g. 37224N.pdf, 50860N.pdf). An Excel sheet has a number of file names in a column and the files related to those file names are in a special folder. Regarding the selected file names, some file names in the folder have the letter ''N'' at the end and some file names do not. So I want to remove the letter ''N'' from the file name that has the letter ''N''.

For this purpose, I used below VBA code below, but not work correctly.

Dim ws6 As Worksheet
Dim LastDot As Long, Path As String, Filename As String, NewFilename As String, j As Long, last_row As Long

Set ws6 = ThisWorkbook.Sheets("Email")
Path = "C:\Users\User\Desktop\Maling Payadvice\With Gmail 01\PaySlips\"
last_row = ws6.Range("B" & ws6.Rows.Count).End(xlUp).Row

For j = 4 To last_row
If Filename = Dir(Path & ws6.Cells(j, 2).Value & "*N.pdf") Then
LastDot = InStrRev(Filename, ".")
NewFilename = Left(Filename, 5) & ".pdf"
Name Path & Filename As Path & NewFilename
NewFilename = Dir(Path & "*.pdf")
End If
Next j

please help me correct this,

Thanks,

Priyantha.

[HansV]

Do the names in the Excel sheet have an N at the end?

[HansV]

Assuming that the names in the sheet do NOT end in "N":

Code: Select all

Sub Test()
    Dim ws6 As Worksheet
    Dim Path As String
    Dim fn As String
    Dim Filename As String
    Dim NewFilename As String
    Dim j As Long
    Dim last_row As Long

    Path = "C:\Users\User\Desktop\Maling Payadvice\With Gmail 01\PaySlips\"

    Set ws6 = ThisWorkbook.Sheets("Email")
    last_row = ws6.Range("B" & ws6.Rows.Count).End(xlUp).Row

    For j = 4 To last_row
        fn = ws6.Cells(j, 2).Value
        Filename = Dir(Path & fn & "N.pdf")
        If Filename <> "" Then
            NewFilename = fn & ".pdf"
            Name Path & Filename As Path & NewFilename
        End If
    Next j
End Sub

[HansV]

You might even try

Code: Select all

Sub Test()
    Dim ws6 As Worksheet
    Dim Path As String
    Dim fn As String
    Dim j As Long
    Dim last_row As Long

    Path = "C:\Users\User\Desktop\Maling Payadvice\With Gmail 01\PaySlips\"

    Set ws6 = ThisWorkbook.Sheets("Email")
    last_row = ws6.Range("B" & ws6.Rows.Count).End(xlUp).Row

    On Error Resume Next

    For j = 4 To last_row
        fn = ws6.Cells(j, 2).Value
        Name Path & fn & "N.pdf" As Path & fn & ".pdf"
    Next j
End Sub

[snb]

Should do the trick:

Code: Select all

Sub M_snb()
  c00 = "C:\Users\User\Desktop\Maling Payadvice\With Gmail 01\PaySlips\"
  sn=ThisWorkbook.Sheets("Email").cells(1).currentregion.columns(2)

  For j = 4 To ubound(sn)
    If Dir(c00 & sn(j,1) & "N.pdf")<>"" Then Name c00 & sn(j,1) & "N.pdf" As  c00 & sn(j,1) & ".pdf"
  next
End Sub

[Priyantha]

Dear All,

The Above Code is working correctly. Thanks for your sport .



Priyantha