Filter by named range in closed file

[ioncila]

Hi
I'm trying to write code to copy all rows from a closed file (Closed.xlsx) that matches a named range ("TECH") in column 8 ("H").
Closed.xlsx is a file that is daily exported from a server with variable size each day (>100.000 rows).
Named range "TECH" belongs to destination book (Open.xlsm) and is also dynamic (contains some technicians names).

Sub CopyFromClosedBook()
 Dim wsD As Worksheet, wsO As Worksheet
  Application.ScreenUpdating = False
  Set wsD = ThisWorkbook.Sheets("Report")
  Set wsO = Workbooks.Open(".\Closed.xlsx").Sheets("DataList")
  
   With wsO
    .AutoFilterMode = False
    .Range("A1:AB" & .Cells(Rows.Count, 1).End(3).Row).AutoFilter 8, "TECH"
    If (.Range("A1:A" & .Range("A1").End(4).Row).SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeConstants).Count) > 1 Then
     .Range("A2:AB" & .Cells(Rows.Count, 1).End(3).Row).Copy
     wsD.[A2].Insert Shift:=xlDown
     .AutoFilterMode = False
    Else: MsgBox "File Not Found"
    End If
   End With
  ActiveWorkbook.Close SaveChanges:=False
  wsD.Range("A2:AB" & Cells(Rows.Count, 1).End(3).Row).RemoveDuplicates Columns:=1, Header:=xlNo
  ThisWorkbook.Save
  Application.ScreenUpdating = True
End Sub

I searched in google and in other sources somthing that suits my need, bur no success.
Can you help me please?

Tnak you so much in advance

[ioncila]

any suggestions?
forgot to mention that both files are in same folder

[snb]

Sub M_snb()
  with getobject(thisworkbook.path & "\Closed.xlsx")
     sn=.range("tech")
     .close 0
  end with 

  ThisWorkbook.Sheets("Report").cells(1).resize(ubound(sn),ubound(sn,2))=sn     
End Sub

[ioncila]

Hi
So, after searching again, I found here in VBA EXPRESS this code thatis more near to what I want to do. I simply don't know how to set ADDRESS with namedrange ("TECH") to get data filtered by TECH names in col 8.

Option Explicit 


 'you can extract data from a closed file by using an
 'XLM macro. Credit for this technique goes to John
 'Walkenback > http://j-walk.com/ss/excel/tips/tip82.htm


Sub GetDataDemo() 


  Dim FilePath$, Row&, Column&, Address$ 


   'change constants & FilePath below to suit
   '***************************************
  Const FileName$ = "Closed.xlsx" 
  Const SheetName$ = "DataList" 
  Const NumRows& = 100000 'Need to configure range from A1 to last row non blank
  Const NumColumns& = 28 'Or setup only cols 1,3,8,24,27 
  FilePath = ActiveWorkbook.Path & "\" 
   '***************************************


  DoEvents 
  Application.ScreenUpdating = False 
  If Dir(FilePath & FileName) = Empty Then 
      MsgBox "The file " & FileName & " was not found", , "File Doesn't Exist" 
      Exit Sub 
  End If 
  For Row = 1 To NumRows 
      For Column = 1 To NumColumns 
          Address = Cells(Row, Column).Address 
          Cells(Row, Column) = GetData(FilePath, FileName, SheetName, Address) 
          Columns.AutoFit 
      Next Column 
  Next Row 
  ActiveWindow.DisplayZeros = False 
End Sub 




Private Function GetData(Path, File, Sheet, Address) 
  Dim Data$ 
  'Data = "'" & Path & "[" & File & "]" & Sheet & "'!" & _ 
  Range(Address).Range("A1").Address(, , xlR1C1)
  Data = "'" & Path & File & "'!" & Address
  GetData = ExecuteExcel4Macro(Data) 
End Function

I work in Excel 2013 and 2016
Thank you all in advance

[snb]

Thank you for ignoring my post.

[ioncila]

Hi snb
Very very sorry for that. I didn't pay attention after browse update.

Your code returns error "Compile error: Variable not defined" in

sn=.range("tech")
Can you help?

Thank you

[snb]

Remove 'option explicit'

[ioncila]

Sorry sng

another error:

Run-time error '438'
Object doesn't support this property or method