- HubPages»
- Technology»
- Computers & Software»
- Computer Science & Programming»
- Programming Languages
VBA: Macro to Create Folder if it Doesn't Exist
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