PDA

View Full Version : Filter by named range in closed file



ioncila
12-11-2017, 09:45 AM
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(xlCellTypeConsta nts).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
12-11-2017, 03:21 PM
any suggestions?
forgot to mention that both files are in same folder

snb
12-12-2017, 03:06 AM
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
12-12-2017, 03:10 AM
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
12-12-2017, 04:13 AM
Thank you for ignoring my post.

ioncila
12-12-2017, 06:03 AM
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
12-12-2017, 08:15 AM
Remove 'option explicit'

ioncila
12-12-2017, 08:35 AM
Sorry sng

another error:

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