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

[Excel Fox]

Check the attachment. I have modified the position of the hidden columns.
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).Of fset(, 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

[jeremiah_j2k]

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