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

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Dim varFileName As Variant
  If SaveAsUI = True Then
    Cancel = True
    Application.EnableEvents = False
    varFileName = Application.GetSaveAsFilename("somefilename.xlsm", " Excel Macro Enabled Workbook (*.xlsm), *.xlsm,", 2)
    If varFileName = vbFalse Then
      Cancel = True
    Else
      Me.SaveAs varFileName, 52
    End If
    Application.EnableEvents = True
  End If
End Sub

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.

bump for no response

Originally Posted by shg

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?

Originally Posted by shg

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.

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

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?