Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    18

    Question Unanswered: Can I use the FileSystemObject to find out if a folder exists?

    I want to find out if a folder exists before I create it.

    I have used this forum to find some handy piece of code for backing up database files, but I wanted to extend the functionality a little by creating a folder for the files with todays date "BU_20100310".

    If I do backups more than once per day, the folder already exists and I get an error. So I thought I'd add an If clause to find out if the folder exists first and in that case jump to another step in the code, My code looks like this (thank you who ever posted it for me to copy):

    'This function will allow you to copy a db that is open,

    'You must set a reference to the 'Microsoft Scripting Runtime' for the CopyFile piece to work!

    Dim fso As FileSystemObject

    Dim sSourcePath As String
    Dim sSourceFile As String
    Dim sBackupPath As String
    Dim sBackupFile As String

    sSourcePath = "X:\Folder1\Folder2\Folder3\"
    sSourceFile = "Current.mdb"
    sBackupPath = "X:\Folder1\Folder2\Archive\BU_" & Format(Date, "yyyymmdd") & "\"
    sBackupFile = "Current_" & Format(Date, "yyyymmdd") & "_" & Format(Time, "hhmmss") & ".mdb"

    Set fso = Folder
    If fso.FolderExists("X:\Folder1\Folder2\Archive\BU_" & Format(Date, "yyyymmdd") & "\") = True Then GoTo CreateFile Else


    Set fso = New FileSystemObject
    fso.CreateFolder sBackupPath
    Set fso = Nothing

    CreateFile:
    Set fso = New FileSystemObject
    fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
    Set fso = Nothing

    So I know I must change something in the red area, probably it's the bold part that's not OK (since I'm really not very good with variables at all) - can you help me please?

  2. #2
    Join Date
    May 2009
    Posts
    258
    Hello Nenne,

    Starting with the red (set fso = Folder), I'd change the logic to the following:
    Code:
    Set fso = New FileSystemObject
    If Not fso.FolderExists(sBackupPath) Then
    	fso.CreateFolder sBackupPath
    End If
    
    fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
    Set fso = Nothing
    You only need to set fso once. I'd recommend staying away from GoTo statements as well. Again, the above logic would replace all of the following:
    Code:
    Set fso = Folder
    If fso.FolderExists("X:\Folder1\Folder2\Archive\BU_" & Format(Date, "yyyymmdd") & "\") = True Then GoTo CreateFile Else
    
    Set fso = New FileSystemObject
    fso.CreateFolder sBackupPath
    Set fso = Nothing
    
    CreateFile:
    Set fso = New FileSystemObject
    fso.CopyFile sSourcePath & sSourceFile, sBackupPath & sBackupFile, True
    Set fso = Nothing
    Regards,

    Ax

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Minor, and not really "better" (although more lightweight) you can do all this with the VBA.FileSystem library:
    Code:
    If vba.FileSystem.Dir(sBackupPath, vbDirectory) = vbNullstring Then....
    Code:
    Call vba.FileSystem.FileCopy(sSourcePath & sSourceFile, sBackupPath & sBackupFile)....
    Code:
    Call vba.FileSystem.MkDir(sBackupPath).....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    one thing to bear in mind is that some AV products treat calls to VBS as potential viral activity. its fine when running on your own system, but if its being deployed to remote users it can cause problems and can lessen trust in your application
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2009
    Posts
    18
    Thank you so much, Ax, pootle flump and healdem!
    I think Ax's suggestion will fit best with the rest of my code this time!

    I'll learn a lot at this forum I can tell!

  6. #6
    Join Date
    Jun 2009
    Posts
    18
    Worked like a charm!!
    Thanks again!
    /Nenne

  7. #7
    Join Date
    May 2009
    Posts
    258
    Great to hear, thanks for posting back!

  8. #8
    Join Date
    Mar 2013
    Posts
    26
    After looking at this post I was wondering if it could be tweaked in some way?

    What I am looking for is a code to check to see it my target file exists or not before clicking on the command button. If the file exists the button is enabled, if not is is disabled.

    Let me know what you think.

    My other post on this, with some ideas, is here;
    http://www.dbforums.com/microsoft-ac...on-solved.html

Posting Permissions

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