Excel Save Location

Topic

New Reply

Ok, I’m posting thematically. (Eileen, when can we embed music to go with our thematic posts?) Until then imagine, if you will, reading this post to the unique sound of bill gates shouting Tower of Power tunes with the local day care providing BUVs — at lunchtime. Bump City. Yeah.

In Excel 97 the Tools>Options>General>Default File Location is set to the exact location required for saving the current spreadsheet(xls). When I click Save to save this newly created sheet, the Save Dialog opens at C:WinntSystem32. Why is that? And more importantly, how can I fix this? Is this really So Very Hard? And don’t say You’ve Got to Funkifize. Anyway, What is Hip? Time for a vacation?

Oh, I’m using Application.GetSaveAsFilename to save this sheet by trapping the SaveAs dialog.

Reply | Quote

Replies

Do you have anything in the alternate startup path? I remember having problems with that in Excel 97. If you open a new workbook in Excel and use File–>Save, does it default to System32, or is it only when you use the GetSaveAsFilename method?

Reply | Quote

Charlotte,

Alt startup location is blank. And, yes, a File>Save points to System32 also.

Reply | Quote

Edited by charlotte on 01/01/10 03:35.

A maybe:

http://support.microsoft.com/support/kb/ar…s/Q161/5/12.asp%5B/url%5D

Reply | Quote

CRI,

As they say in Battleship: miss. But thanks for trying. That Q article deals with the value returned from the dialog. My problem is the folder displayed when the dialog is opened.

Reply | Quote

Running Excel2K but should be similar:

What entry do you have under
HKEY_CURRENT_USERSoftwareMicrosoftOfficeyour_versionExcelOptions
as the Default Path?

Just as a BTW, what happens if you change the default path to something ‘foolproof’, such as C: – does it offer you that location?

Reply | Quote

Leif, et. al,

That reg key has the correct path. I think the solution to the problem has been found in the What’s This? help note for Default Folder Location in the General Tab in Excel.

It says, and I quote, “You must restart Microsoft Excel to change the default working folder location to a network server.”

Ahhhh! So, here’s what I’m going to try: OLE an Excel app, set the default folder, close the Excel app the create another Excel app and proceed with the automation.

Reply | Quote

Kevin,

You left out that little detail about the network server and the fact that you’re trying to set the path from code. You are hereby granted a large WART on your nose by authority as moderator!

Reply | Quote

Charlotte, darling, what does this mean (quote from original post):

“Oh, I’m using Application.GetSaveAsFilename to save this sheet by trapping the SaveAs dialog.”

If that isn’t a reference to code, then show me where to put that in the UI. And did I not post this in the VB/VBA forum? I want that wart removed immediately (even though my physiognomy took on quite a distinguished renaissance look and made me look older than 8).

“Miss, I’m eight years old. You don’t really think I believe in Santa Claus, do you? I don’t think so.”

Reply | Quote

Kevin, Sweetie, the GetSaveAsFilename method works nicely on a local drive. You didn’t mention networks until the penultimate post. And using the results of the trapped SaveAs dialog doesn’t mean you’re trying to set the file location value, only that you’re trying to save to that location. However, I will consent to reducing the size of the wart.

Reply | Quote

Charlotte,

Your are quite right, no mention of networks. For this and all other infractions, I humbly apologize.

As to my solution, what do you all think of that? Is there a better way? Opening and closing an instance of Excel just to set the default folder location seems a bit sophomoric — even though it works nicely.

Reply | Quote

If the reg key has the correct path (we’re talking HKEY_CURRENT_USERSoftwareMicrosoftOffice9.0ExcelOptions here, right?), I don’t understand why that would not be effective when you opened Excel, even as an automation object.

Reply | Quote

I am not using the reg mods for this; however, that’s not a bad idea. The Default Folder Path (Excel 97) in the registry is:

HKEY_CURRENT_USERSoftwareMicrosoftOffice8.0ExcelMicrosoft ExcelDefault

I am using an application object xlapp:
Set xlapp = CreateObject(“Excel.Application”)
xlApp.DefaultFilePath

After the trouble I’ve had trying to change the reg key for the Windows “My Documents” folder, I’m not too confident that the change will take effect without a stop/start — but I will test this approach and thank you for suggesting it.

BTW, the problem with changing the “My Documents” folder via reg key is it will not update until the user logs off (or Shutsdown and restarts Windows). I’ve tried the only API lead I could get (SHChangeNotify), but cannot get it to work. Know anything about this?

Reply | Quote

Yes, I keep forgetting how different the reg keys and folders are for Office 2000 (try to figure out where to put a custom spreadsheet template so it will show up in the New dialog–it took me an hour!)

I gave up on My Documents and just learned to live with it. The whole “My Whatever” syndrome is overly cute, but it isn’t really any more annoying–and at least is more descriptive–than C:OPG, which is where the MOD 2000 expects to put the Office Programmer’s Guide samples.

Reply | Quote

Pasting the code in question would have helped, don’t you think ?

Reply | Quote