File not found (error 53) when using Name command.

[Karl1135]

I am trying to remove the extra periods in a downloaded file so TransferText will work to import the file. TransferText doesn' t like the extra periods. The following code returns file not found (error 53). The files exists. Can copy the file name in Windows explorer and file opens. Also Dir works.

Dim OldName As String, NewName As String


OldName = Me.AmzFileName
NewName = Me.NewAmzFileName


Debug.Print OldName
Debug.Print NewName
Debug.Print Dir(OldName)

Debug.print shows


C:\Users\Karl\Downloads\AMZOrders02-24\Retail.CustomerReturns.1\Retail.CustomerReturns .1.csv
C:\Users\Karl\Downloads\AMZOrders02-24\RetailCustomerReturns1\RetailCustomerReturns1.c sv
Retail.CustomerReturns.1.csv

[June7]

NewName removes periods from folder name as well as file name. Did you actually rename folder as well as file? I don't see any code that renames anything.

Please post code between CODE tags to retain formatting and readability.

[Karl1135]

I am trying to remove the extra periods in a downloaded file so TransferText will work to import the file. TransferText doesn' t like the extra periods. The following code returns file not found (error 53). The files exists. Can copy the file name in Windows explorer and file opens. Also Dir works.

Dim OldName As String, NewName As String


OldName = Me.AmzFileName
NewName = Me.NewAmzFileName


Debug.Print OldName
Debug.Print NewName
Debug.Print Dir(OldName)

Debug.print shows
C:\Users\Karl\Downloads\AMZOrders02-24\Retail.CustomerReturns.1\Retail.CustomerReturns .1.csv
C:\Users\Karl\Downloads\AMZOrders02-24\RetailCustomerReturns1\RetailCustomerReturns1.c sv
Retail.CustomerReturns.1.csv

Edit Added:
Name OldName As NewName

[June7]

As I already pointed out, your code removes periods from folder name in the string but does not actually rename the folder so of course the Name command cannot find that path for renaming file.

Why create folder that has same name as file?

Quoting your own post is not placing code between CODE tags. Unnecessary to quote your post.

[moke123]

Have you tried FSO?

Something like this will rename the file in the same folder.
You can modify it with various options.

Private Sub sRenameCsv(FPath As String, NewName As String)

    Dim fil As Object, FExt As String, PFol As String
    
    'late binding
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
    
    'early binding
    'Dim fso As New FileSystemObject

    Set fil = fso.GetFile(FPath)

    FExt = "." & fso.GetExtensionName(FPath)  'get the file extension

    PFol = fso.GetParentFolderName(FPath)     'get the parent folder path
    
    If Not fso.FileExists(fso.BuildPath(PFol, NewName & FExt)) Then  'check that the re-named file does not exist
    
        fso.MoveFile FPath, fso.BuildPath(PFol, NewName & FExt)  'move the file to same folder with new name
    Else
        MsgBox "File already exists"
    End If

    Set fso = Nothing

End Sub

If the periods(.) in the directory names are a problem, you can rename and move the file to a different folder

Private Sub sRenameMoveCsv(FPath As String, NewFolderPath As String, NewName As String)
'FPath - Path to the file to rename
'NewFolderPath - path to the folder to save file to.
'NewName - new file name


    Dim fil As Object, FExt As String, PFol As String
    
    'late binding
        Dim fso As Object
        Set fso = CreateObject("Scripting.FileSystemObject")
    
    'early binding
    'Dim fso As New FileSystemObject


    Set fil = fso.GetFile(FPath)


    FExt = "." & fso.GetExtensionName(FPath)  'get the file extension
   
    If Not fso.FileExists(fso.BuildPath(NewFolderPath, NewName & FExt)) Then  'check that the re-named file does not exist
    
        fso.MoveFile FPath, fso.BuildPath(NewFolderPath, NewName & FExt)  'move the file to new folder with new name
    Else
        MsgBox "File already exists"
    End If


    Set fso = Nothing


End Sub

[Karl1135]

As I already pointed out, your code removes periods from folder name in the string but does not actually rename the folder so of course the Name command cannot find that path for renaming file.

Why create folder that has same name as file?

Quoting your own post is not placing code between CODE tags. Unnecessary to quote your post.

Only option I see is "Reply with quote". Don't see Reply only. New users may not see that.
New Destination folder exists
Solved using another posters suggestion to use FSO.

[Welshgasman]

So what does this picture show?