Public Function gblnFileExists

Topic

New Reply

I don’t suppose you know offhand whether excel 97 vba displays the same error?
(I suppose I could investigate myself, but if you already know…)

Brooke

Reply | Quote

Replies

Al,

Thanks for posting this.

One possible little tweak:
Add an “Exit For” statement immediately after “gblnFileExists = True” – that way as soon as there’s a positive ‘hit’, the loop gets exited and the function can finish up.
Probably would make little or no difference in this case, where .FoundFiles.Count is likely to be a small number, but would make a difference in a situation where the foundfiles count were larger.

Gary

Reply | Quote

Seems like a lot of trouble for a file exist function. Maybe I’m missing something. If so, please enlighten me.

What do you think of this as a FileExist function:

Public Function fFileExists(strFullName)

fFileExists = cbool(len(dir(strFullName)))

End Function

Could it be that not all of Office supports Dir() but all apps support application.filesearch?

Reply | Quote

Safe bet that if the app runs VB/VBA, then Dir is going to work.

.FileSearch can be used to search for different types of files, but can only be run from a VBA host application (actually, I should amend that – you can run it from Office apps, no idea if it’s available say in Visio or Mappoint or whatever).

If you can set a reference to Microsoft Scripting Runtime (which can be available within an Office 97 context, depending on whether certain other components are installed), then you can make use of the File Scripting Object functions, as in this example:

Private Function bCheckTemplateExists(sTemplatePath As String) As Boolean
bCheckTemplateExists = FSO.FileExists(sTemplatePath)
End Function

(assumes FileSystemObject has already been declared and an FSO object created, at module level)

Gary

Reply | Quote

Gary,

Visio is a VBA host. In fact, it was the first third party product to license VBA when VBA was first introduced. MapPoint is off my map, so I can’t speak to it.

You can also use the FileSystemObject with late binding with a reference to the Office Object library rather than the scripting runtime.

Reply | Quote

Hi Charlotte,

I knew Visio is a VBA host. What I was/am unsure about is whether FileSearch is only available via VBA hosts that are also MS Office apps(?).

I didn’t know about using FileSystemObject with late binding, thanks.

Gary

Reply | Quote

Very cool.

Does the File Scripting Object offer an file dialogs?

Reply | Quote

No, it only makes available a number of methods for working with files and folders.

The best coverage I’ve seen of this (25+pp.) can be found O’Reilly’s VB & VBA In A Nutshell.

With regard to your other post, I have to (with a tinge of wistfulness for a less responsible time) agree with you.
This must be why so many programmers are ‘contract’ employees!

Reply | Quote

I knew you pick up on the ambiguity there…

Reply | Quote

And Einstein (sp?) said something like “The consciousness that creates a problem cannot solve it.” I’ll get the quote and post the exact wording.

Reply | Quote

So it follows that we can enhance our problem-solving skills by altering our consciousness? – make mine a double!

Reply | Quote

Such a perspective was widely practiced in the 60’s, 70’s and of course the 80’s. While I had some experience in the realm of expanding one’s consciousness, I can testify that I got smarter only afer learning how to “contract” one’s consciousness.

Reply | Quote

Just remembered this one was posted a few weeks ago:

Public Function FileExists(strFilename As String) As Boolean
On Error Resume Next
Call GetAttr(strFilename)
FileExists = (Err = 0)
Err.Clear
End Function

Reply | Quote

Hi Al

Sorry I missed this one, and I wanted to add the following in regards to your comment listed below:

<<>>

I some times have the need to use the GetShortPathName API to get the 8.3 path without any spaces that say a shell command can understand. It goes like this:

Public Declare Function GetShortPathName Lib “kernel32” _
Alias “GetShortPathNameA” (ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, _
ByVal cchBuffer As Long) As Long

Maybe by using this function to generate the name for the Dir function it will eliminate the problem.

I hope this helps.

Wassim

Reply | Quote

I hereby update my old solution to eliminate a bug: if you passed into the function a filename of the empty string, the function returned true (due to the Dir command interpreting the “” as nothing which means get the next file — in this case the first file in the current folder). Very bad. The FolderExists function had the same problem.

Public Function fFolderExists(strFullName As Variant) As Boolean
If Len(strFullName) 0 Then
fFolderExists = Len(Dir(strFullName, vbDirectory))
End If
End Function

Public Function fFileExists(strFullName As String) As Boolean
If Len(strFullName) 0 Then
fFileExists = Len(Dir(strFullName, vbNormal))
End If
End Function

Thanks to Al for inspiring me to take a closer look!

Reply | Quote