Call Shell Explorer

35 Views Asked by At

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
1

There are 1 best solutions below

0
Wallenbees On

This answer from @FaneDuru worked perfectly: shell "explorer.exe """ & GetDesktop & "\""", vbNormalFocus