asked on

Remove dot and filename extension

Below is the macro:
Sub DuplicateRename()
strFileCopyFrom = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C3").Value
For Each c In Range(Range("A2"), Range("A" & Cells.Rows.Count).End(xlUp))
strFileCopyTo = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C3").Value & "_" & Format(c, "000") & ".pdf"
FileCopy strFileCopyFrom, strFileCopyTo
Next
End Sub

Example:

Original name: Maintenance Check Form.pdf

What the Macro creates:
Maintenance Check Form.PDF_SA1563HJ.pdf
Maintenance Check Form.PDF_SA1564HJ.pdf

Need to delete the ".pdf" in the ~center of file name or delete the four characters to left of the underscore ( _ )

The deleting of characters, I would guess, is more encompassing whereas deleting the .pdf would only apply to pdf files

Thanks

CompProbSolv

One method is to change:
strFileCopyFrom = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C3").Value
For Each c In Range(Range("A2"), Range("A" & Cells.Rows.Count).End(xlUp))
strFileCopyTo = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C3").Value & "_" & Format(c, "000") & ".pdf"

to:
strFileCopyFrom = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C3").Value
d=left(strFileCopyFrom,len(strFileCopyFrom)-4)

For Each c In Range(Range("A2"), Range("A" & Cells.Rows.Count).End(xlUp))
strFileCopyTo = d & "_" & Format(c, "000") & ".pdf"

The added line that creates d will strip off the trailing 4 digits. The shortened version will get used to create strFileCopyTo

dgd1212

ASKER

Tried suggestions but no results.

Just to clarify:
What the Macro creates:
Maintenance Check Form.PDF_SA1563HJ.pdf
Maintenance Check Form.PDF_SA1564HJ.pdf

What the result should be:
Maintenance Check Form_SA1563HJ.pdf
Maintenance Check Form_SA1564HJ.pdf

The trailing 4 digits must be kept

Thanks

CompProbSolv

What results did you get with the suggestion?

The trailing 4 digits that I was trying to remove should have come from Maintenance Check Form.PDF in the strFileCopyTo

Make sure you include the second change which is in the last line.

dgd1212

ASKER

Below is the macro:
Sub DuplicateRename()
strFileCopyFrom = ThisWorkbook.Path & "\" & Sheets("Sheet1").Range("C3").Value
d = Left(strFileCopyFrom, Len(strFileCopyFrom) - 4)

For Each c In Range(Range("A2"), Range("A" & Cells.Rows.Count).End(xlUp))
strFileCopyTo = d & "_" & Format(c, "000") & ".pdf"
Next
End Sub

When I run there is no created files in the directory. See attached.

Probably something obvious i'm missing....

Thanks
InstallForms.xlsm

ASKER CERTIFIED SOLUTION

CompProbSolv

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dgd1212

ASKER

Finally got it to work. Sorry for the delay.