CODEWITHSUNDEEP
vbaexcel
Prateek Vishwas
Prateek Vishwas

Reputation: 203

Getting folder path using msoFileDialogFolderPicker

I have a code which picks file from a SourcePath then renames it and saves in DestPath. The code is working fine with hardcoded folder path for SourcePath (SourcePath = "C:\Invoices\Raw invoices"). however, it's not able to capture and retain the folderpath with msoFileDialogFolderPicker function. The code is unable to find the file in sourcepath and gives error as programmed.

Here is the sample data.

enter image description here

Here is the code I am using.

Sub Rename_invoices()
    Dim SourcePath As String, DestPath As String, Fname As String, NewFName As String
    Dim i As Long
    SourcePath = GetFolder("C:\")
    DestPath = "C:\Dunning Temp\"
    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        If Not IsEmpty(Range("A" & i).Value) Then
            NewFName = Range("B" & i).Value
            'Search for the first file containing the string in column A
            Fname = Dir(SourcePath & "*" & Range("A" & i).Value & "*")
            If Fname <> vbNullString Then
                FileCopy SourcePath & Fname, DestPath & NewFName
            Else
                MsgBox Range("A" & i).Value & " dosen't exist in the folder"
            End If
        End If
    Next i
    ActiveSheet.Close = False
End Sub

Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

Upvotes: 0

Views: 1238

Answers (1)

BigBen
BigBen

Reputation: 49998

The path your GetFolder Function returns will not end with a backslash \. As is, the pathname argument you pass to Dir in Fname = Dir(SourcePath & "*" & Range("A" & i).Value & "*") will be incorrect.

So change SourcePath = GetFolder("C:\") to SourcePath = GetFolder("C:\") & "\", or add a trailing backslash within your GetFolder function.

As @Mistella pointed out, using Debug.Print would easily highlight this issue.

Upvotes: 2

Related Questions