Solved

Remove dot and filename extension

Posted on 2013-12-01
6
153 Views
Last Modified: 2014-02-07
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
0
Comment
Question by:dgd1212
  • 3
  • 3
6 Comments
 
LVL 20

Expert Comment

by:CompProbSolv
Comment Utility
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
0
 

Author Comment

by:dgd1212
Comment Utility
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
0
 
LVL 20

Expert Comment

by:CompProbSolv
Comment Utility
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.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:dgd1212
Comment Utility
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
0
 
LVL 20

Accepted Solution

by:
CompProbSolv earned 500 total points
Comment Utility
My post did not include everything.  It appears that you have left off the following before "Next":
    FileCopy strFileCopyFrom, strFileCopyTo
0
 

Author Closing Comment

by:dgd1212
Comment Utility
Finally got it to work. Sorry for the delay.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now