none
How do I rename a file in VBA

    Question

  •  

    Hi

    I have an excel sheet that contains data. as per the data form one perticular cell. i need to rename a wave and a word file. Is this possible in VBA.

     

    Thanks
    Riya

    Tuesday, February 27, 2007 2:54 PM

Answers

  • Taken from the excel help files....

     

    Name Statement Example

    This example uses the Name statement to rename a file. For purposes of this example, assume that the directories or folders that are specified already exist. On the Macintosh, “HD:” is the default drive name and portions of the pathname are separated by colons instead of backslashes.

    Dim OldName, NewName
    OldName = "OLDFILE": NewName = "NEWFILE"    ' Define file names.
    Name OldName As NewName    ' Rename file. 
    
    OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
    Name OldName As NewName    ' Move and rename file.
    
    Tuesday, February 27, 2007 6:58 PM

All replies

  • Taken from the excel help files....

     

    Name Statement Example

    This example uses the Name statement to rename a file. For purposes of this example, assume that the directories or folders that are specified already exist. On the Macintosh, “HD:” is the default drive name and portions of the pathname are separated by colons instead of backslashes.

    Dim OldName, NewName
    OldName = "OLDFILE": NewName = "NEWFILE"    ' Define file names.
    Name OldName As NewName    ' Rename file. 
    
    OldName = "C:\MYDIR\OLDFILE": NewName = "C:\YOURDIR\NEWFILE"
    Name OldName As NewName    ' Move and rename file.
    
    Tuesday, February 27, 2007 6:58 PM
  • Thanks Dustin

    I did find the same from excel help files, but my situation is different there are around 200 records in each excel file. I have to first reach for the file, wave and doc files(e.g name as per column A3) in a particular folder and then replace that then with some other cell refrence (e.g name as per in column D3). Is this possible.

    sample of data.


    FILE WT LEVEL Files Name
    1 43424680 Discharge Summary B OB_Discharge_B
    2 44276978 Discharge Summary B OB_Discharge_B
    3 51019478 Discharge Summary B OB_Discharge_B
    4 51040367 Discharge Summary B OB_Discharge_B
    5 51040383 Discharge Summary B OB_Discharge_B
    6 51051100 Discharge Summary B OB_Discharge_B

    Need to check for the name "43424680" in a folder (there will be 2 files one wave and the other doc file) and then change the name to "OB_Discharge_B".

    Is this possible. I could not find any help source for this, and I am not that good in VBA. Just write small macros to make my task easier and faster.

    Thanks for your help
    Riya

    Wednesday, February 28, 2007 3:38 AM
  • Sure you can....

    When doign the name function, simply get the name that the file will have from your cell.

    Thursday, March 01, 2007 3:52 PM
  • I think this will do what your looking for

    Public Sub RenameFiles()

    Dim bTest As Boolean
    Dim iCtr As Integer
    Dim OldFile As String
    Dim NewFile As String

    bTest = True
    iCtr = 3
    Do While bTest = True
        If Worksheets(1).Range("A" & iCtr).Value > 0 Then
            OldFile = _
            "C:\TestFiles\" & Worksheets(1).Range("A" & iCtr).Value & ".wav"
            NewFile = _
            "C:\TestFiles\" & Worksheets(1).Range("D" & iCtr).Value & ".wav"
            Name OldFile As NewFile
            OldFile = _
            "C:\TestFiles\" & Worksheets(1).Range("A" & iCtr).Value & ".doc"
            NewFile = _
            "C:\TestFiles\" & Worksheets(1).Range("D" & iCtr).Value & ".doc"
            Name OldFile As NewFile
            iCtr = iCtr + 1
        Else
            bTest = False
        End If
    Loop
    End Sub

    Friday, March 02, 2007 8:27 PM
  •  

    Hey Dustin

    Thanks a lot dude. But i have not yet tried it. will have to try it when i get some peace full time.

    Thanks for your feedback

    Riya

    Monday, March 05, 2007 1:03 PM
  •  

    Hey Matthe

    I have not tired yet will try it when i get my time off from the trainng. Thanks a lot. If this code works then you have made my life so easy.......

    Thanks again

    Riya 

    Tuesday, March 06, 2007 4:05 AM