Macro to Create New Folder & Save File

Posted by Richard P on October 23, 2001 11:21 PM

Can anyone recommend some Macro code to carry out the function of performing a "File, Save As" and then click the Create New Folder button. I would like the New Folder name to be the same as the contents of cell A1 and the Filename to be the contents of cell A2.

Thanks !

Posted by Paul on October 25, 2001 4:46 AM

Sub Macro1()
Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next ' If directory exist goto next line
strDirname = Range("A1").Value ' New directory name

strFilename = Range("A2").Value 'New file name
strDefpath = "C:\My Documents\" 'Default path name
If IsEmpty(strDirname) Then Exit Sub
If IsEmpty(strFilename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "\" & strFilename 'create total string

ActiveWorkbook.SaveAs FileName:=strPathname, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub