Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function gblnFileExists

    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

  2. #2
    JustCallMeAl
    Guest

    Re: Public Function gblnFileExists

    Sorry, no I don't. I almost do no work in Excel.

  3. #3
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Public Function gblnFileExists

    Al,

    Thanks for posting this. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    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

  4. #4
    JustCallMeAl
    Guest

    Re: Public Function gblnFileExists

    Excellent point.

    We *can* have situations where .foundfiles.count could be a four-digit number! Especially as I intend to have this as a "library" function which can be called in any situation.

    Thanks

  5. #5
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function gblnFileExists

    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?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #6
    JustCallMeAl
    Guest

    Re: Public Function gblnFileExists

    This why I submitted the code.

    I did try that single line in the immediate window. Worked great. Then I started dissecting it, and our Network blew up! <img src=/S/cauldron.gif border=0 alt=cauldron width=20 height=20>

    Related to the code? I doubt it. Just an interesting coincidence.

    When we get back up, I will be playing with it more. Thanks.

  7. #7
    JustCallMeAl
    Guest

    Re: Public Function gblnFileExists

    As way of explanation....

    The code I was working from, old WordBasic code, used the WordBasic equivalent to VBA's Application.FileSearch. I therefore took VBA's Application.FileSearch and worked from there. That is when I found the problem that seem like there was some lurking DOS 8.3 gremlins. Finally after much massaging, I came up with what I posted.

    Now, your question about Office supporting Dir(), at least Word97 SR2 does. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    I just thought of a programming law:

    <hr>When programming, do not work in a vacuum (i.e., by yourself).<hr>

    Thanks, Kevin.

  8. #8
    JustCallMeAl
    Guest

    Public Function gblnFileExists

    [Edited because of Gary Frieder's suggestion]

    [Not Edited, but much thanks to Kevin's contribution. See his <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=vb&Number=38271&page=0& view=expanded&sb=5&vc=1>post</A>. How much better is one line v. 20 or so.]

    Here is a little something I whipped up. Submitting it for fine-tuning or optimizing, or just for your consideration. I believe there is enough internal documentation to explain why I chose to do what I did.

    <pre>Public Function gblnFileExists( _
    ByVal strFilePath As String, _
    ByVal strFileName As String) As Boolean
    '************************************************* ****
    'Purpose: Determines whether a file exists given the
    ' full path (including ending "") and the
    ' file name.
    '************************************************* ****

    Dim strProposedCapDotName As String
    Dim intCounter As Integer

    gblnFileExists = False

    With Application.FileSearch
    .FileName = strFileName
    .LookIn = strFilePath
    .Execute

    '-----------------------
    'Word 97, SR2, VBA has a wierd habit of showing that the
    ' wrong file exists under certain conditions. If a file
    ' named banana789.doc exists and you are looking to see
    ' if banana78.doc exists, without some sort of validation,
    ' as below, VBA will declare that banana78.doc exists.
    ' The only thing we have come up with is that this is a
    ' remnant from the old DOS 8.3 file naming convention.

    If .FoundFiles.Count > 0 Then
    strProposedCapDotName = strFilePath + strFileName
    For intCounter = 1 To .FoundFiles.Count
    If StrComp(strProposedCapDotName, _
    .FoundFiles(intCounter), _
    vbTextCompare _
    ) = 0 Then
    gblnFileExists = True
    Exit For
    End If
    Next intCounter

    End If
    End With

    End Function</pre>


  9. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function gblnFileExists

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  10. #10
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Public Function gblnFileExists

    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

  11. #11
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Public Function gblnFileExists

    So it follows that we can enhance our problem-solving skills by altering our consciousness? - make mine a double! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Public Function gblnFileExists

    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.
    Charlotte

  13. #13
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: Public Function gblnFileExists

    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. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Gary

  14. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function gblnFileExists

    Very cool.

    Does the File Scripting Object offer an file dialogs?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  15. #15
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Public Function gblnFileExists

    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. <img src=/S/read.gif border=0 alt=read width=19 height=24>
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •