VBA FileDialog

hiranimnavin

New Member
Joined
Jul 15, 2021
Messages
4
Office Version
  1. 365
Platform
  1. 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.

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:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
My test shows that when I select a file, it was the selected file, not the first one in the folder. Not sure why you have that problem
 
Upvote 0
Maybe because of strextension = Dir("*.xls*")?

Does the correct file open? Is the problem here: Cells(1, s + 1) = strextension?

Also, you forgot to declare strextension.
 
Upvote 0
As Gokhan Aycan has said, the problem is with the strextension line.
Try it like this
VBA Code:
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


Set wkbSource = Workbooks.Open(strFile)
strextension = wkbSource.Name

Set x = wkbSource.Worksheets(1).Range("A1:B65536")
Set closedbook = wkbSource
wkbDest.Activate
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top