VBA Macro To Open A File And Extract Data From Specific Columns

[jeremiah_j2k]

Hello All,

I'm trying to make my workbook more flexible and trying not to depend on a fixed file path that's why i'm trying to use a combination of msoFileDialogFilePicker and lookup function but having a hard time making the code work.

Can we use msoFileDialogFilePicker and lookup function in one macro to open a particular file and copy and paste the data to a workbook?

The file that I need to open is the "output.csv" (csv file location varies also the number of columns but column labels are fixed) the code should copy and paste the data to "Book1.xlsm".

Book1.xlsm columns
Date Opened
SR # Owner
Severity
Status
Substatus
Client Id
User Updated On


output.csv columns that need to be copied
Date Opened
SR # Owner
Severity
Status
Substatus
Client Id
User Updated On

Sub Export_DATA()
On Error GoTo noselect


Dim Export As FileDialog
Set Export = Application.FileDialog(msoFileDialogFilePicker)
  Export.AllowMultiSelect = False
  Export.ButtonName = "Open Raw Data"
  Export.Filters.Clear
  Export.Filters.Add "Excel Files", "*.csv"
  Export.Filters.Add "CSV File", "*.csv"
  Export.Title = "File Save As"
  If Export.Show = True Then
    fncFileSelected = fd.SelectedItems(1)
  End If
    
'Need a code here that will lookup the contect of the file being opened and will copy and paste them the Book1.xlsm
    
    
  Range("U2") = fncFileSelected 'U2 cell will capture the path of the selected file
  Workbooks.Open (fncFileSelected)

noselect:
Exit Sub

  
End Sub

Thank you in advanced for your help.. any assistance will be very much appreciated