By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,098 Members | 1,405 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,098 IT Pros & Developers. It's quick & easy.

Extension renaming in Excel Column

P: 10
What I'm looking for is a VB function that would rename all the file names in the same column to a different extension, bellow you see the different filenames I have that they are all .jpg and what I would like to do is create a function that will change it to .mpg like you see in the other table. This is Windows Xp on Office 2003 Excel. Using VB for code.

Filename
9WS705001M1.jpg
9WS705002M1.jpg
9WS705003M1.jpg
9WS705004M1.jpg
9WS705005M1.jpg
9WS705064M1.jpg
9WS705065M1.jpg
9WS705006M1.jpg
9WS705007M1.jpg
9WS705008M1.jpg
9WS705009M1.jpg


Filename
9WS705001M1.mpg
9WS705002M1.mpg
9WS705003M1.mpg
9WS705004M1.mpg
9WS705005M1.mpg
9WS705064M1.mpg
9WS705065M1.mpg
9WS705006M1.mpg
9WS705007M1.mpg
9WS705008M1.mpg
9WS705009M1.mpg
Mar 28 '07 #1
Share this Question
Share on Google+
17 Replies


P: 10
What I'm looking for is a VB function that would rename all the file names in the same column to a different extension, bellow you see the different filenames I have that they are all .jpg and what I would like to do is create a function that will change it to .mpg like you see in the other table. This is Windows Xp on Office 2003 Excel. Using VB for code.

Filename
705001M1.jpg
705002M1.jpg
705003M1.jpg
705004M1.jpg
705005M1.jpg
705064M1.jpg
705065M1.jpg
705065M1.jpg
705007M1.jpg
705008M1.jpg
705009M1.jpg


Filename
705001M1.mpg
705002M1.mpg
705003M1.mpg
705004M1.mpg
705005M1.mpg
705064M1.mpg
705065M1.mpg
705006M1.mpg
705007M1.mpg
705008M1.mpg
705009M1.mpg


Sorry i don't know if the file names matter here are the correct ones sorry about that
Mar 28 '07 #2

iburyak
Expert 100+
P: 1,017
=MID(A2, 1, LEN(A2) - 3) & "mpg"
Mar 28 '07 #3

P: 10
=MID(A2, 1, LEN(A2) - 3) & "mpg"

I sorry but this doesn't make any sense to me and remember this has to be VB code
Mar 28 '07 #4

P: 10
I sorry but this doesn't make any sense to me and remember this has to be VB code
Can some please help me out with this question please thanks
Apr 12 '07 #5

iburyak
Expert 100+
P: 1,017
It is an Excel formula

1. Open Excel
2. Put 705001M1.jpg into A2 cell of excel
3. Paste formula in any cell in the same row and see what happens.
Apr 12 '07 #6

P: 10
It is an Excel formula

1. Open Excel
2. Put 705001M1.jpg into A2 cell of excel
3. Paste formula in any cell in the same row and see what happens.
The thing is I do not need a formula for excel i need VB code that will do thast will change it from .jpg to .mpg
Apr 16 '07 #7

Expert 5K+
P: 8,434
The thing is I do not need a formula for excel i need VB code that will do thast will change it from .jpg to .mpg
Why not just record a macro which does a search and replace. Tell the replace function to replace ".jpg" with ".mpg". You will then have the code you need recorded in the macro, and can do whatever you like with it.

Recording is a useful technique to quickly create all sorts of code in Excel (and Word).
Apr 17 '07 #8

P: 10
Why not just record a macro which does a search and replace. Tell the replace function to replace ".jpg" with ".mpg". You will then have the code you need recorded in the macro, and can do whatever you like with it.

Recording is a useful technique to quickly create all sorts of code in Excel (and Word).
Great If you think that this woul be the best way to then I will go ahead and try that thanks
Apr 17 '07 #9

SammyB
Expert 100+
P: 807
What I'm looking for is a VB function that would rename all the file names in the same column to a different extension....
Assuming your list starts in A1 & there is an empty cell at the end of the list, here is the code to physically rename the files in your list.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Sub Rename()
  3.     Const FILE_PATH As String = "C:\Test\"
  4.     Dim c As Range, sOld As String, sNew As String
  5.     ChDir FILE_PATH
  6.     For Each c In Cells(1, 1).CurrentRegion.Columns(1).Cells
  7.         sOld = c.Text
  8.         sNew = Split(sOld, ".")(0) & ".mpg"
  9.         If Dir(sOld) <> "" Then
  10.             Name sOld As sNew
  11.         End If
  12.     Next c
  13. End Sub
