Solved

microsoft access 2010 filtered data export

Posted on 2016-09-13
4
63 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

856 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