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

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

Code:

---

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

Check the attachment. I have modified the position of the hidden columns.

Code:

---

Sub Export_DATA()

    Worksheets("Backlog-Raw Data").Range("J9:Q355").ClearContents
    With Application.FileDialog(msoFileDialogFilePicker)
        .AllowMultiSelect = False
        .ButtonName = "Open Raw Data"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.csv"
        .Filters.Add "CSV File", "*.csv"
        .Title = "File Save As"
        .Show
        If .SelectedItems.Count Then
            strFileSelected = .SelectedItems(1)
        Else
            MsgBox "Cancelled by user!"
            Exit Sub 'Ideally, should exit from the bottom of the sub-routine.
        End If
    End With
    fncFileSelected = strFileSelected
    'Need a code here that will lookup the contect of the file being opened and will copy and paste them the Book1.xlsm
    With Workbooks.Open(Filename:=fncFileSelected, ReadOnly:=True)
        .Sheets(1).Cells(1).End(xlToRight).Offset(, 2).Resize(, 8).Value = ThisWorkbook.Worksheets("Backlog-Raw Data").Range("J8:Q8").Value
        .Sheets(1).Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Sheets(1).Cells(1).End(xlToRight).Offset(, 2).CurrentRegion, Unique:=False
        With .Sheets(1).Cells(1).End(xlToRight).Offset(, 2).CurrentRegion
            ThisWorkbook.Worksheets("Backlog-Raw Data").Range("J8:Q8").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        Workbooks(.Name).Close 0
    End With
   
End Sub

---

Thanks so much for the code Admin Excel Fox. Its working perfectly as needed. Thank you :) :) :)