VBA - Save As PDF

[Spaztic]

I'm working on a macro that would save my Excel workbook as a PDF.
I'm saving as pdf with some options (ignore print area, save entire workbook)...

This file will be used by many different users so the line "C:\Users\myname\OneDrive - Company\Desktop\folder\trial.pdf" probably won't work as it has my name entered and also the file name (currently 'trial') is not always the same. I'd like the saved pdf file name to be the same as the Excel file and stored in the same location as the Excel file.

Any help would be appreciated!

Sub SavePDF()

    ActiveWorkbook.Save
    ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Users\myname\OneDrive - Company\Desktop\folder\trial.pdf", _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=True, OpenAfterPublish:=False

    End Sub

 

[Spaztic]

Thank you for your help Micron! When using without sharepoint...the code works as intended.

 

[Micron]

AFAIK, sharepoint paths use / character, file paths use \ so you would have to swap where I used \ in the code. However, I'm fairly certain Dir will not work with URL's and I don't think FSO will either. You would have to find out what the UNC path is to the sp server and use that. I think you could use Windows File Explorer to get the unc path or ask your IT person to help with that. If you use the unc path, do not swap the slash character, otherwise you'll have a forward slash in the path and then it will likely fail.

EDIT - you might be able to discover the unc path to the server if you use file explorer, navigate to a file, right click on it and choose properties. That may show you the path to the file... just use the part that gets you to the folder where the Excel wb is located.

 

[Spaztic]

This worked for sharepoint...

Dim sPath As String, sName As String

sPath = ThisWorkbook.Path
sPath = ThisWorkbook.GetLocalPath(ThisWorkbook.Path)

sName = ThisWorkbook.Name
sName = Left(sName, InStrRev(sName, ".") - 1)

' Check to see if file exists
    Dim strFileExists As String
    Dim strFileName As String
    
    Dim tsfile As String
    
    strFileName = sName & ".pdf"   ' old   sNameBuildFilePath
    tsfile = sPath & "\" & strFileName

    strFileExists = Dir(tsfile)
    
    Dim AnswerYes As String
        
    If strFileExists <> "" Then
        MsgBox "Error: " & strFileName & " already exists"
       
        AnswerYes = MsgBox("Do you Wish to overwrite?", vbQuestion + vbYesNo, "File Exists")
         
        If AnswerYes = vbNo Then
            GoTo End Sub
        End If
        
    End If
  
ThisWorkbook.Save
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, fileName:=sPath & "\" & sName & ".pdf", _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=True, OpenAfterPublish:=False

 

[jolivanes]

Instead of this (code in Post #1)

C:\Users\myname

try

Environ$("userprofile") & "\"