I am using a workbook to design a PDF that will save on any user's desktop. Despite the code creating the pdf in the right location, at the end I try to open the folder, and instead of opening the created folder it opens the users "Documents" folder. Can you spot what i am doing wrong here?
Function Create_PDF()
Dim GetDesktop As String
Dim ws As Worksheet
Dim ClientName As String, dt As String, FullName As String, fName As String, sep As String, cusip As String
Dim myrange As String
Dim MyTableRange As String
Dim sfolderpath As String
Set ws = ActiveSheet
ClientName = ws.Range("I10").Value
cusip = ws.Range("I11").Value
ActiveSheet.PageSetup.PrintArea = "F5:N28"
'*************Locates Desktop Folder for the User**************
Dim oWSHShell As Object
Set oWSHShell = CreateObject("WScript.Shell")
GetDesktop = oWSHShell.SpecialFolders("Desktop")
Set oWSHShell = Nothing
'**********************Creates the end folder*******************
Set FindFolder = CreateObject("Scripting.FileSystemObject")
sfolderpath = GetDesktop & "\Class Actions\"
If FindFolder.FolderExists(sfolderpath) Then
Else
MkDir sfolderpath
End If
'*********** Formatting the File Name for the Export *************
fName = sfolderpath & ClientName & " - Eligibility Report - " & cusip
If Len(Dir(fName & ".pdf")) > 0 Then sep = " - "
fName = fName & ".pdf"
'***********Setting Export to PDF Parameters***********
Application.PrintCommunication = False
With Sheets("Starting Page").PageSetup
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True
ws.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fName, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
'************Opens Destination Folder to Retrieve File***********
Call Shell("explorer.exe " & sfolderpath, vbNormalFocus)
End Function
This answer from @FaneDuru worked perfectly:
shell "explorer.exe """ & GetDesktop & "\""", vbNormalFocus