Results 1 to 4 of 4
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    new directory, add files (Excel 97+)

    I'm using Excel 2003 but need a solution that works for older versions too. I have code that creates a text file of certain fields on a two worksheets. This file can be read later to pre-populate these same fields. This all works fine except that I just now noticed that it doesn't detect if the file exists before creating it. <img src=/S/doh.gif border=0 alt=doh width=15 height=15> I want to create a directory under the current workbook's path (whatever it may be) which contains these data files. I was using FileSearch but then discovered it's for xl2002+ only and, even worse, it's not detecting a file already exists. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>
    <pre>Public Function FileExists(fDir As String, fName As String) As Boolean
    FileExists = False
    With Application.FileSearch ' for xl2002+ only
    .NewSearch
    .LookIn = fDir
    .Filename = fName
    ' .Execute
    ' If .FoundFiles.Count <> 0 Then FileExists = True
    If .Execute > 0 Then FileExists = True
    End With
    End Function</pre>

    I pass 'fDir' as Thisworkbook.path & "data", and fName is the actual file name such as "customerAA.txt". So I need to test if the file exists in that directory. The above code always returns False.

    Question 1. How do I make this Excel version independent (the lowest common set of code that'll work from xl97-2003)
    Question 2. How can I get it to create the sub-folder the first time only (such as data) without changing the user's current directory.
    Question 3. Why does the above code always return False?

    There are so many File related functions I get confused and it's hard to know what's backward compatible. <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    Thnx,
    Deb

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: new directory, add files (Excel 97+)

    I always got a headache using FileSearch, not least because it seemed painfully slow. In VBA, there is a crusty old technique you can use to check for the existence of a file (and, with slightly more difficulty, the existence of a folder), Dir, which does not rely on the scripting library so it should always be available. (Now, I'll admit only using it in Word, but I'm pretty sure it's available in all VBA hosts.)

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

    Re: new directory, add files (Excel 97+)

    Yep, Dir has been around as long as VBA. It has a few gotchas too, but we still use it. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Charlotte

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: new directory, add files (Excel 97+)

    Ok good stuff. I stumbled on Dir just a while ago and got it all working just fine. As others said, I find the whole file search, find, show, create, etc. dance in VBA to be quite an effort when trying to figure out what works for what rev and what gotchas there are - it's all trial and error but w/o having 4 different versions of Excel to play with, it's not easy.

    Thanks again,
    Deb

Posting Permissions

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