Copy/Filter from Unopened Excel File

seekeagle

New Member
Joined
Mar 5, 2009
Messages
23
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.
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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