Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

microsoft access 2010 filtered data export

do you know how to export filtered data export to excel?
0
Hiroyuki Tamura
Asked:
Hiroyuki Tamura
  • 2
3 Solutions
 
MASTechnical Department HeadCommented:
Create a query based on your requirement then export using the below code.

DoCmd.OutputTo acOutputQuery, "Query_name", acFormatXLS, outputFileName
2
 
aikimarkCommented:
Are you talking about filtering a table/query in a datasheet view and then exporting that filtered row set to Excel?
1
 
Hiroyuki TamuraAuthor Commented:
>Are you talking about filtering a table/query in a datasheet view

Yes, that is correct.
0
 
aikimarkCommented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now