Run-time error '52': Bad file name or number

wrslphil

New Member
Joined
Dec 30, 2016
Messages
3
Hi, I could do with a little help with this issue.

I'm currently developing a "Quick" file renaming system for some reports that are produced and currently renamed in numerical order.
I have set this up to read from the txt document for each file in the specified folder and get the report name. This text is then manipulated slightly to give a nicer looking report name.

The next stage was to copy the original file and paste it into a new file directory with the newly found and edited name.

Unfortunately I encounted Run-time error '52' after a few successful copies. The file name it crashed on is:
E:\Retail Implementation\Report output\_GL152A__00000044.txt

To me the filename seemed okay, so I did a test of just copying the file to the directory location that it would have been given as part of the full process (see sub: Test) and this worked perfectly. I

I have also tried to continue on error but only 700 or so files copied out of 5000 so I can't just omit the ones that don't work.

Some of the code is no doubt sloppy due to editing small parts for testing and debugging purposes.

I've tried lots of things but nothing seems to work.

Any ideas would be greatly appreciated.
Code:
Option Explicit

Function GetReportName(FileName As String)
Dim fso As New FileSystemObject
Dim Report As TextStream
Set Report = fso.OpenTextFile(FileName)
Dim FirstLine As String
FirstLine = Report.Read(10)
Report.Close
FirstLine = Replace(FirstLine, "(", "_")
FirstLine = Replace(FirstLine, ")", "_")
FirstLine = Replace(FirstLine, ":", "_")
FirstLine = Trim(FirstLine)
FirstLine = Replace(FirstLine, " ", "_")
FirstLine = Replace(FirstLine, "__", "_")
DoEvents
GetReportName = UCase(FirstLine)
FirstLine = ""
End Function
 

Sub SearchFiles()
Dim directory As String
directory = "Y:\Reports"
Dim File As Variant, Folder, Files
Dim fso As New FileSystemObject
Set Folder = fso.GetFolder(directory)
Set Files = Folder.Files
    For Each File In Files
        Dim FileString As String, NewName As String, outputfile As String
        FileString = File
        NewName = GetReportName(FileString) & "_" & Mid(FileString, 12, 8)
        outputfile = "E:\Retail Implementation\Report output" & NewName & ".txt"
        DoEvents
        fso.CopyFile File, outputfile, True
        outputfile = 0
        NewName = 0
        FileString = 0
    Next File
End Sub

Sub test()
Dim fso As New FileSystemObject
fso.CopyFile "Y:\Reports\00000044.txt", "E:\Retail Implementation\Report output\_GL152A__00000044.txt"
End Sub
 
Last edited by a moderator:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
This may or may not help but it's kinda cool. This will clean your filename of invalid characters:

Code:
Function CleanFileName(ByVal name As String) As String
    If name Is Nothing Then
        CleanFileName = vbNullString
    Else
        Dim badChar As Char
        For Each badChar In System.IO.Path.GetInvalidPathChars
            CleanFileName = Replace(name, badChar, vbNullString)
        Next
    End If
End Function
 
Upvote 0
Also, starting a file with an underscore can have special meaning to a Windows SharePoint Server:

"

  • If you use an underscore character (_) at the beginning of a file name, the file will be a hidden file."
 
Upvote 0
Thanks for your reply HackSlash. I have this without any underscores or spaces but still have a problem with this. It may be worth noting that the ones that are failing seem to be where the .txt file's report name is enclosed in brakcets, hence the manipulation to avoid a filename error. Not sure what is happening but it's almost like it is still holding the bracket value in memory rather than the substitution (Which of course it shouldn't be).
 
Upvote 0
Well you need to look at the variable contents before the copy command is executed. You can do that by setting a break-point in the code and analyzing it. You can also use "Debug.Print"

I can see that you make a reference to "FileString" twice. One is sanitized and one is not.

Code:
FileString = File
NewName = GetReportName(FileString) & "_" & Mid(FileString, 12, 8)

If "GetReportName" is where your filename sanitation occurs you would need to change the code like this:

Code:
FileString = GetReportName(File)
NewName = FileString & "_" & Mid(FileString, 12, 8)
 
Upvote 0
Well you need to look at the variable contents before the copy command is executed. You can do that by setting a break-point in the code and analyzing it. You can also use "Debug.Print"

I can see that you make a reference to "FileString" twice. One is sanitized and one is not.

Code:
FileString = File
NewName = GetReportName(FileString) & "_" & Mid(FileString, 12, 8)

If "GetReportName" is where your filename sanitation occurs you would need to change the code like this:

Code:
FileString = GetReportName(File)
NewName = FileString & "_" & Mid(FileString, 12, 8)



I'll try to debug this further, thank you.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,317
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top