Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for changing path in macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for changing path in macro
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 -- Cheers Nigel "Nigel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro for changing path in macro
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 "Nigel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
File Path - Macro | Excel Worksheet Functions | |||
How to remove path from macro reference | Excel Discussion (Misc queries) | |||
Setting a defaut path in a Macro | Excel Programming | |||
File Path Macro | Excel Programming | |||
How to lock the path to a macro | Excel Programming |