VBA生成PDF后打开目标文件夹失败，误打开Documents文件夹

核心问题 #

你的代码在调用Shell打开文件夹时，路径中包含空格（"Class Actions"）却没有用双引号包裹，导致explorer.exe无法正确识别完整路径，最终默认打开了Documents文件夹。

修复方案 #

修改打开文件夹的Shell命令，给路径添加双引号，确保整个路径被当作单个参数传递：

Call Shell("explorer.exe """ & sfolderpath & """", vbNormalFocus)

完整优化后的代码 #

同时建议加上Option Explicit强制变量声明，避免潜在的未声明变量问题（比如原代码中的FindFolder未声明）：

Option Explicit

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
    Dim oWSHShell As Object
    Dim FindFolder As Object ' 声明变量
    
    Set ws = ActiveSheet
    ClientName = ws.Range("I10").Value
    cusip = ws.Range("I11").Value
         
    ActiveSheet.PageSetup.PrintArea = "F5:N28"

 '*************定位用户桌面文件夹**************  
    Set oWSHShell = CreateObject("WScript.Shell")
    GetDesktop = oWSHShell.SpecialFolders("Desktop")
    Set oWSHShell = Nothing
    
 '**********************创建目标文件夹*******************
    Set FindFolder = CreateObject("Scripting.FileSystemObject")
    sfolderpath = GetDesktop & "\Class Actions\"
    If Not FindFolder.FolderExists(sfolderpath) Then
       MkDir sfolderpath
    End If
    
    '***********格式化导出文件名*************
    fName = sfolderpath & ClientName & " - Eligibility Report - " & cusip
        
    ' 优化重复文件名处理逻辑（避免覆盖已有文件）
    If Len(Dir(fName & ".pdf")) > 0 Then
        Dim i As Integer
        i = 1
        Do While Len(Dir(fName & " - " & i & ".pdf")) > 0
            i = i + 1
        Loop
        fName = fName & " - " & i
    End If
    fName = fName & ".pdf"
    
    '***********设置PDF导出参数***********
    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
        
    '************打开目标文件夹***********
    Call Shell("explorer.exe """ & sfolderpath & """", vbNormalFocus)
        
End Function

额外说明 #

1. 引号转义：VBA中用两个双引号""表示字符串中的一个双引号，这样explorer.exe就能正确识别带空格的完整路径。
2. 变量声明：添加Option Explicit可以避免因拼写错误导致的未声明变量问题，减少运行时错误概率。
3. 重复文件名处理：补充了版本号递增逻辑，避免直接覆盖已存在的同名PDF文件。

内容的提问来源于stack exchange，提问作者Wallenbees