DoCmd.TransferSpreadsheet acExport Question

[MTSPEER]

Hello,

I was wondering if it's possible to export a query without saving it as an object in the front-end. Write out the query in the module and export it? Here below is my code: Let me know is something is wrong. Thanks

Public Sub Test()
Dim Str As String
Dim fd As FileDialog

Set fd = Application.FileDialog(msoFileDialogSaveAs)
Str = "SELECT Hello.test FROM Table;"

With fd
.AllowMultiSelect = False
.Title = "Save File"
.InitialFileName = "Str.xlsx"

If .Show = True Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Str", .SelectedItems(1)

Else

MsgBox "No File was selected"

End If

End With
Set fd = Nothing



End Sub

[aytee111]

Sadly no. Your options are to export a table or a query, or you can open Excel and copy/paste. You can turn your "Str" into a make-table query, run it and export the table or use the Str to create a query.

[MTSPEER]

That's lame. But thank you. I just hate having so many objects.

[aytee111]

Delete them when you are done.

[ssanfu]

I was wondering if it's possible to export a query without saving it as an object in the front-end

I would say it is not possible.

Reasoning:
One of the parameters of the DoCmd.TransferSpreadsheet method is a string that is the name of a table or query.
If you have a table named "Table1", access knows it should open the table to get the data.
If you have a saved query named "Query1", access knows it should open the execute the query to get the data.

Lets say you have a variable "Var1" that is declared as a string. The value of "Var1" is "SELECT Hello.test FROM Table;".
If you use "Var1" (with quotes) as the value for the parameter TableName, Access looks for a Table/Query named "Var1". Doesn't execute because "Var1" doesn't exist.
What happens if you replace "Var1" with the SQL string "SELECT Hello.test FROM Table;"?? Still doesn't execute because Access doesn't know how to handle the SQL because it is looking for the name of a Table/Query.

If you use Var1 (without quotes), and variable Var1 value is "Table1", the command will execute because Access knows that Var1 is a variable, so it reads the value Var1 (=Table1), finds that "Table1" exists and the command completes.

If you change the value of Var1 to be the SQL string "SELECT Hello.test FROM Table;" what happens?
Access will try to find a table or query named "SELECT Hello.test FROM Table;". There is no table/query named "SELECT Hello.test FROM Table;" so the command fails.


I don't see any possibility of using a SQL string in place of a table or query name.......

[ItsMe]

You can automate Excel to copy a recordset that exists in Access. For instance, you can create a DAO recordset using an SQL statement or clone a Form's recordset. Then, you can copy that recordset over to your Worksheet. Here is a snippet.
https://www.accessforums.net/showthr...494#post281494