Solved

Can you create a VBA macro that checks if folder exists and if not creates one

Posted on 2016-10-13
9
101 Views
Last Modified: 2016-10-19
I am trying to create a macro that saves down a file to a folder with the corresponding month and year.

i.e. S:2016\10-Oct 2016

If the folder doesn't exist I then want the macro to create one.

Is this even possible?
0
Comment
Question by:Benjamin Hopper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41841979
Not sure what you meant by
saves down a file to a folder with the corresponding month and year
But the following code will create the folder defined in the strFolderPath variable if it doesnt already exist.
Option Explicit

Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long
Sub Demo()
    Dim strFolderPath As String
    strFolderPath = "C:\2016\10-Oct 2016\"
    If Dir(strFolderPath, vbDirectory) <> vbNullString Then
        MakeSureDirectoryPathExists strFolderPath
    End If
End Sub

Open in new window

0
 
LVL 35

Expert Comment

by:Kimputer
ID: 41841997
I think that code works, but only if you change <> to =
0
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41841999
oops ;-)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 54

Expert Comment

by:Bill Prew
ID: 41842038
If you know the parent folder will exist, then you can create the new date based child pretty simply like below.  In addition, if wanted a little more control over things then you could use the FileSystemObject as well, happy to share an example of that as well...

strDir = "S:\2016\10-OCT-2016\" 
If Dir(strDir, vbDirectory) = "" Then MkDir strDir

Open in new window

~bp
0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41842169
Maybe you want the name to be dynamic
Option Explicit

Public Sub TestFolderExists()
 Dim sFullPath As String
 
 sFullPath = "S:" & Year(Date) & Application.PathSeparator & Format(Date, "dd-mmm yyyy")
 If Not Dir(sFullPath, vbDirectory) = vbNullString Then
        MsgBox sFullPath & " exists!"
    Else: MkDir (sFullPath)
    End If

End Sub

Open in new window

0
 
LVL 35

Expert Comment

by:Kimputer
ID: 41842208
I still like the first solution better (because creating the subfolder is not depended on parent folder's existence, and therefore more fool proof). The asker might have a hard time figuring out this problem right after new year's eve.
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41844031
Hi.

Please, be patient if my English language level is not so good.

Try this:

Dim fso As Scripting.FileSystemObject
    Dim ArrPath As Variant
    Dim n As Integer
    
    Dim sFullPath As String
    
    Set fso = New Scripting.FileSystemObject
    
    sFullPath = CurrentProject.Path & "\" & Year(Date) & "\" & Format(Date, "dd-mmm yyyy")

    ArrPath = Split(sFullPath, "\")
    
    sFullPath = ArrPath(0)
    
    For n = 1 To UBound(ArrPath)
        sFullPath = sFullPath & "\" & ArrPath(n)
        If Not fso.FolderExists(sFullPath) Then
            fso.CreateFolder (sFullPath)
        End If
    Next n
    Set fso = Nothing  

Open in new window


You only require a reference to Microsoft Scripting Host (included) and doesn't need any external library.

This routine hasn't problems if the full path not exists, because checks/create the full path.


Also, if you are using a network drive, you can check this and use the real network resource (ServerName\Root Path) instead of mapped names by using the Scripting. FileSystemObject .


Best regards.

Antonio.
Barcelona (Spain).
0
 
LVL 19

Accepted Solution

by:
Roy_Cox earned 500 total points
ID: 41844705
Please disregard my previous answer, I posted the wrong code.

This is the code that I meant to post. It will create a Name in the Excel workbook that is created which is used to check if the workbook is a copy. This way you do not need to remove all the code in the workbook

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim bCopy As Boolean
    Const SaveAsPath = "C:\Users\pike\Desktop\backup\"
    Cancel = True
    On Error Resume Next
    bCopy = Evaluate(ThisWorkbook.Names("Copy").RefersTo)

    If bCopy Then
        ThisWorkbook.Close True    ''??? True will ensure the workbook is saved before closing
    Else
        Select Case MsgBox("Would you like to create a backup copy?", vbYesNo _
            Or vbQuestion Or vbDefaultButton1, "Create Copy")
        Case vbYes
            ActiveWorkbook.SaveAs SaveAsPath & ThisWorkbook.Name & Format(Date, _
                "ddmmyyyy") & ".xlsm", FileFormat:=52
            ''/// set the flag is the Name
            ThisWorkbook.Names.Add Name:="Copy", RefersTo:=1, Visible:=True
        Case vbNo
        End Select
        End If
        On Error GoTo 0
    End Sub

Open in new window

0
 
LVL 19

Expert Comment

by:Roy_Cox
ID: 41850572
Pleased to help.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question