Renaming Files in a Folder

[Jen]

Hi all,



I am a newbie to Access and I am trying to create a very simple Module that will rename a large number of files from a folder. I wrote the code for one file to test if it will work but it returns a runtime error 53 (where highlighted in red=. However, the file path for sFile is correct. Can anyone help me with that? Thank you very much in advance!

Sub renaming()

Dim sFile As String
Dim SFileNew As String
sFile = "C:\Users\Jen\Desktop\test\testfile.pptx"
SFileNew = "C:\Users\Jen\Desktop\test\newtestfile.pptx"

Name sFile As SFileNew


End Sub

[ranman256]

Paste this code and have it call
RenameFilesInDir

it will prompt for a file to choose, (you MUST ADD REFERENCE : Microsoft Office 11.0 Object Library)
then it scans the folder and rename all files by putting the word NEW in front of the old file name.
This can be changed to any word. (at ScanFolder "New", vRetval )

Public Sub RenameFilesInDir()
Dim vDir, vRetval, vFile

  'pick a file in a folder
vRetval = UserPick1File("*", vDir)
   
   'if picked , scan all files in it
If vRetval <> "" Then
    ScanFolder "New", vRetval
End If
End Sub


 ' pick any file in a folder to RENAME ALL files in folder
Public Function ScanFolder(ByVal pvNewWord, ByVal pvStartFile, Optional pvPattern)
Dim i As Integer, sDir As String
Dim fso, oFolder, oFile

  'get the folder name
i = InStrRev(pvStartFile, "\")          'not available in '97
If i > 0 Then vDir = Left(pvStartFile, i)

   'create folder object
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder(vDir)

   'scan all files
For Each oFile In oFolder.Files
  'If instr(oFile.Name , pvPattern)>0 Then
        vOld = vDir & oFile.Name
        vnew = vDir & pvNewWord & vOld
        
          'MsgBox vOld & vbCrLf & vnew
        Name vOld As vName
     
  'End If
Next

Set oFile = Nothing
Set oFolder = Nothing
Set fso = Nothing
End Function


'open file dialog box
Public Function UserPick1File(ByVal pvFilter, Optional pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String


With Application.FileDialog(msoFileDialogFilePicker)   'you MUST ADD REFERENCE : Microsoft Office 11.0 Object Library)
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "c:\"
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    
        If .Show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
End Function

[Jen]

Hi Ranman257,

Thank you very much for your help. Unfortunately, your code returns a runtime error 75 for "Name vOld As vName"...any hints on how to fix this? Apologies, I am really a newbie here.. .

Thanks in advance for your help!

cheers,
Jen

[ranman256]

The NAME statement is correct.
Error 75 is a file access error....do you have rights to rename, write to the folder?

[ssanfu]

There is nothing wrong with your code. I tested it (using a file on my desktop) and the file was renamed.
Do you have a reference set for "Microsoft Access 14.0 Object Library"?

Try moving "test\testfile.pptx" to "C:\".
The code would then be:

Sub renaming()

    Dim sFile As String
    Dim SFileNew As String

    sFile = "C:\testfile.pptx"
    SFileNew = "C:\newtestfile.pptx"

    Name sFile As SFileNew


End Sub

I tried a text file (test.txt) and a powerpoint (test.pptx) file. Both times the file was renamed.

[ssanfu]

@ranman256

Shouldn't the Name statement be:

Name vOld As vNew

instead of

Name vOld As vName

???