+ Reply to Thread
Results 1 to 15 of 15

Changing path for Application.GetSaveAsFileName

  1. #1
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Changing path for Application.GetSaveAsFileName

    The code below forces a new, unsaved instance in Excel (opened from a sharepoint menu on our intranet) to save as a macro-enabled workbook, but I cannot get it to open the "Save As" window to the correct path.

    The issue is when they save, it defaults to their local My Documents folder, but we want this to save to whatever sharepoint (web folder) they last navigated to when they opened the new instance. That is exactly the path it opens to if I remove this code, so it's in there somewhere.

    (Without this code, although it wants to save in the correct web folder, it tries to save as an xlsx by default, which is a nuisance to the users...who are also unwilling to just change the type to xlsm in the save as dialog.)

    Please Login or Register  to view this content.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Changing path for Application.GetSaveAsFileName

    try

    Please Login or Register  to view this content.
    Then GetSaveAsFilename
    Last edited by shg; 10-04-2011 at 12:09 AM.
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Changing path for Application.GetSaveAsFileName

    thanks, but i guess the real problem is, how do i get the "some share" path?

    the users navigate to their own folder on sharepoint, hit a "New" button to open an unsaved instance of this file, and when they save it, the Save As dialog is already pointing to whatever folder they were in when they hit New (only it wants to save as a simple xlsx). With this code, I've fixed the file type issue and it wants to save as xlsm, but it doesn't know that path anymore and defaults to their local My Documents folder.

    As a test, I opened a new Excel file, then closed Internet Explorer completely, then saved the file...it still remembered the path on Sharepoint. So that path variable is being stored somewhere other than IE, but I don't know how to retrieve it.

  4. #4
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Changing path for Application.GetSaveAsFileName

    bump for no response

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Changing path for Application.GetSaveAsFileName

    If you use VBA to prompt the user to navigate to the template (using GetOpenFileName) then you'll have the path for a later save.

  6. #6
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Changing path for Application.GetSaveAsFileName

    Quote Originally Posted by shg View Post
    If you use VBA to prompt the user to navigate to the template (using GetOpenFileName) then you'll have the path for a later save.
    Sorry, but having multiple dialogs (one to pick the folder, another to save) won't be an acceptable answer. This was never a problem with the Excel 2003 templates, but with the new file types, we're running into new messages, complications, and extra steps that the upper management (i.e. low patience) users aren't happy about. My task is to force the file to save where they expect, in the xlsm format, without any extra hassle.

    Again, that path is "known" by Excel automatically when they open the template, but I can't see how to call that "known" path variable. If I try to save with my BeforeSave code in place, it wants to save to the desktop. However, if I cancel the Save As dialog, then comment out the vba code and try to save again, it opens at the sharepoint path. If I cancel again, then uncomment the code and try to save again, it opens at the sharepoint folder WITH my code working...go figure.

    So in short, that path is stored in some variable that Excel uses by default for a Save As path, but not with my code (unless I try first without the code, cancel, and try again with the code). If I could simply find that variable, I could just pass it to the file name parameter of the GetSaveAsFileName function and everything's perfect.

    Or perhaps a completely different method that gets the job done?

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Changing path for Application.GetSaveAsFileName

    Are they creating a new workbook from a template on SharePoint, or opening a read-only workbook on SharePoint, or ...?

  8. #8
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Changing path for Application.GetSaveAsFileName

    Quote Originally Posted by shg View Post
    Are they creating a new workbook from a template on SharePoint, or opening a read-only workbook on SharePoint, or ...?
    You could say it's a template, but they're opening an xlsm file, not an xltm. I copy the xlsm file into a hidden "Forms" folder on Sharepoint, and they access it via a menu by selecting it as an option under the "New" menu group.

    I don't have access to see the Sharepoint code that's in that New menu item, but I don't believe it's relevant. The command simply opens the workbook in a new, unsaved local instance of Excel, and even if I close IE/Sharepoint down while that Excel instance is open, it still remembers the Sharepoint location the user was last in when you try to save.

  9. #9
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Changing path for Application.GetSaveAsFileName

    If they are opening an existing file, then ThatWorkbook.Path points to home, does it not?

  10. #10
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Changing path for Application.GetSaveAsFileName

    Quote Originally Posted by shg View Post
    If they are opening an existing file, then ThatWorkbook.Path points to home, does it not?
    Nope. Before it's saved, workbook.path and workbook.fullpath are null (or empty strings)

  11. #11
    Forum Expert Kenneth Hobson's Avatar
    Join Date
    02-05-2007
    Location
    Tecumseh, OK
    MS-Off Ver
    Office 365, Win10Home
    Posts
    2,573

    Re: Changing path for Application.GetSaveAsFileName

    Does CurDir help?

  12. #12
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Changing path for Application.GetSaveAsFileName

    That's why I asked if they are opening a workbook or doing New from a template. If the former, the path is there, even if the workbook is read-only.

    Maybe that's different for a Sharepoint path; I can't test that.

  13. #13
    Registered User
    Join Date
    10-25-2010
    Location
    philadelphia, pa, usa
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Changing path for Application.GetSaveAsFileName

    Quote Originally Posted by Kenneth Hobson View Post
    Does CurDir help?
    thanks for the suggestion, but that just returns the path to the local profile's "Desktop" folder.

  14. #14
    Registered User
    Join Date
    03-26-2015
    Location
    Cracow, Poland
    MS-Off Ver
    2010
    Posts
    1

    Re: Changing path for Application.GetSaveAsFileName

    Hi phi11yguy19! have you figured out how to solve this issue? I am facing same problem :/

  15. #15
    Registered User
    Join Date
    03-04-2014
    Location
    Seattle, WA
    MS-Off Ver
    Excel 2013
    Posts
    3

    Exclamation Re: Changing path for Application.GetSaveAsFileName

    Replying to this thread because it matches exactly what I am experiencing as well. Null value if I try to "get path" when opening XLSM file from SharePoint content type and want to force users to Save As (*.XLSM). If I use the GetSaveAsFileName method then it defaults to local directory. If I don't use the code, it defaults to current SharePoint path but Type is XLSX!

    Please somebody out there, is there a solution? Is there a way to get the SharePoint current path using VBA?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1