Copy/Filter from Unopened Excel File

[seekeagle]

I am using VBA to copy a table from an unopened Excel file to the active Excel file. The macro was developed in the "record macro" mode and works fine. However, the table is 40,000 lines long, and I would like to apply the advanced filter without opening the file. Your help with this task would be appreciated.

 

[Datsmart]

Can you post the code you are using to copy the table?

This will help us to better suggest a solution and not have to re-write what you already have.
Is the Advanced Filter to be applied to the same "unopened" workbook?
Please include the Path, filename, worksheet name, and range of the table.

 

[Peter_SSs]

I am using VBA to copy a table from an unopened Excel file to the active Excel file. The macro was developed in the "record macro" mode and works fine. However, the table is 40,000 lines long, and I would like to apply the advanced filter without opening the file. Your help with this task would be appreciated.

Welcome to the MrExcel board!

Since you already have code successfully importing the table, what about just doing the Advanced Filter in the active, open workbook instead of in the source workbook?

 

[seekeagle]

Can you post the code you are using to copy the table?

This will help us to better suggest a solution and not have to re-write what you already have.
Is the Advanced Filter to be applied to the same "unopened" workbook?
Please include the Path, filename, worksheet name, and range of the table.

John,

Thanks for your interest. My code is listed below. The reason that I want to filter is that the file is huge, and I do not want to copy all of it each time I need data.

Sub gah_read_query()
Dim filename As String
Dim filepath As String
Dim commandarray As String

filename = "ICAS_Raw_Data"
filepath = "U:\03_F-22\10_CAM_Tool\05_Super_Tool\ICAS_Raw_Data.xls"
commandarray = "'Raw Data, DO 28,31,36,49,60 $'" ' This is the sheet name

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=U:\03_F-22\10_CAM_Tool\05_Super_Tool\ICAS_Raw_Data.xls;" _
, _
"Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:" _
, _
"Database Password="""";Jet OLEDB:Engine Type=35;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:" _
, _
"Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;" _
, _
"Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array(commandarray)
.Name = filename
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = filepath
.Refresh BackgroundQuery:=False
End With

Sheets("Sheet2").QueryTables(1).Delete
End Sub

 

[xenou]

What's the filter criteria you want to use on this table? Note: be specific about column/field names, datatypes (if ambiguous - is there text, numbers, or dates in the column), and the exact criteria to apply.

Alex