Exporting from Access 2000 to Excel via script

[McGeeky]

Hi. I want to be able to export the results of an Access 2000 query (passing
in filter parameters) to an Excel worksheet automatically.

E.g. a user opens an Access 2000 form, then would enter in a destination
file name for Excel, some filter parameters, then click a button. The export
would happen automatically.

Is it possible to do this?

Many thanks.

McGeeky.

 

[Allen Browne]

Create a query to use for the export.
Modify the SQL property of the QueryDef, so it contains your criteria.
Use TransferSpreadsheet to export the query.

This kind of thing:

Dim strWhere As String
Dim strSql As String
strWhere = " WHERE (City = """ & Me.txtCity & """)"
strSql = "SELECT * FROM Table1 " & strWhere & " ORDER BY [SomeField];"
CurrentDb.QueryDefs("Query1").SQL = strSql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Query1", "C:\MyFolder\MyFile.xls", True

 

[McGeeky]

Thanks for that Allen, exactly what I need!!

Create a query to use for the export.
Modify the SQL property of the QueryDef, so it contains your criteria.
Use TransferSpreadsheet to export the query.

This kind of thing:

Dim strWhere As String
Dim strSql As String
strWhere = " WHERE (City = """ & Me.txtCity & """)"
strSql = "SELECT * FROM Table1 " & strWhere & " ORDER BY [SomeField];"
CurrentDb.QueryDefs("Query1").SQL = strSql
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
"Query1", "C:\MyFolder\MyFile.xls", True
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi. I want to be able to export the results of an Access 2000 query
(passing in filter parameters) to an Excel worksheet automatically.

E.g. a user opens an Access 2000 form, then would enter in a destination
file name for Excel, some filter parameters, then click a button. The
export would happen automatically.