Check if file exists based on the filename of a Workbook

[JazzSP8]

Hey All

I'm trying to come up with a way of checking a folder full of Workbooks and seeing if there is a corresponding text file in another folder.

I know how to check through a folder for the Workbooks, and I know how to check to see if a file exists but I just can't get them to work together :-/

What my code attempts to do is find a Workbook name and then take a part of that Workbook name and use it to look for a text file.

Going back to basics, this is the code that I came up with originally based on what I already knew;

Sub CheckSheets()
PAfilePath = "G:\Pricing Sheets\"
SubfilePath = "G:\Pricing Sheets\Price Files\"

PriceAnalyser = Dir$(PAfilePath & "*.xlsm")

Do While PriceAnalyser <> ""
PMName = Left(Mid(PriceAnalyser, InStr(PriceAnalyser, " - ") + 3, Len(PriceAnalyser)), Len(Mid(PriceAnalyser, InStr(PriceAnalyser, " - ") + 3, Len(PriceAnalyser))) - 5)
    If Dir(SubfilePath & PMName & ".txt") = "" Then '' Doesn't exist
        MsgBox PMName
    End If
PriceAnalyser = Dir$
Loop

End Sub

I'm getting the error "Invalid procedure call or argument" on;

PriceAnalyser = Dir$

After it's done the first loop.

I've tried various things to get it to work but all have failed, I've been working on the premise that because I use 'Dir' twice then something is going wrong.

The first Workbook gets named as I'd expect; in the test environment I've set up then the text file for this workbook doesn't exist and the Message Box gives me the name but then I get the error after that.

If I remove the IF statement then it loops through all of the Workbooks get named in my Message Box.

Any help would be much appreciated

Thanks for taking the time to look at this.

 

[bobsan42]

This error pops up when you try to call DIR w/o arguments after it has already returned an empty string to tell you it has finished listing the files/folders. I have colored three sections of your code below.
The problem that you have is because the red DIR is not a follow up of the green DIR as you may want it to be, but is actually a follow up of the blue DIR, which has already returned "" when didn't find the TXT file.

Sub CheckSheets()PAfilePath = "G:\Pricing Sheets\"
SubfilePath = "G:\Pricing Sheets\Price Files\"


[B][COLOR=#008000]PriceAnalyser = Dir$(PAfilePath & "*.xlsm")[/COLOR][/B]


Do While PriceAnalyser <> ""
PMName = Left(Mid(PriceAnalyser, InStr(PriceAnalyser, " - ") + 3, Len(PriceAnalyser)), Len(Mid(PriceAnalyser, InStr(PriceAnalyser, " - ") + 3, Len(PriceAnalyser))) - 5)
    If [B][COLOR=#0000ff]Dir(SubfilePath & PMName & ".txt") [/COLOR][/B]= "" Then '' Doesn't exist
        MsgBox PMName
    End If
[B][COLOR=#ff0000]PriceAnalyser = Dir$[/COLOR][/B]
Loop


End Sub

Consider using CreateObject("Scripting.FileSystemObject").GetFile(.........) to replace the blue dir (or use other method to check for TXT) if you want it to work.

 

[JazzSP8]

Ahhhhhhhhhhhhhhhhhh - Yes, of course makes sense now - Got it now

Brilliant, thanks for pointing me in the right direction.

 

[JazzSP8]

Works as expected now, as a follow up for if anyone comes across this from a search I changed the loop to;

Do While PriceAnalyser <> ""
PMName = Left(Mid(PriceAnalyser, InStr(PriceAnalyser, " - ") + 3, Len(PriceAnalyser)), Len(Mid(PriceAnalyser, InStr(PriceAnalyser, " - ") + 3, Len(PriceAnalyser))) - 5)
    
    CheckTxtFile = CreateObject("Scripting.FileSystemObject").fileExists(SubfilePath & PMName & ".txt")
           
    If CheckTxtFile = False Then '' Doesn't exit
            
        MsgBox PMName & " " & CheckTxtFile
  
    End If
  
PriceAnalyser = Dir$
Loop

Thanks again for the help

 

[Norie]

Why are you using FSO.FileExists and Dir?

Couldn't you do the whole thing with one or the other?

 

[JazzSP8]

Why are you using FSO.FileExists and Dir?

Couldn't you do the whole thing with one or the other?

As per my original code / post I was trying to use Dir twice the first time to get me a list of Workbooks in a folder, the second time to check a file exists based on their filenames. I've done both of these things before using Dir but never tried to do them both at the same time as I need to do on this occasion.

bobsan42 explained I couldn't use it this way as the first time (getting the list of Workbooks) was part of a loop, and then I was calling Dir again to check for a text file from within that loop which broke it (or that is the way I understood his explanation at least).

He suggested the FSO method within the loop so I didn't break it, so I Googled and got it working.

If you know different then I'm always happy to learn

 

[Norie]

If you used FSO you could get the entire contents of the folder without looping.

You would still need to loop through all the files to do what you want but it might be more straightforward.

If you can tell me the 2 folders involved, i.e the workbook folder and the text file folder, I could show you what I mean.

By the way, what part of the workbook name are you trying to match with a text file?

 

[JazzSP8]

Excellent, I've not used FSO much as I've never needed to before so any clues on how it can be used will be very much appreciated.

My Worksheets live in this folder G:\Pricing Sheets\

The text files live in G:\Pricing Sheets\Price Files\

("G" is a network drive if that makes any difference)

The Workbooks are named as "Pricing Analyser - Jazz SP8.xlsm", the text files are named as "Jazz SP8.txt".

(Again, if it makes any difference it could also be just a single name like "Pricing Analyser - Jazz.xlsm" and the file would then be named "Jazz.txt")

And there will be at least 10 Workbooks but no more than 20.

Thanks again for taking a look

 

[Norie]

What do you want to happen/do when a matching text file is found, or not found?

 

[JazzSP8]

It's if the file doesn't exist that I need to know.

I've already got the code done for what needs to happen if it doesn't exist so please don't worry about that - But to explain what I'm trying to achieve; The text file is produced by the Workbooks in the folder when a price list is updated.

If the price list hasn't been updated then I need to pull that information from the Workbook and reinclude it in the latest version of the Workbook when I next produce them. Some of the Workbooks are quite heavy with data and formulas and if I can just quickly check for a text file instead of opening each book it'll save time in an already lengthy process.

Our computers are underpowered and our network is slow at peak times, trying to open a large Workbook over the network can take about two minutes sometimes so I'm trying to avoid it if I can :-/