Why am I getting an nonexistent file name?

Question

I'm trying to run a code that will do some replaces in all excel files in a folder. First, here is the code:

Dim FileSystem As Object
Dim HostFolder As String
Dim StringA As String
Dim StringB As String

Sub Init()
    Application.DisplayAlerts = False

    HostFolder = "\\Pc-dell\d\Documentos\LSM2\CLIENTES\SRB CDT 2 PROGRESSO2\"

    Set FileSystem = CreateObject("Scripting.FileSystemObject")
    DoFolder FileSystem.GetFolder(HostFolder)

    Application.DisplayAlerts = True
End Sub

Sub DoFolder(Folder)
    Dim SubFolder

    For Each SubFolder In Folder.SubFolders
        DoFolder SubFolder
    Next

    Dim File

    For Each File In Folder.Files

        If Right(File.Name, 4) = ".xls" Or Right(File.Name, 5) = ".xlsx" Then

            With Workbooks.Open(File, False)


                Dim ws As Worksheet
                For Each ws In ActiveWorkbook.Worksheets

                    SwapStringsInActiveWorkbook "l1", "l2", ws
                    SwapStringsInActiveWorkbook "L1", "L2", ws
                    SwapStringsInActiveWorkbook "l 1", "l 2", ws
                    SwapStringsInActiveWorkbook "L 1", "L 2", ws
                    SwapStringsInActiveWorkbook "loja1", "loja2", ws
                    SwapStringsInActiveWorkbook "LOJA1", "LOJA2", ws
                    SwapStringsInActiveWorkbook "loja 1", "loja 2", ws
                    SwapStringsInActiveWorkbook "LOJA 1", "LOJA 2", ws
                    SwapStringsInActiveWorkbook "Loja1", "Loja2", ws
                    SwapStringsInActiveWorkbook "Loja 1", "Loja 2", ws
                    SwapStringsInActiveWorkbook "LOJA1", "LOJA2", ws
                    SwapStringsInActiveWorkbook "LOJA 1", "LOJA 2", ws

                Next ws

            End With

            ActiveWorkbook.Save
            ActiveWorkbook.Close

        End If
Continue:
    Next
End Sub

Sub SwapStringsInActiveWorkbook(StringA As String, StringB As String, ws As Worksheet)

    On Error Resume Next
    ws.Cells.SpecialCells(xlCellTypeConstants).Replace What:=StringA, Replacement:="_AUXTEMPREPL_", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

    On Error Resume Next
    ws.Cells.SpecialCells(xlCellTypeConstants).Replace What:=StringB, Replacement:=StringA, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

    On Error Resume Next
    ws.Cells.SpecialCells(xlCellTypeConstants).Replace What:="_AUXTEMPREPL_", Replacement:=StringB, LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=True, SearchFormat:=False, ReplaceFormat:=False

    On Error GoTo 0
End Sub

But at a certain point, after some replacements, it gives me an error saying that it couldn't open a file:

"Excel couldn't open the file '~$PLAN COMPARAT de CUSTOS Rancho Bom Loja2-2015.xlsx' because the file format or the file extension is not valid. Check if the file is not corrupted and if the extension matches the file format." (this is a translation from a error message that was in portuguese, sorry I couldn't change the language)

The fact is that I did a search on this folder, and there is no such file. There is a file called "PLAN COMPARAT de CUSTOS RB-L2-2015.xlsx" though, which is an abbreviation. Maybe the file it is trying to open is and old file that doesn't exist anymore?

I had a script to do replacements in word files before as well, and it would also try to open some files that doesn't exist or it would try to open a file called "~$" plus the name of a file that existed. Where is this "~$" coming from?

I need some clues about that.

the `.xlsl` is a locked version of a file. most likely the file was saved when Excel crashed at some point and it is hidden in the drive. If you choose the option to show hidden files in Internet Explorer you will see it. You can do a check I believe to test for files that are hidden (or at least check for the `~$` or `.xlsl` in the file name and if that's there skip it (or perhaps even delete it). — Scott Holtzman, Feb 19 '16 at 14:40
I'm really sorry, but the .xlsl was a typo of mine. I meant "xslx" indeed. — Felipe Müller, Feb 19 '16 at 14:48
I still think your issue is hidden temp files that are being found when you cycle through all files in a folder. Personally, I don't think this is a coding issue. — Chrismas007, Feb 19 '16 at 14:57
This isn't a coding issue, as @ScottHoltzman indicated - the `~$` prefix relates to a locked file, which any app would error trying to open. — SierraOscar, Feb 19 '16 at 15:08
If you're going to modify files, it's best to collect all of the file names *before* you start any processing: then you don't run the risk of having the lock files caught up in your processing, or having Dir() give the the same file twice (because the sort order is not guaranteed to not involve the last update date...) E.g. see http://stackoverflow.com/questions/26824225/loop-through-folder-renaming-files-that-meet-specific-criteria-using-vba/26924038#26924038 — Tim Williams, Feb 19 '16 at 17:03

score 1 · Accepted Answer · answered Feb 19 '16 at 15:26

1

I am only suggesting this until you find/remove the miscreant files. Replace:

If Right(File.Name, 4) = ".xls" Or Right(File.Name, 5) = ".xlsx" Then

with:

If Left(File.Name, 2) <> "~$" And (Right(File.Name, 4) = ".xls" Or Right(File.Name, 5) = ".xlsx") Then

this should only be a temporary work-around.

answered Feb 19 '16 at 15:26

Gary's Student

95,722
10
59
99

Ok. Thank you. I tested it and it is working now. I didn't understand why this is a temporary work-around though. – Felipe Müller Feb 19 '16 at 15:43
1

@FelipeMüller Although the work-around will solve your problem, using it will hide the underlying issue............if you do not care about the the existence of junk files then go ahead and use it................if you want to remove the junk, it would be easy to write a new sub to locate the all the junk in all subfolders. – Gary's Student Feb 19 '16 at 15:50
oh ok, I didn't actually know they were junk files. I thought they were needed for something. – Felipe Müller Feb 20 '16 at 00:55

Why am I getting an nonexistent file name?

1 Answers1