Ask user for folder path if path does not exist-CodePudding

I want to check (using VBA) if a folder path exists.

saveLocation = "G:\documents\"

If the folder exists then I want to save to this location.

I am currently saving as a PDF using the following code.

 saveLocation = saveLocation & "myfile.pdf"

However the issue I have, is if the folder location doesnt exist I want to prompt or ask the user to select a folder.

How would I go about doing this?

Thanks in advance.

CodePudding user response：

Please, use one of the next two ways:

Sub testFolderIfExists()
   Dim saveLocation As String
   
   saveLocation = "G:\documents\"

   If Dir(saveLocation, vbDirectory) <> "" Then Debug.Print saveLocation & " folder exists"
   'or
   Dim fso  As Object: Set fso = CreateObject("Scripting.FileSystemObject")
   
   If fso.FolderExists(saveLocation) Then Debug.Print saveLocation & " folder exists"
End Sub

CodePudding user response：

Is this what you are trying?

Option Explicit

Sub Sample()
    Dim Ret
    Dim saveLocation As String
    
    saveLocation = "G:\documents\"
    
    If Dir(saveLocation, vbDirectory) <> "" Then
        '~~> Folder Exists
        '~~> Save at saveLocation
    Else
        '~~> Folder doesn't exist
        '~~> Prompt user to select folder
        Ret = BrowseForFolder
        
        If Ret <> False Then
            saveLocation = Ret
            
            If Right(saveLocation, 1) <> "\" Then saveLocation = saveLocation & "\"
            
            '~~> save at saveLocation
        End If
    End If
End Sub

Function BrowseForFolder(Optional OpenAt As Variant) As Variant
    Dim ShellApp As Object
     
    Set ShellApp = CreateObject("Shell.Application"). _
    BrowseForFolder(0, "Please choose a folder", 0, OpenAt)
     
    On Error Resume Next
    BrowseForFolder = ShellApp.self.Path
    On Error GoTo 0
     
    Set ShellApp = Nothing
     
    Select Case Mid(BrowseForFolder, 2, 1)
    Case Is = ":"
        If Left(BrowseForFolder, 1) = ":" Then GoTo Whoa
    Case Is = "\"
        If Not Left(BrowseForFolder, 1) = "\" Then GoTo Whoa
    Case Else
        GoTo Whoa
    End Select
     
    Exit Function
Whoa:
    BrowseForFolder = False
End Function

Screenshot (If folder doesn't exist)