Extension renaming in Excel Column

[ContractKiller]

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

[ContractKiller]

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

[iburyak]

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

[ContractKiller]

=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

[ContractKiller]

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

[iburyak]

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.

[ContractKiller]

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

[Killer42]

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).

[ContractKiller]

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

[SammyB]

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.

Option Explicit
Sub Rename()
    Const FILE_PATH As String = "C:\Test\"
    Dim c As Range, sOld As String, sNew As String
    ChDir FILE_PATH
    For Each c In Cells(1, 1).CurrentRegion.Columns(1).Cells
        sOld = c.Text
        sNew = Split(sOld, ".")(0) & ".mpg"
        If Dir(sOld) <> "" Then
            Name sOld As sNew
        End If
    Next c
End Sub

[Killer42]

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.

[SammyB]

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?"

[Killer42]

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.

[ContractKiller]

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

[ContractKiller]

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.

Option Explicit
Sub Rename()
    Const FILE_PATH As String = "C:\Test\"
    Dim c As Range, sOld As String, sNew As String
    ChDir FILE_PATH
    For Each c In Cells(1, 1).CurrentRegion.Columns(1).Cells
        sOld = c.Text
        sNew = Split(sOld, ".")(0) & ".mpg"
        If Dir(sOld) <> "" Then
            Name sOld As sNew
        End If
    Next c
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

[Killer42]

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...

Option Explicit
Sub Rename()
  Dim c As Range, sOld As String, sNew As String
  For Each c In Cells(1, 1).CurrentRegion.Columns(1).Cells
    sOld = c.Text
    sNew = Split(sOld, ".")(0) & ".mpg"
    c.Value = sNew
  Next c
End Sub

(Or perhaps it's the .Text property you need to set. Have a play with it, and see where you end up.)

[Killer42]

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.

[SammyB]

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