Apr 17 '07 #10

Expert 5K+
P: 8,434
Bet my way's faster! :)

Seriously though, I hope ContractKiller realises that your version will actually rename the files, not just change the names in the spreadsheet. Do we know whether that was the requirement? If so, well spotted Sammy. I thought CK just wanted to change the cells in Excel.

Checking that the file exists was a nice touch. It might also be a good idea to ensure that the new name doesn't already exist.

One thing to keep in mind, ContractKiller. This code will need to be adjusted slightly if there's any possibility that the original filenames may include a dot, since it is splitting the name at the first dot. Normally, of course, you'd expect this to work OK.
Apr 17 '07 #11

SammyB
Expert 100+
P: 807
Bet my way's faster! :)
Doing nothing is always faster! :D
Isn't that code for rename weird! Must be original BASIC (Beginner's All-purpose Symbolic Instruction Code). Did you ever run BASIC on Radio Shack's TRS-80? If you had the instruction "GO TO 100" and there wasn't a line numbered 100, the error message was "How?"
Apr 18 '07 #12

Expert 5K+
P: 8,434
Doing nothing is always faster! :D
Hey! Whaddaya mean, nothing? I just suggested using a search&replace function rather than coding a loop. That's efficiency, that is. :)

Isn't that code for rename weird! Must be original BASIC (Beginner's All-purpose Symbolic Instruction Code). Did you ever run BASIC on Radio Shack's TRS-80? If you had the instruction "GO TO 100" and there wasn't a line numbered 100, the error message was "How?"
No, I started out on Applesoft BASIC, then went to the IBM flavour of (presumably MS) BASIC, then BASICA, QuickBasic, Visual Basic.

Love the error message, though. When I develop utilities only for my own use or other developers around here, I sometimes use message like that, too. Ones that you would never show to an end-user, but which actually make perfect sense. So I sometimes get messages from the computer like "Huh? With what, dopey?!". :D

Certainly Applesoft BASIC didn't have a KILL statement - it didn't even know there was an operating system, so had no file-related statements:p. Don't remember about the IBM one.
Apr 18 '07 #13

P: 10
Thanks Guys Killer im sorry to tell you but when I used yours what it did its jsut a formula but every time i errase the information the formula will get errased too. But thanks to both of you
Apr 21 '07 #14

P: 10
Assuming your list starts in A1 & there is an empty cell at the end of the list, here is the code to physically rename the files in your list.
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Sub Rename()
  3.     Const FILE_PATH As String = "C:\Test\"
  4.     Dim c As Range, sOld As String, sNew As String
  5.     ChDir FILE_PATH
  6.     For Each c In Cells(1, 1).CurrentRegion.Columns(1).Cells
  7.         sOld = c.Text
  8.         sNew = Split(sOld, ".")(0) & ".mpg"
  9.         If Dir(sOld) <> "" Then
  10.             Name sOld As sNew
  11.         End If
  12.     Next c
  13. End Sub
Ok but this code tells me how to change filenames from a folder not how change the file names on a column from excel is you can please let me know that will be great thanks
Apr 21 '07 #15

Expert 5K+
P: 8,434
Ok but this code tells me how to change filenames from a folder not how change the file names on a column from excel is you can please let me know that will be great thanks
Well, you could try replacing the Name with a simple value change.

For example...
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Sub Rename()
  3.   Dim c As Range, sOld As String, sNew As String
  4.   For Each c In Cells(1, 1).CurrentRegion.Columns(1).Cells
  5.     sOld = c.Text
  6.     sNew = Split(sOld, ".")(0) & ".mpg"
  7.     c.Value = sNew
  8.   Next c
  9. End Sub
(Or perhaps it's the .Text property you need to set. Have a play with it, and see where you end up.)
Apr 21 '07 #16

Expert 5K+
P: 8,434
Thanks Guys Killer im sorry to tell you but when I used yours what it did its jsut a formula but every time i errase the information the formula will get errased too. But thanks to both of you
I don't really get your meaning. I suggested making a macro (in other words, VBA code) which performs the function.
Apr 21 '07 #17

SammyB
Expert 100+
P: 807
Ok but this code tells me how to change filenames from a folder not how change the file names on a column from excel is you can please let me know that will be great thanks
Very :confused:
Please restate what you want to do
Apr 21 '07 #18

Post your reply

Sign in to post your reply or Sign up for a free account.