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
Macro to select a filepath / strfolder by browsing using button option
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
-
- 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
You could use the following function:
Create a macro in which you use the function to assign a variable:
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
Code: Select all
Sub Test()
Dim strPath As String
strPath = BrowseFolder
...
End Sub
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Macro to select a filepath / strfolder by browsing using button option
Hello
This macro throws a error message stating Compile error “Expected end sub”. Kindly advice
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
-
- 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
Please look at my previous reply. You have copied the code incorrectly.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12577
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Macro to select a filepath / strfolder by browsing using button option
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
-
- Lounger
- Posts: 29
- Joined: 03 Sep 2020, 05:10
Re: Macro to select a filepath / strfolder by browsing using button option
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
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
-
- 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
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
Hans