Open up New Excel workbook from Access

Hi all
I am trying to open up a new Excel workbook from Access query. Want to open the query up in Excel and give the user the option to save it there if they want to keep the information. I am currently creating a file and saving the query from the database there. Is it possible to execute a query and open up excel with the results of the query?

My code is:
'SQL SELECT all from table
strSql = strSql & strSqlSign

Set qdf = db.CreateQueryDef("tmpExcelOut", strSql)
'Set qdf = db.CreateQueryDef("qryTmpExport", strSql)
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, ("tmpExcelOut"), fPath, True
'Delete the object
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "tmpExcelOut"
DoCmd.SetWarnings True

Set qdf = Nothing
Set db = Nothing

Thanks

What I do is use a macro. In the macro you can specify the file path or leave it blank and access will prompt the user where to save the Excel file. Then in Auto Start select Yes. In your code above use the DoCmd.RunMacro "NameOfMacro".

This will create your Excel spreadsheet from your query results and then open the Excel file automatically for viewing.

Was this what you were looking for?

lwells

Yes this is exactly what I want to do.

Few questions:
Is the object type in the macro Query (makes sense since I am exporting a query results)?

Should the action in the macro be OutputTo?

Where should the query parameter be in the DoCmd.RunMacro be? The code is:

strSql = strSql & strSqlSign
Set qdf = db.CreateQueryDef("tmpExcelOut", strSql)
DoCmd.RunMacro "SaveToExcel", qdf

Not sure where I should put the QueryDef

Thanks for the help

Yes,

It would be

OutputTo
Query------------>tmpExcelOut
OutputFormat ---->Microsoft Excel 97-2003 (*.xls)
Output File-------> (optional)Type the file path here or leave blank
AutoStart-------->Yes

I gather you named the macro SaveToExcel which is great.

In your code this is how it would look


strSql = strSql & strSqlSign

Set qdf = db.CreateQueryDef("tmpExcelOut", strSql)

DoCmd.RunMacro "SaveToExcel"

'Delete the object
DoCmd.SetWarnings False
DoCmd.DeleteObject acQuery, "tmpExcelOut"
DoCmd.SetWarnings True

Set qdf = Nothing
Set db = Nothing

This will open an excel workbook based on the temp query you created in your code.

I would recommend that you create your temp query, but don't delete it so you can compile your database...otherwise the object will be missing. Once you compile, then you can delete the query.

lwells

Brilliant.
Works like magic, exactly what I wanted thanks again.