Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Create folder (VBA - Excel XP)

    I am sort of an advanced beginner with VBA. I am needing to programmatically test for the existence of a Windows folder, and create it if it does not exist. I couldn't find out how to accomplish this with the books I have. Does anybody have code that would accomplish this?

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    You need to use the Dir and mkDir command.

    Dim strDir As String
    <font color=448800>'Directory to check</font color=448800>
    strDir = "C:test"
    <font color=448800>'Does directory exist</font color=448800>
    If Dir(strDir, vbDirectory) = "" Then
    <font color=448800>'No, so create it</font color=448800>
    MkDir strDir
    End If
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  4. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    I believe the FileSystemObject has a FolderExists method/function.

  5. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    185
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    Bryan--

    Thanks for your piece of code. It worked for creating a directory.

    Mike--

    There is a FolderExists and a FileExists method. For the little function I'm creating, I also need to determine if a certain file name exists. The documented syntax for FileExists is <font color=blue>object .FileExists(filespec)</font color=blue> where Object is always the name of a FileSystemObject. How do I determine the name of the FileSystemObject that I need to use?

  6. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    Randall,,
    You can use the Dir function to see if a file exists as well.

    If Dir("c:FullPathtoFile.ext") = "" then
    'File does not exist
    End If

    If the file does exist, the dir command will return the File Name.
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  7. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    You can use the FileSystemObject from the scripting runtime as follows :

    Dim oFs
    Set oFs = CreateObject("Scripting.FileSystemObject")
    If oFs.FileExists("FilepathFileName") Then
    'do whatever
    End if
    Set oFs = Nothing

    You can also use oFs.FolderExists to check for folders.

    You could also use something like

    Function fFileExists(strFullName As String) As Boolean
    fFileExists = Len(Dir(strFullName, vbNormal))
    End Function

    Andrew C

  8. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    141
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    You'll need to set a reference to Microsoft Scripting Runtime. It's possible that you'll need to install the Windows Scripting Host, but it's usually installed on most systems unless you specifically didn't install it.

    Dim oFSO as Scripting.FileSystemObject

    Set oFSO = New Scripting.FileSystemObject

    If oFSO.FolderExists("folderpath") = True...

    I hope this helps.

    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #8
    4 Star Lounger
    Join Date
    Aug 2002
    Location
    Dallas, Texas, USA
    Posts
    594
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Create folder (VBA - Excel XP)

    Just a warning about the FileSystemObject. It is part of the scripting model, and some network administrators disable it, so if your code is going to be used at locations you don't have control over, it is a better idea to use the code Bryan posted (carbonnb).

Posting Permissions

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