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,

 

[RoryA]

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)
      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
        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

 

[josros60]

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)
      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
        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

Thank you,

I runned it but it give an "run-time error 438" object doesn't support this property or method"
and stopped a this line:

If Not IsError(Application.Match(cell.Value, .Rows(1), 0)) Then

 

[RoryA]

Sorry - .Rows(1) should be .Sheets(1).Rows(1)

 

[josros60]

Thanks again.

But it' instead of matching the headers if not matching the headers is moving them I thought will just skip the one not in the source and leave everything same in my template, (attaching picture of Before and after executing the code)

have GST QST HSTON the source only have HSTON but sometimes will have 3 or two them. I would like to match headers if leave in blanks ones not in the soruce.

thank you

 

[josros60]

Thanks again.

But it' instead of matching the headers if not matching the headers is moving them I thought will just skip the one not in the source and leave everything same in my template, (attaching picture of Before and after executing the code)

have GST QST HSTON the source only have HSTON but sometimes will have 3 or two them. I would like to match headers if leave in blanks ones not in the soruce.

thank you

Any suggestions?

Thank you

 

[josros60]

Please ignore my last post, it worked perfectly.

Thank you very much for all your help, I really appreciated.

Have a wonderful happy holliday!