View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
GS[_6_] GS[_6_] is offline
external usenet poster
 
Posts: 1,182
Default Help needed in hanging the default file location for Excel and Word

On 1/15/2019 3:14 PM, Kirk Bubul wrote:
On Tue, 15 Jan 2019 14:25:56 -0500, GS wrote:



Excel stores the full path to the files on its Recent list; - since you've
moved those file to a different path, Excel can't find them in the
'stored'
path.

You need to Browse the 1st time you open any of those files so Excel
stores the
new path for each one!


Thanks, Garry, I was afraid of that.


I really "know nuthink!" of any of the Office products other than the bare
basics, but can't you clear the Recent Files list and then won't it use
default locations when open the File menu?


That would be the same as browsing to the new location of the files via the
'Open' menuitem. Kirk suggests he already did change the default path, but is
clicking a file (shortcut link) to open on the Recent list.

Clearing that list is probably a good idea because it will remove the natural
tendancy to select previously worked on files while rebuilding the list with
the new path where those files were moved. However, I don't see a feature in
the UI to clear the Recent list so will have to be done manually!

The path to the list is:
C:\Users\<username\AppData\Roaming\Microsoft\Offi ce\Recent

Optionally, the files in this folder could be modified to change their 'Target'
via the Properties dialog, Shortcut tab -OR- via code. The latter would require
looping all the files in the folder and setting/modifying their target property
via VB[A]'s Replace() function.

IE:
Const sOldPath$ = "C:\office documents"
Const sNewPath$ = "C:\Users\Kirk\Documents"

Dim sTarget$
'code to loop the folder 1 file at a time

'code to get current Target property
sTarget = Replace(sTarget, sOldPath, sNewPath)
'code to set new Target Property

'code to get the next file

Note that no error is raised if sOldPath isn't found, so only those with the
old path will be updated.

Unfortunately, Application.RecentFiles.Item(#).Path is READ ONLY and so some
other approach is required to edit paths. Perhaps the new DsoFile.dll is one
way to go.

You can use VBA, however, to read the existing path, modify it as above, then
..Add a new Item so each file gets replaced with its new path instead of having
to browse for every one manually...

Sub ResetRecentPaths()
Const sOldPath$ = "C:\office documents"
Const sNewPath$ = "C:\Users\Kirk\Documents"

Dim sTarget$, v
For Each v In Application.RecentFiles
sTarget = Replace(v.Path, sOldPath, sNewPath)
Application.RecentFiles.Add sTarget
Next 'v
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion