Specify a save folder

[cdbrown]

Hi everyone,

Is it possible to get a macro to prompt the user to select a folder or create a new folder to save all the output wb's the other macros create. Perhaps save that location in a cell to be referenced as needed. I would also like the macro to then create a subfolder within the selected folder which will be the save folder for the intermediate dump wb's.

Cheers
-cdbrown

[OBP]

You can certainly do all three.
You can use an "Input" message box to ask the user to enter a folder path, not realy recommended due to typographical errors.
You can store the folder locations in Excel cells and refer to them in VBA.
You can store them in "Public" string variables for use by VBA procedures.
Finally you can create a folder using either the "Object.CreateFolder" or the the older MkDir method.

[cdbrown]

Thanks OBP.

So to select the save folder could I use something like this and it would record the location on List sheet, cell 10,3.


Set ff = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select a folder to save the Uploads.", 0, "c:\\")
If Not ff Is Nothing Then
GetFolder = ff.Items.Item.Path
Else
GetFolder = vbNullString
End If
MyFolder = GetFolder
If MyFolder = vbNullString Then
MsgBox "Please select a folder to save the Uploads.", vbCritical
Exit Sub
End If
Sheets("List").Cells(10, 3).Value = MyFolder


What code would I need to create the subfolder - name is based on the value in cell 9,3 - within the folder set in cell 10,3.

[ALe]

have a look at this kb entry
http://vbaexpress.com/kb/getarticle.php?kb_id=276

[OBP]

MkDir MyFolder & "/ExcelFilename"
where ExcelFilename is the name of your workbook