TransferSpreadsheet without specifying a filename to export to

Hi Lonely,

As Hans said, you will need a file name/path but you could also add the filename without a prompt using something like:

Dim strExpDirectory As String

Dim strfullname As String

Dim strPath As String

Dim strQry As String

strQry = "qrytest"

strExpDirectory = Application.CurrentProject.Path & ""

strfullname = strExpDirectory & Format(Date, "yyyy-mm-dd")

DoCmd.TransferSpreadsheet acExport, , strQry, strfullname, True

This will save the fie to the same directory as your access database with the file name as today's date.

If you want to also open the spreadsheet once it is exported then you could use:

Dim strExpDirectory As String

Dim strfullname As String

Dim strPath As String

Dim xlApp As Object

Dim strQry As String

strQry = "qrytest"

strExpDirectory = Application.CurrentProject.Path & ""

strfullname = strExpDirectory & Format(Date, "yyyy-mm-dd")

DoCmd.TransferSpreadsheet acExport, , strQry, strfullname, True

Set xlApp = CreateObject("Excel.Application")

        xlApp.Workbooks.Open (strfullname)

        xlApp.Visible = True

I've used today's date as the file name but you could just have something generic

Sadly, the help is incorrect: the FileName argument is required. You might use the InputBox function to prompt the user for a filename.