Export Filtered Form Data To Excel

[Jeff Garrison]

Here's one that's been bugging me for about a week now...

I have a form that allows users to filter records, simple enough. But I want
to give them the option to export the filtered records to Excel. I don't
want to use the docmd.outputTo due to it won't filter the records, it puts
all of the records in the file. I've looked around and found some code
(actually that I'm already using), but the problem is that it outputs
EVERYTHING on the form. My goal is to output only the fields that are on the
form.

I found another bit of code (following) that does what I need it to do in
the way of only exporting the data and field names, but I've inadvertantly
deleted part of one line (the Set rs=currentDB) and commented it out.

Dim xlApp As Object
Dim xlBook As Object
Dim rs As DAO.Recordset
Dim sql As String
Dim i As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add

sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source if
not a subform
'Set rs = CurrentDb.

For i = 1 To rs.Fields.Count
xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to
Excel
Next i
xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset data
through Excel

' You can add whatever other formatting you want by running Excel VBA
throught the xlApp object

xlApp.Visible = True

Set xlApp = Nothing
Set xlBook = Nothing
Set rs = Nothing


I know that the rs statement is for the Record Source. But...I'd like to
somehow have that statment pull in the filtered data. Most everything I've
seen with the OpenRecordset command uses a table or query. The filter is on
the form and changes dynamically based on what's entered.

Any help would be greatly appreciated.

Thanks.

Jeff

 

[Allen Browne]

Jeff, what I do is to create a query to use for the export, and then write
the SQL property of the QueryDef before I use TransferSpreadsheet so that it
outputs the desired records.

This kind of thing:

Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT Table1.* FROM Table1 " & vbCrLf
Const strcTail = "ORDER BY SomeField;"
Const strcExportQuery = "Query1" 'Name of the query for exports.

If Me.FilterOn Then
strWhere = "WHERE " & Me.Filter & vbCrLf
End If
dbEngine(0)(0).QueryDefs(strcExportQuery).SQL = strcStub & strWhere &
strcTail

strFile = "C:\Data\MyExport.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strcExportQuery, strFile

 

[jtgarrison]

Allen -

I've never used the QueryDefs before, so I'm not entirely sure how to
use them.

Here's the code I changed it to...

Dim strWhere As String
Dim strFile As String

Const strcStub = "SELECT tblEDIOrderLinks.*,
tblEDIDispatchFile.DF_ORDER, tblEDIDispatchFile.DF_CUSTNUM,
tblProjectMain.prjProjectName, tblContacts.contactFullName AS TechName
" & vbCrLf
Const strcTail = "ORDER BY tblEDIOrderLinks.Order_EDI_ID;"
Const strcExportQuery = "EDI Filter Export" 'Name of the query
for exports.


If Me.FilterOn Then
strWhere = "WHERE " & Me.Filter & vbCrLf
End If
DBEngine(0)(0).QueryDefs(strcExportQuery).sql = strcStub &
strWhere & strcTail


strFile = "C:\Data\MyExport.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
strcExportQuery, strFile

When I place the code in the OnClick and execute, I get the following
error...

Run-tiime error '3265':

Item not found in this collection.

What am I missing?

Thanks

Jeff

 

[Allen Browne]

That all looks okay.

You'll probably find it works if you try it again now.
Otherwise try:
CurrentDb
in place of:
dbEngine(0)(0)

CurrentDb is always up to date, whereas dbEngine(0)(0) doesn't know about
the query you just created (which is why I say it will probably work if you
close the database and try again.)

 

[klimshakepo]

Here's one that's been bugging me for about a week now...

I have a form that allows users to filter records, simple enough. But I want
to give them the option to export the filtered records to Excel. I don't
want to use the docmd.outputTo due to it won't filter the records, it puts
all of the records in the file. I've looked around and found some code
(actually that I'm already using), but the problem is that it outputs
EVERYTHING on the form. My goal is to output only the fields that are on the
form.

I found another bit of code (following) that does what I need it to do in
the way of only exporting the data and field names, but I've inadvertantly
deleted part of one line (the Set rs=currentDB) and commented it out.

Dim xlApp As Object
Dim xlBook As Object
Dim rs As DAO.Recordset
Dim sql As String
Dim i As Integer

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add

sql = Forms("frmProjectEDISearch").Form.RecordSource 'Your record source if
not a subform
'Set rs = CurrentDb.

For i = 1 To rs.Fields.Count
xlBook.Sheets(1).Cells(1, i) = rs.Fields(i - 1).Name 'Write Field names to
Excel
Next i
xlBook.Sheets(1).Cells(2, 1).CopyFromRecordset rs 'Import the recordset data
through Excel

' You can add whatever other formatting you want by running Excel VBA
throught the xlApp object

xlApp.Visible = True

Set xlApp = Nothing
Set xlBook = Nothing
Set rs = Nothing


I know that the rs statement is for the Record Source. But...I'd like to
somehow have that statment pull in the filtered data. Most everything I've
seen with the OpenRecordset command uses a table or query. The filter is on
the form and changes dynamically based on what's entered.

Any help would be greatly appreciated.

Thanks.

Jeff

Hi Alienbrown,

Thanks for this helpful info, can I ask you about multiple filter, then export to excel. Please help, thanks