Solved

microsoft access 2010 filtered data export

Posted on 2016-09-13
4
66 Views
Last Modified: 2016-09-16
do you know how to export filtered data export to excel?
0
Comment
Question by:Hiroyuki Tamura
  • 2
4 Comments
 
LVL 25

Assisted Solution

by:-MAS
-MAS earned 250 total points
ID: 41795799
Create a query based on your requirement then export using the below code.

DoCmd.OutputTo acOutputQuery, "Query_name", acFormatXLS, outputFileName
2
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
ID: 41796057
Are you talking about filtering a table/query in a datasheet view and then exporting that filtered row set to Excel?
1
 

Author Comment

by:Hiroyuki Tamura
ID: 41796136
>Are you talking about filtering a table/query in a datasheet view

Yes, that is correct.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 250 total points
ID: 41796220
Please try this.
Sub Q_28969428()
    Dim strSrc As String
    Dim strFilter As String
    Dim rs As Recordset
    Dim oXL As Object
    Dim oWkb As Object
    Dim rng As Object
    Dim fld As Field
    
    strSrc = Application.Screen.ActiveDatasheet.Recordset.Name
    strFilter = Application.Screen.ActiveDatasheet.Filter
    Set rs = DBEngine(0)(0).OpenRecordset("Select * From " & strSrc & " Where " & strFilter)
    Set oXL = CreateObject("Excel.Application")
    Set oWkb = oXL.Workbooks.Add
    oXL.Visible = True
    Set rng = oWkb.Worksheets("Sheet1").Range("A1")
    For Each fld In rs.Fields   'headers
        rng.Value = fld.Name
        Set rng = rng.Offset(0, 1)
    Next
    oWkb.Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
End Sub

Open in new window

0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question