Macro to select a filepath / strfolder by browsing using button option

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Macro to select a filepath / strfolder by browsing using button option

Post by Jude0887 »

Hello Hans. Hope you are doing good.

Can you help me out with a macro which could browse and select a filepath/strfolder using a button option. Can you please provide me the macro I will create the button and assign the Macro to it.

Thanks in advance

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro to select a filepath / strfolder by browsing using button option

Post by HansV »

You could use the following function:

Code: Select all

Function BrowseFolder(Optional InitialPath As String) As String
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .InitialFileName = InitialPath
        If .Show Then
            BrowseFolder = .SelectedItems(1)
        End If
    End With
End Function
Create a macro in which you use the function to assign a variable:

Code: Select all

Sub Test()
    Dim strPath As String
    strPath = BrowseFolder
    ...
End Sub
Best wishes,
Hans

Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Macro to select a filepath / strfolder by browsing using button option

Post by Jude0887 »

Hello
This macro throws a error message stating Compile error “Expected end sub”. Kindly advice

Code: Select all

Sub Test()
    Dim strPath As String
    strPath = BrowseFolder
    Function BrowseFolder(Optional InitialPath As String) As String
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .InitialFileName = InitialPath
        If .Show Then
            BrowseFolder = .SelectedItems(1)
        End If
    End With
End Function
End Sub

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro to select a filepath / strfolder by browsing using button option

Post by HansV »

Please look at my previous reply. You have copied the code incorrectly.
Best wishes,
Hans

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Macro to select a filepath / strfolder by browsing using button option

Post by StuartR »

You simply need to move the function out of the sub...

Code: Select all

Sub Test()
    Dim strPath As String
    strPath = BrowseFolder
End Sub

Function BrowseFolder(Optional InitialPath As String) As String
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .InitialFileName = InitialPath
        If .Show Then
            BrowseFolder = .SelectedItems(1)
        End If
    End With
End Function
StuartR


Jude0887
Lounger
Posts: 29
Joined: 03 Sep 2020, 05:10

Re: Macro to select a filepath / strfolder by browsing using button option

Post by Jude0887 »

Oh okay thank u. I am using a another macro where one of the requirement is to browse for the folder. I will use the above macro for the browse . How can the path selected by browsing gets selected in the below macro (Const strfolder =“C:\PDF\”. How can i link it. Kindly advice

This macro is for renaming pdf files

Code: Select all

Sub RenamePDF()
  Const strFolder = "C:\PDF\"
    Dim r As Long
    Dim m As Long
    Dim strOld As String
    Dim strNew As String
    Dim n As Long
    On Error Resume Next
    ' Get the last non-empty row
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Assuming that the names start in row 2
    For r = 2 To m
        strOld = Range("A" & r).Value
        strNew = Range("B" & r).Value
        n = Application.CountIf(Range("B1:B" & r - 1), strNew)
        If n > 0 Then
            strNew = strNew & "(" & n & ")"
        End If
        Name strFolder & strOld As strFolder & strNew & ".pdf"
    Next r
End Sub

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Macro to select a filepath / strfolder by browsing using button option

Post by HansV »

Like this:

Code: Select all

Sub RenamePDF()
    Dim strFolder As String
    Dim r As Long
    Dim m As Long
    Dim strOld As String
    Dim strNew As String
    Dim n As Long
    With Application.FileDialog(4) ' msoFileDialogFolderPicker
        .Title = "Select a folder"
        If .Show Then
            strFolder = .SelectedItems(1) & "\"
        Else
            Beep
            Exit Sub
        End If
    End With
    On Error Resume Next
    ' Get the last non-empty row
    m = Range("A" & Rows.Count).End(xlUp).Row
    ' Assuming that the names start in row 2
    For r = 2 To m
        strOld = Range("A" & r).Value
        strNew = Range("B" & r).Value
        n = Application.CountIf(Range("B1:B" & r - 1), strNew)
        If n > 0 Then
            strNew = strNew & "(" & n & ")"
        End If
        Name strFolder & strOld As strFolder & strNew & ".pdf"
    Next r
End Sub
Best wishes,
Hans