MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Creating a new folder

Posted by SJT on August 02, 2001 4:46 PM

Is there a way to write a macro that will save a worksheet into a folder that doesn't exist yet - For instance:

Sub CreateFolder()

ActiveWorkbook.SaveAs FileName:= "C:\NewFolderThatDoesn'tExistYet\123.xls"
End Sub

Something to that effect (preferably something that actually works, unlike the example above.)

Posted by brent on August 02, 2001 5:26 PM

I tried it by going to the Tools menu, open Macro, Record new macro - record the steps - stop the recording. It worked.

Posted by sjt on August 02, 2001 5:35 PM

Thanks. I tried that also and the macro didn't record the creation of the folder. It just recorded the directory change. I know there's a CreateFolder syntax but my VBA skills are lacking.

Posted by SJT on August 02, 2001 5:47 PM

Ahh! I figured it out!

CreateObject("Scripting.FileSystemObject").CreateFolder ("C:\windows\desktop\new folder test\")

Posted by Ivan F Moala on August 02, 2001 7:31 PM

Re: Ahh! I figured it out!

Or just use VBA
MkDir "C:\NewFolderThatDoesn'tExistYet\123.xls"