Specifying working folder

[Fred Smith]

Our head office, in their wisdom, moved our network drive from F: to H:.

Now I can't make any changes to Tools>Options..., because anytime I click on
a tab, it fails with "Excel cannot find F:/Fred Smith".

How do I tell Excel the working folder is now in H:/Fred Smith?

Using XL2003, Windows XP.

Thanks,
Fred.

 

[Gord Dibben]

Don't know if this will work or not but you could give it a try.

Close Excel first and On the Windows Taskbar

1) Start>Run "excel.exe /unregserver"(no quotes)>OK.
2) Start>Run "excel.exe /regserver"(no quotes)>OK.

See the space between exe and /regserver

You might have to designate a full path to excel.exe.

In that case Start>Run "H:\yourpath\excel.exe" /regserver(quotes
required)>OK.


Gord Dibben MS Excel MVP

 

[Dave Peterson]

If Gord's suggestion doesn't work...

Try opening excel.
Hit alt-f11 to get to the VBE (where macros live)
hit ctrl-g to see the immediate window
type this:
Application.DefaultFilePath = "h:\fred smith\"
and hit enter

Then back to excel to test.

Just a word of warning. If you've been assigned a laptop and you don't always
connect to the network, then excel will realize that this can't be your default
file path and change it what it likes. (Same thing if you use any pc that
doesn't always connect to the network, actually.)

I used to keep a workbook in my XLStart folder that contained this macro:

Option Explicit
Sub auto_open()

'Dim FSO As Scripting.FileSystemObject
Dim FSO As Object

Const myDefaultFolder As String = "X:\my folder\myfolder2l"

'Set FSO = New Scripting.FileSystemObject
Set FSO = CreateObject("scripting.filesystemobject")

With Application
If FSO.FolderExists(folderspec:=myDefaultFolder) Then
.DefaultFilePath = myDefaultFolder
ChDrive myDefaultFolder
ChDir myDefaultFolder
Else
MsgBox "DefaultFilePath not reset, still: " & .DefaultFilePath
End If
End With

ThisWorkbook.Close savechanges:=False

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[smartin]

Our head office, in their wisdom, moved our network drive from F: to H:.

Now I can't make any changes to Tools>Options..., because anytime I
click on a tab, it fails with "Excel cannot find F:/Fred Smith".

How do I tell Excel the working folder is now in H:/Fred Smith?

Using XL2003, Windows XP.

Thanks,
Fred.

Can you not open Tools | Options | General and change the default file
location? (Works for me in the same environment even when the default
path doesn't exist.)

Failing that, you could try this temporary workaround, which I cannot
fully test.

Create this empty folder structure using Windows Explorer:

C:\F\Fred Smith

Enter the command prompt (Start | Run | cmd | OK) and type

subst F: C:\F

Return to Excel and try to change the default file location. If this
works, it is because Windows/Excel now believes F:\Fred Smith exists,
even though it is located at C:\F\Fred Smith.

Note the subst drive assignment will be forgotten the next time you log off.

 

[Fred Smith]

No. I can get Tools>Options... dialogue box displayed, but as soon as I
click on the General tab, I get the error message. I'm not given any chance
to make any changes to the options.

Any other ideas?

Regards,
Fred.

 

[Fred Smith]

Gord,

I can't access this newsgroup from work, so I tried to do your suggestion
from memory. However, I just did the /regserver part, without any success. I
will try both tomorrow.

Do I need to do this from Start>Run, or can I do it from a shortcut as well?

Regards,
Fred.

 

[Fred Smith]

Dave,

I tried the VBA command, but no success. I even confirmed with
?Application.DefaultFilePath that the new path was set. However, I still get
the same error message.

Any other ideas?

Regards,
Fred.

 

[Dave Peterson]

There aren't too many places where a drive and folder are specified in
tools|Options.

One was the defaultfilepath. But another is the alternate startup folder.

Try:
?application.AltStartupPath
if it points to that troublesome folder, then change it to ""
application.AltStartupPath = ""

Another drive path to try is:
?application.AutoRecover.Path

You could start excel in safe mode:
close excel
windows start button|Run
excel /safe

And search for more drive/folders. But you'll have to open excel normally to
change them.

 

[Dave Peterson]

Did you try the other suggestion?

Can you map a network drive to the F: drive (just temporarily)--or maybe use a
thumb drive?

Then create a folder by that name so that you can access Tools|Options?

 

[Fred Smith]

Bingo. Application.AutoRecover.Path was the problem. I changed it to an
existing folder, and solved my problem.

Thanks,
Fred.

 

[Dave Peterson]

I think mine hasn't been changed from the default location.

Just in case you want to use that...

C:\Documents and Settings\(username)\Application Data\Microsoft\Excel\

(under winxp home and xl2003)

Bingo. Application.AutoRecover.Path was the problem. I changed it to an
existing folder, and solved my problem.

Thanks,
Fred.

 

[Fred Smith]

Thanks, I think I'll use this. I had specified a network folder, but I like
the idea of using the local disk better.

Thanks for your help,
Fred.

 

[Dave Peterson]

Using a network drive can be a problem--if the network is down or if you just
can't connect to the network.

For the recovery file location, I'd use a local drive. It would be faster, too.

Thanks, I think I'll use this. I had specified a network folder, but I like
the idea of using the local disk better.

Thanks for your help,
Fred.