VBAで特殊フォルダのパスを取得する

ExcelVBA

デスクトップの取得方法

Sub Sample3()
    Dim Path As String, WSH As Variant
    Set WSH = CreateObject("WScript.Shell")
    Path = WSH.SpecialFolders("Desktop") & "\"
    ActiveWorkbook.SaveAs Path & "Sample1.xls"
    Set WSH = Nothing
End Sub

マイドキュメント

Sub Sample4()
    Dim Path As String, WSH As Variant, OpenFileName As String
    Set WSH = CreateObject("WScript.Shell")
    Path = WSH.SpecialFolders("MyDocuments") & "\"
    ChDir Path
    OpenFileName = Application.GetOpenFilename("Excelブック,*.xls")
    If OpenFileName <> "False" Then
        Workbooks.Open OpenFileName
    End If
    Set WSH = Nothing
End Sub

ネタ元