Exporting of Search results to Excel

  • Thread starter Thread starter end user
  • Start date Start date
E

end user

Need to know if results of search/filter can be exported to Excel without
formal report being needed. I.e., in SQL you can save a search/query and
input the "data" you want to search on at any time, does Access have the same
capability? As an end user of an access database I can search and then
filter the database but am told I cannot download the results of that
search/filter to Excel without having a formal report.

If yes, please advise how I can relate this requirement to an Access
programmer.

Thanks
 
Create a query for the purpose of exporting.
Write the SQL property of the query before you export.

1. Create a sample query that returns the correct columns for (say) one
record. Save the query with a name such as qryExportExcel.

2. Switch the query to SQL View to see the SQL statement. Copy it so you can
use it in your code.

3. In the Click event procedure of the command button you will use to export
to Excel, break the string into two after the word WHERE. Like this:
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT Table1.* FROM Table1 WHERE "
Const strcTail = "ORDER BY Field1;"

4. Build the WHERE clause as you need it. This kind of thing:
strWhere = "(ClientID = " & Me.[ClientID] & ") AND (Inactive =False)"

5. Create the query string by concatenating the where clause into the query
string, and assigning it to the SQL property of the querydef:
CurrentDb.QueryDefs("qryExportExcel").SQL = _
strcStub & strWhere & strcTail

6. Do the export:
strFile = "C:\MyFolder\MyFile.xls"
DoCmd.TransferSpreadsheet acExport , acSpreadsheetTypeExcel9, _
"qryExportExcel", strFile, True
 
Back
Top