Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: VBA - create a folder if not already present

  1. #1
    Join Date
    6th February 2003
    Location
    London, England
    Posts
    614

    VBA - create a folder if not already present

    Goooood mooooorning VBA'ers,

    I'd like my macro to create a folder called "su078 images" on the root C: drive, but only if the folder doesn't already exist there

    (so if the macro is run for the first time on a machine, for example, it will not find the folder and will create it....... subsequent runs will find it and not attempt to create it)

    any ideas ?
    cheers
    Chris
    :-)

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    Re: VBA - create a folder if not already present

    Hi Chris,

    You could use a UDF to check whether it already exists and, if not, create it. However, it's probably easiest to just try to create it anyway and ignore any error message (generated if it already exists). Like this:
    VB:
    On Error Resume Next 
    MkDir "C:\su078 images" 
    On Error Goto 0 
    
    
    HTH
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  3. #3
    Join Date
    6th February 2003
    Location
    London, England
    Posts
    614

    Re: VBA - create a folder if not already present

    thanks Richie, I'll give that a whirl.....

    (I *knew* it would be a big job !!)

    cheers
    Hope this helps,
    Chris

  4. #4
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,682

    Re: VBA - create a folder if not already present

    Chris
    This is a bit fuller and canbe re used over and over - in my opinion i dislike 'on error ....' nothing taken from Richies code this would be my apporach - took about 5 minutes to write and hope stops any bugs

    Had to add XXX to teh dir as have a locked WorkStation at work, sure You can edit ok thou - this is in line with Richies ideas of UDF and is quite powerful

    good luck

    jiuk
    VB:
    Private Const theDrive As String = "C" 
    Private Const theDriveFormat As String = ":\" 
    Private Const theDir As String = "xxx\su078 images" 
    Private Const theDirFormat As String = "\" 
     
    Public targetpath As String 
     
    Sub Way1() 
         'written by Jack in the UK
         'www.exce-it.com
         'Excel Xp+
         
         '-----
         ' jiuk - test
         'MsgBox testDir
         ' OK - false
         
        If testDir = True Then 
             ' jiuk - do your stuff
             ' suggest
             ' - call other procedure
            Exit Sub 
             
        Else 
            MkDir targetpath 
             
        End If 
         
    TheEnd: 
         ' jiuk - some code
        Exit Sub 
         
    End Sub 
     
    Function testDir() As Boolean 
         'written by Jack in the UK
         'www.exce-it.com
         'Excel Xp+
         
        testDir = False 
         
        targetpath = theDrive 
        targetpath = targetpath & theDriveFormat 
        targetpath = targetpath & theDir 
        targetpath = targetpath & theDirFormat 
         
        If Dir(targetpath, vbDirectory) = "" Then testDir = False _ 
    Else testDir = True 
         
    TheEnd: 
         ' jiuk - somecode
        Exit Function 
         
    End Function 
    
    
    Last edited by Jack in the UK; April 6th, 2005 at 22:01.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    Re: VBA - create a folder if not already present

    Hi Jack,

    If you go the UDF route then I'd recommend having the path as an argument for the function. That way, it is more easily re-used when you need it, rather than having to set-up various public variables. Just a thought
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  6. #6
    Join Date
    26th January 2003
    Location
    UK
    Posts
    4,682

    Re: VBA - create a folder if not already present

    Hey Richie

    Yeah true just a hardcode wavy to stop input errors i guess - could use input box and build the same way - again millions of ways to do these things, these days i do try when ever possible to avoid blanket on error code lines unless i can not figure a way around it, and do tend to have lots of small procedures linking up, i guess this is now my style.

    I do use a lot of functions these days to test all sorts and call them in many procedures just keeps sections of code out the way and easy to maintain / edit. As You see this is very much teh way / style i work now and find works ok for me, being very much trial and error and self taught / what i see / read and try on OzGrid i guess i have found this is the way to go, i find things simpler this way and Boolean will solve lots of tests over code procedures that try to do teh same and end up with long loops / with / and if statements, little chunks are nice

    jiuk

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    6th February 2003
    Location
    London, England
    Posts
    614

    Re: VBA - create a folder if not already present

    cheers lads, some good ideas there

    :-)
    Hope this helps,
    Chris

Thread Information

Users Browsing this Thread

There are currently 6 users browsing this thread. (0 members and 6 guests)

Possible Answers

  1. Create Folder If It Doesn't Exist
    By janbang in forum EXCEL HELP
    Replies: 2
    Last Post: July 27th, 2007, 19:58
  2. Code To Check For And Create New Folder
    By Ying&Yang4u in forum EXCEL HELP
    Replies: 3
    Last Post: September 13th, 2006, 00:55
  3. Replies: 6
    Last Post: May 24th, 2006, 00:14

Bookmarks

Posting Permissions

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