hiranimnavin
New Member
- Joined
- Jul 15, 2021
- Messages
- 4
- Office Version
- 365
- Platform
- Windows
I have multiple excel file in a folder. Want to select each file individually to to vlookup in a mail file. Below code opens the file but it only chose the first file in the folder even though i have selected the last file.
Please suggest so i can get the data from the file that i choose in the file dialog.
VBA Code:
Sub CopySheetWB()
Dim fd As Office.FileDialog
Dim strFile As String
Dim wkbDest As Workbook
Dim wkbSource As Workbook
Set wkbDest = ActiveWorkbook
Dim r, s, i As Long
Dim x As Range
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx?", 1
.Title = "Choose an Excel file"
.AllowMultiSelect = False
If .Show = True Then
strFile = .SelectedItems(1)
End If
strextension = Dir("*.xls*")
Set wkbSource = Workbooks.Open(strFile)
Set x = wkbSource.Worksheets(1).Range("A1:B65536")
Set closedbook = wkbSource
wkbDest.Activate
r = Range("A65536").End(xlUp).Row
MsgBox "Last Row in Active sheet is " & r
s = Range("IV1").End(xlToLeft).Column
MsgBox "Last Column in Active sheet is " & s
Cells(1, s + 1) = strextension
Cells(2, s + 1).Select
ActiveCell.Formula2R1C1 = "=VLOOKUP(RC2,'" & strextension & "'!R1C1:R65534C2,2,FALSE)"
Selection.Copy
Range(Cells(3, s + 1), Cells(r, s + 1)).Select
ActiveSheet.Paste
closedbook.Close savechanges:=False
End With
End Sub
Please suggest so i can get the data from the file that i choose in the file dialog.
Last edited by a moderator: