CODEWITHSUNDEEP
excelvba
Bart g
Bart g

Reputation: 585

Selecting one or multiple files at a time in VBA

I have been working on making the macro to choose one file at a time or two files at the same time. If the user chooses one file first, then the second file(found in different directory) chosen place it below the first file name, but do not replace the first file name. If the user chooses two files then place them one after the other one. This is what I have so far and it does not work when the user chooses only one file because it does not show the name of the file in the respective cell. It works when I choose two files but it does not work when I choose only one file. Any help is appreciated.

strInitialDirectory = CurDir
Set fd = Application.FileDialog(msoFileDialogFilePicker)

With fd
    .Title = "Choose File"
    .InitialFileName = CurDir & "\"
    .AllowMultiSelect = True
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"

    If .Show = False Then
        ChDir (strInitialDirectory)
        Exit Sub
    End If
        If fd.SelectedItems.Count > 1 Then
            strPathAndOne = .SelectedItems(1)
            strPathAndTwo = .SelectedItems(2)
        Else
                strPathAndOne = .SelectedItems(1)
                mvUp = True
        End If
 End With

 strFileOne = Right(strPathAndOne, Len(strPathAndOne) - InStrRev(strPathAndOne, "\"))
 strFileTwo = Right(strPathAndTwo, Len(strPathAndTwo) - InStrRev(strPathAndTwo, "\"))

    If mvUp = True Then
        ThisWorkbook.Sheets("Sheet1").Range("E4") = strFileTwo
    Else
        ThisWorkbook.Sheets("Sheet1").Range("E3") = strFileOne
        ThisWorkbook.Sheets("Sheet1").Range("E4") = strFileTwo
    End If


 End Sub

Upvotes: 0

Views: 518

Answers (2)

Bart g
Bart g

Reputation: 585

Change IsEmpty("E3") for IsEmpty(Range("E3")), in the following way.

 If fd.SelectedItems.Count = 2 Then
            strPathAndOne = .SelectedItems(1)
            strPathAndTwo = .SelectedItems(2)

    ElseIf fd.SelectedItems.Count = 1 Then
            If IsEmpty(Range("E3")) = True Then
                strPathAndOne = .SelectedItems(1)
            ElseIf IsEmpty(Range("E4")) = True Then
                strPathAndTwo = .SelectedItems(1)
            End If
    End If
End With

 strFileOne = Right(strPathAndOne, Len(strPathAndOne) - InStrRev(strPathAndOne, "\"))
 strFileTwo = Right(strPathAndTwo, Len(strPathAndTwo) - InStrRev(strPathAndTwo, "\"))

    If IsEmpty(Range("E3")) = True And IsEmpty(Range("E4")) = True Then
        ThisWorkbook.Sheets("Sheet1").Range("E3") = strFileOne
        ThisWorkbook.Sheets("Sheet1").Range("E4") = strFileTwo
    ElseIf IsEmpty(Range("E3")) = True Then
        ThisWorkbook.Sheets("Sheet1").Range("E3") = strFileOne
    ElseIf IsEmpty(Range("E4")) = True Then
        ThisWorkbook.Sheets("Sheet1").Range("E4") = strFileTwo
    End If

Upvotes: 0

dcromley
dcromley

Reputation: 1410

Use F8 to single-cycle to see what's going on. Or do debug.print's. If mvUp is True then I think you want strFileOne instead of strFileTwo.

Upvotes: 1

Related Questions