Filtering And Then Extracting Filtered Data From Closed Workbook

[Kumar Shanmugam]

I have PAN India data. Without opening the workbook I want to filer a data where state_name= Karnataka and extract the data to a new worksheet. Please find attached the sample file.

Also I have a list of PAN India data monthwise in a folder. It will be great if there is a macro code that will extract data from a closed workbook that matches a criteria where state_name="Karnataka".

 

[Marc L]

Beginner's way : open workbook and just use Excel basics :
a filter or better an advanced filter …

If you know SQL, you could use ADODB, see samples on every forum …
​

 

[Kumar Shanmugam]

PAN India data is very huge. 100 MB. Opening the workbook will not serve my purpose. Hence require macro that will extract filtered data from closed workbook

 

[Kumar Shanmugam]

I don't have SQL installed in my office PC. Hence Excel VBA is the only solution possible.

 

[Nebu]

Hi:

You do not need SQL in your system for using ADODB connection and SQL , this can be done through VBA, attach a sample of both the files here someone may be able to help you out.

Thanks

 

[Kumar Shanmugam]

Hi,

Please find attached both sample file one containing PAN India data and another filtered data.

I have monthwise PAN India data in below directory

D:\Hemangi Data Copy\Alloc coll Data\2016

I wanted to get the filtered data in a new sheet.

 

[Kumar Shanmugam]

Also please find attached the sampled filtered data.

 

[Nebu]

Hi:

Use the following code.

Sub test()
'Add reference for Microsoft Activex Data Objects Library-Microsoft Activex Data Objects 6.1 Library before running the macro
Application.ScreenUpdating = False
Dim Conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
'You need to update the below line with your desired path
cpath$ = ThisWorkbook.Path & "\Get data from closed workbook" & ".xlsx"
rsconn$ = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & cpath & "';" & _
            "Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"";"
Conn.Open rsconn
With Sheet1
        Sheet1.Range("A2:BZ" & Me.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
' You need to update the field names in the below line as per your original file
                    strSQL$ = "SELECT * FROM [Sheet1$]Where [STATE_NAME]= '" & Sheet2.Range("A1") & "'"
'                    On Error Resume Next
                    rs.Open strSQL, Conn, adOpenStatic, adLockOptimistic, adCmdText
                    Sheet1.Range("A2").CopyFromRecordset rs
                    Set rs = Nothing
End With

Set Conn = Nothing
Application.ScreenUpdating = True
End Sub

I am assuming that both the files will be stored in the same path, else you will have to change the value of "cpath" in the code. I have given the value KARNATAKA in the sheet2 cell a1 , you can even hard code the same into the code if you want.

Thanks

 

[Kumar Shanmugam]

How to add reference for microsof activex data object

 

[Kumar Shanmugam]

I don't have Microsoft access. Does it require Microsoft access

 

[Nebu]

Hi:

Go to VBA edititor

Tools-References - select then Microsoft ActiveX data object.

The following link explains what to do
http://excel-macro.tutorialhorizon.com/vba-excel-reference-libraries-in-excel-workbook/

Thanks

 

[Kumar Shanmugam]

Hi I am getting error.

 

[Kumar Shanmugam]

I am new to Macro Also I don't know coding in VBA please help.

 

[Nebu]

Hi:

I can't help you if you cannot help yourself. The link I given clearly specify why you are getting the error. Go through the link and try to figure out yourself.

Thanks