Macro for changing path in macro

[Guest]

Hi,

can a macro be written to change a path set a macro to another location of
your choice? I.E.

my current macro saves to "s:\Invoicing\Sales 05 06" but as we have remote
users, this path changes because they need these files to save locally too. i
was wondering if i included a button, they could start the command, open a
dialog box and select their new location to save. This would then be the set
location on the macro until it is changed. Is this possible?

Thanks,

Nigel

 

[Nigel]

Your best option would be store the path to the files in a worksheet cell
and reference this cell in your macro. To get the user to choose the path
is a bit tricky as there is not an xL dialog to select paths, only get files
and save file locations.

But you can use the following.....(I have used John's code succesfully)

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

 

[Guest]

The following function will do what you want, but I'm not sure which version
of XL you need to get the "FolderPicker". Copy the following "Sub" and
"Function" into a standard module - then run the "Sub":

Sub test()

Dim sFolder As String
sFolder = FolderSelectPrompt
MsgBox sFolder
End Sub

Public Function FolderSelectPrompt(Optional argPath As String) As String
'CALL CAUSES FOLDER SELECTION DIALOG BOX TO APPEAR;
'FUNCTION RETURNS THE FOLDER SELECTED BY THE USER;
'Create a FileDialog object as a File Picker dialog box
Dim dlgFolder As FileDialog
Set dlgFolder = Application.FileDialog(msoFileDialogFolderPicker)
With dlgFolder
.AllowMultiSelect = False
.ButtonName = "RUN!"
.InitialView = msoFileDialogViewDetails
.InitialFileName = argPath
.Title = "RUN PROGRAM!"
'.Show
End With
If dlgFolder.Show = -1 Then
FolderSelectPrompt = dlgFolder.SelectedItems(1) & "\"
Else
End
End If
End Function

HTH