Run-time error '1004': The extract range has a missing or invalid field name.

[josros60]

Hi,

Have the code below

Sub Export_DCL_DATA()

    Worksheets("EXPENSE").Range("A2:H14").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
        Application.ScreenUpdating = False
        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 content of the file being opened and will copy and paste them the EXPENSE_TEMPLATE.xlsm
    With Workbooks.Open(Filename:=fncFileSelected, ReadOnly:=True)
        .Sheets(1).Cells(1).End(xlToRight).Offset(, 2).Resize(, 4).Value = ThisWorkbook.Worksheets("EXPENSE").Range("A1:H1").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("EXPENSE").Range("A1:H1").Resize(.Rows.Count, .Columns.Count).Value = .Value
        End With
        Workbooks(.Name).Close 0
    End With
    Application.ScreenUpdating = True
End Sub

and giving me this error

Run-time error '1004':
The extract range has a missing or invalid field name.


Attaching files:

Thank you,

 

[josros60]

Hi,

Any help please.

Thank you,

 

[RoryA]

I don't really see why you are assigning the value of A1:H1 when the range you are assigning to is only 4 columns wide. The error would suggest that one of the headers doesn't match your data source.

 

[josros60]

Because the A1:H1 are the headers to match from CSV file.

Thank you,

 

[RoryA]

But the range you assign them to is only 4 cells.

 

[josros60]

I changed it 7, 8 still same error checked also the headers looks same.



Thank you,

 

[josros60]

I found the problem, the headers ok but if the source for example don't have GST or QST BUT HSTON won't work how to modify if to skip those columns in my template when the source don't have it this time sometimes will have it.

This time has HSTON but not GST or QST and those columns are first then I have column Purchase Order Number and Invoice Description will give me error need to skip GST/QST that are not in the source this time.

Thank you,

Thank you,

 

[josros60]

Hi,
Any help in the code:

how to modify if to skip those columns in my template when the source don't have it this time sometimes will have it.

Thanks

 

[RoryA]

Something like:

    With Workbooks.Open(Filename:=fncFileSelected, ReadOnly:=True)
      dim outCell as range
      set outcell =  .Sheets(1).Cells(1).End(xlToRight).Offset(, 2)
      dim cell as range
      for each cell in ThisWorkbook.Worksheets("EXPENSE").Range("A1:H1")
         if not iserror(application.match(cell.value, .rows(1), 0)) then
            outcell.value = cell.value
            set outcell = outcell.offset(, 1)
         end if
      next cell
      .Sheets(1).Cells(1).CurrentRegion.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Sheets(1).Cells(1).End(xlToRight).Offset(, 2).CurrentRegion, Unique:=False

 

[josros60]

I am not that good in VBA would you mind writing the complete code I tried but couldn't get it.

Thank you