User to select variable folder for saving files

[nikki_k]

Hi I hope someone can help.

I've searched through other posts but either can't find the same scenario as mine or I'm not looking properly.

I would like my macro to prompt the user to select a folder for files to be either opened from or saved to during the running of my macro.

In fact, I need them to select two folders, one for this month and the other for last month.

I think I need to set the two folders as a variable but being fairly new to vba I'm not quite sure how to do this.

Any help would be greatly appreciated.

Thanks

 

[Andrew Poulsom]

Look here:

http://spreadsheetpage.com/index.php/tip/selecting_a_directory/

If you have Excel 2003 you can use FileDialog with msoFileDialogFolderPicker:

http://www.mrexcel.com/forum/showthread.php?t=62434

 

[Richard Schollar]

Hi Nikki & Welcome to the Board!

If you are using xl2002 or above you can use:

Dim strFolder As String
 
With FileDialog(msoFileDialogFolderPicker)
.InitialFileName = ThisWorkbook.Path
    .AllowMultiSelect = False
    If .Show = -1 Then
        strFolder = .SelectedItems(1)   'selected folder assigned to variable strFolder here
    Else
        MsgBox "No folder selected! Exiting Sub...": Exit Sub
    End If
End With
 
'then use strFolder:
 
ActiveWorkbook.SaveAs strFolder & "\MyFile.xls"    'for example

 

[nikki_k]

Thanks very much guys ... it works perfectly now!

 

[nikki_k]

Oops ... sorry just one more question:

How can I get it to include the strFolder name into the file name when it saves eg:

strFolder is ... C:\Reconciliation\Pension\Jun09

so I want my file name to be 'MyFile Jun09.xls'

Thanks in advance.

 

[Andrew Poulsom]

Didn't Richard show you how?

ActiveWorkbook.SaveAs strFolder & "\MyFile.xls" 'for example

 

[nikki_k]

He did but it just saves as 'MyFile.xls' and ideally I would like it to save as 'MyFile Jun09.xls'

I can't work out how to add the last folder name to be in the file name.

 

[Andrew Poulsom]

Here is an example of how to construct the file name:

Sub Test()
    Const strFolder = "C:\Reconciliation\Pension\Jun09"
    Dim Per As String
    Per = Right(strFolder, Len(strFolder) - InStrRev(strFolder, "\"))
    MsgBox strFolder & "\MyFile " & Per & ".xls"
End Sub

 

[Richard Schollar]

Hi Nikki

Try the following:

ActiveWorkbook.SaveAs strFolder & "\" & "MyFile " & Mid(strFolder,InstrRev(strFolder,"\")+1) & ".xls"

 

[nikki_k]

That is absolutely brilliant

Thank you ever so much for all of your help.