ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Science & Programming»
  • Programming Languages

VBA: Macro to Create Folder if it Doesn't Exist

Updated on August 30, 2012

The Problem

When attempting to save to a file path in visual basic. The file path will not automatically be created if it doesn't exist.

Consider the following example:

Sub test()
ActiveWorkbook.SaveAs ("C:\Test\Test.xls")
End Sub

We want to save the active workbook in the folder C:\Test, if this folder doesn't exist we will receive the following error:

The Solution

Check if path exists (If path exists its length will be returned, otherwise zero will be)

If Len(Dir(Path, vbDirectory)) = 0 Then

If not create directory

MkDir ("C:\Test")

The End Result

Sub test()

Dim Path As String
Path = "C:\Test"

If Len(Dir(Path, vbDirectory)) = 0 Then
    MkDir (Path)
End If

ActiveWorkbook.SaveAs (Path & "\Test.xls")

End Sub

An Important Note

The MkDir Function will only work if the folder proceeding the last exists

For instance if you wanted to save to the path C:\Macro\Test\Test.xls

You would have to first test if the path C:\Macro exists and if not create if before proceeding the next folder.

Sub test()

Dim Path As String
Path = "C:\Macro"

If Len(Dir(Path, vbDirectory)) = 0 Then
    MkDir (Path)
End If

If Len(Dir(Path & "/Test", vbDirectory)) = 0 Then
    MkDir (Path & "/Test")
End If

ActiveWorkbook.SaveAs (Path & "\Test\Test.xls")

End Sub

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.