Saving to Desktop....

[RandyD123]

I am using this code to create and save my excel file to a PDF file in the current directory. However I would like it to save to my desktop by default if possible. This file exists on sharepoint and all users should be able to click the "Export As PDF" button and the default save should be the desktop..... The path to the desktop is "C:\Users\%username%\OneDrive - USTSA\Desktop". Thank you in advance.....

Sub Save_PDF_with_Prompt()

Dim xWs As Worksheet
Dim xWb As Workbook
Dim xTime As String
Dim xName As String
Dim xPath As String
Dim xFile As String
Dim yPathFile As String
Dim zFile As Variant

On Error GoTo errHandler

Set xWb = ActiveWorkbook
Set xWs = ActiveSheet
xTime = Format(Date - 1, "mm.dd.yyyy")

xPath = xWb.Path
If xPath = "" Then
  xPath = Application.DefaultFilePath
End If
xPath = xPath & "\"

xName = Replace(xWs.Name, " ", "")
xName = Replace(xName, ".", "_")

xFile = xWs.Name & "_" & xTime & ".pdf"
yPathFile = xPath & xFile

zFile = Application.GetSaveAsFilename _
    (InitialFileName:=yPathFile, _
        FileFilter:="PDF Format (*.pdf), *.pdf", _
        Title:="Save As a PDF File")

If zFile <> "False" Then
    xWs.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=zFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    MsgBox "Successfully Saved As a PDF: " _
      & vbCrLf _
      & zFile
End If

exitHandler:
    Exit Sub
errHandler:
    MsgBox "Failed to Save"
    Resume exitHandler

End Sub

 

[Joe4]

You cannot run Excel VBA code from SharePoint.
The file with VBA code would need to be saved to the Desktop first.

 

[RandyD123]

You cannot run Excel VBA code from SharePoint.
The file with VBA code would need to be saved to the Desktop first.

All of our excel spreadsheets that reside on Sharepoint run VBA just fine........ We are using One Drive which is tied right into Sharepoint.

 

[Joe4]

Are you sure?

Everything I have ever read and what Microsoft has posted said that VBA files cannot be run while on-line in Sharepoint.
It says that Sharepoint can hold VBA files, just cannot run them while online, so the file would need to be downloaded to the Desktop first in order to run the VBA.

Work with VBA macros in Excel for the web - Microsoft Support

Use the Excel Desktop App to create, edit and run VBA macros. You can view and edit macro-enabled workbooks in Excel for the web, but you need to edit the macros themselves in the Excel Desktop App.

support.microsoft.com

If the VBA code is actually working, I question whether you are really running it from Sharepoint on-line.

 

[RandyD123]

Are you sure?

Everything I have ever read and what Microsoft has posted said that VBA files cannot be run while on-line in Sharepoint.
It says that Sharepoint can hold VBA files, just cannot run them while online, so the file would need to be downloaded to the Desktop first in order to run the VBA.

Work with VBA macros in Excel for the web - Microsoft Support

Use the Excel Desktop App to create, edit and run VBA macros. You can view and edit macro-enabled workbooks in Excel for the web, but you need to edit the macros themselves in the Excel Desktop App.

support.microsoft.com

If the VBA code is actually working, I question whether you are really running it from Sharepoint on-line.

Oh, I see what you are saying. I'll be clearer, we all use the desktop office applications. So yes, we technically do download them and open them with desktop versions of Excel, Word, and so on.....

 

[RandyD123]

Ok so now that we all have that straightened out, is there a way that my "save as" dialog box can be set to default to save on the desktop using the VBA?

 

[Joe4]

Ok so now that we all have that straightened out, is there a way that my "save as" dialog box can be set to default to save on the desktop using the VBA?

See if either of these lines returns the correct username:

    MsgBox Application.UserName
    MsgBox Environ("Username")

If so, then you can you dynamically build your default path, i.e.

Dim myPath as String
myPath = "C:\Users\" & Environ("Username") & "\OneDrive - USTSA\Desktop"

 

[Strithken]

Alternative solution that satisfies cases where the Desktop is in an alternate location (credit: stackoverflow):

Dim file_path As String
file_path = CreateObject("WScript.Shell").SpecialFolders("Desktop")

 

[RandyD123]

See if either of these lines returns the correct username:

    MsgBox Application.UserName
    MsgBox Environ("Username")

If so, then you can you dynamically build your default path, i.e.

Dim myPath as String
myPath = "C:\Users\" & Environ("Username") & "\OneDrive - USTSA\Desktop"

Where would I put that in the above code?

 

[MARK858]

Personally I would use the method in post 8 (not sure why the credit to SO, as it is an old method).
You would use it a replacement for your XPath

XPath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & "\"

xName = Replace(xWs.Name, " ", "")
xName = Replace(xName, ".", "_")

xFile = xWs.Name & "_" & xTime & ".pdf"
yPathFile = XPath & xFile