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

josros60

Well-known Member
Joined
Jun 27, 2010
Messages
786
Office Version
  1. 365
Hi,

Have the code below

VBA Code:
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,
 

Attachments

  • TARGET SHEET.png
    TARGET SHEET.png
    9.4 KB · Views: 14
  • SOURCE CSV FILE.png
    SOURCE CSV FILE.png
    53.8 KB · Views: 13
VBA Code:
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
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
VBA Code:
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:

VBA Code:
If Not IsError(Application.Match(cell.Value, .Rows(1), 0)) Then
 
Upvote 0
Sorry - .Rows(1) should be .Sheets(1).Rows(1)
 
Upvote 0
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
 

Attachments

  • Before running the code.png
    Before running the code.png
    8.3 KB · Views: 5
  • After running the code.png
    After running the code.png
    37.4 KB · Views: 5
Upvote 0
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
 
Upvote 0
Please ignore my last post, it worked perfectly.

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

Have a wonderful happy holliday!
 
Upvote 0

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top