Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Save Location

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Save Location

    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?
    Charlotte

  4. #3
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    Charlotte,

    Alt startup location is blank. And, yes, a File>Save points to System32 also.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  5. #4
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  6. #5
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    Running Excel2K but should be similar:

    What entry do you have under
    HKEY_CURRENT_USERSoftwareMicrosoftOffice<font color=blue>your_version</font color=blue>ExcelOptions
    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?

  7. #6
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  8. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    <P ID="edit"><FONT SIZE=-1>Edited by charlotte on 01/01/10 03:35.</FONT></P>A maybe:

    http://support.microsoft.com/support/kb/ar...s/Q161/5/12.asp

  9. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Save Location

    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 <font class="bigger"><font class="bigger"><font color=448800><font face="Comic Sans MS">WART</font face=comic></font color=448800></font size=big></font size=big> on your nose by authority as moderator!
    Charlotte

  10. #9
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    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."[img]/w3timages/icons/thinks.gif[/img]

    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."
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  11. #10
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

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

  12. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Save Location

    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.
    Charlotte

  13. #12
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    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.
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  14. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Save Location

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

  15. #14
    Gold Lounger
    Join Date
    Dec 2000
    Location
    Hollywood (sorta), California, USA
    Posts
    2,759
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Save Location

    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.0ExcelMi crosoft 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?
    Kevin <IMG SRC=http://www.wopr.com/w3tuserpics/Kevin_sig.gif alt="Keep the change, ya filthy animal...">
    <img src=/w3timages/blackline.gif width=33% height=2><img src=/w3timages/redline.gif width=33% height=2><img src=/w3timages/blackline.gif width=33% height=2>

  16. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Excel Save Location

    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.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •