(Main Question Answered) Check File Open with random Path

I have an application that requires users to download a file from a location that is then imported at their execution into the application. I've done my best to counsel users to download the file to their desktop (for ease of locating) and to stick with a standard filename so that there are no errors. Some of these people just don't take direction well, will save the file wherever, or save the name inappropratly, or worse, open up multiple copies of the same file corrupting the name:
ie: FileName.xls, FileName.xls(1), FileName1

I'm trying to input some Error Handling to help these guys out because they panic when they recieve debug popups.

What I'm primarily attempting to do is detect if FILENAME.xls is open.

Please Login or Register  to view this content.

The above snippet was from a reference material by John Walkenbach that is usually spot on, however no matter what I do, it always believes the file is closed, even when I'm staring at it on my screen.

I also cannot just hardcode the path because some files are saved on the users local pc's and the path to their desktop or even their mydocuments is different than my own, and too many people could potentially be using the app at the same time to do different things so I can't have a single space for the imported files either.

What I need to do is test if the Filename only is open (in its regular state), by just testing the name without the path.

If its not, I can easily do the GetOpenFilename call to have them browse and locate the file and then open it, but this creates a second problem... if they named it different. Normally that'd be a cake, but, I need to store just the filename that is selected as its own variable publically so the application procedures can reference that for the duration of its running for that point in time.

Last question, is there a way for the vba to automatically search the users desktop for the typical filename as a last ditch effort to utilize and open if it is not found?

This function returns true if the specified workbook is open in the same instance of Excel.

Please Login or Register  to view this content.

The code you posted,

Please Login or Register  to view this content.

... can never succeed because it's comparing an uppercase word to a lowercase one.

The function works and should be usable. I've not tried functions before but have mulled them around, I guess now is better than never, and it will slim down the code a bit.

Do you mind if I utilize your function and change the name to incorporate it into the error handler format i was using already?

Do you mind if I utilize your function and change the name to incorporate it into the error handler format i was using already?

Feel free. You never have to ask that question here.