Solved

Remove dot and filename extension

Posted on 2013-12-01
6
171 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 21

Expert Comment

by:CompProbSolv
ID: 39689084
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
ID: 39689558
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 21

Expert Comment

by:CompProbSolv
ID: 39690461
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dgd1212
ID: 39691595
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 21

Accepted Solution

by:
CompProbSolv earned 500 total points
ID: 39693227
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
ID: 39843405
Finally got it to work. Sorry for the delay.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

734 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