Export a simple query result to an excel file

[wop_nuno]

Hi everyone,

I'm kind of a newbie, when programming in VB so i'm having a little bit of trouble exporting a simple query result to a excel file.

I've found this code that works fine when i use a table:

Const FILE_PATH As String = "C:\My Documents\"
Dim FULLPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "tbl_name", strFullPath & "Test.xls", False

MsgBox ("Export Complete")

How can i do this using a query?
Let's say my query is:
strSQL = "SELECT ID, NomeCandidato, DataEntrada FROM FichaCandidatura"

I've tried to do this

Const FILE_PATH As String = "C:\My Documents\"
Dim FULLPath As String
strFullPath = FILE_PATH
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "strSQL", strFullPath & "Test.xls", False
MsgBox ("Export Complete")


but is says that it can't find the object "strSQL".

How do i define my query to use in that line?

Can anyone help me?

 

[DCrake]

Remove the quotes from around the strSQL

 

[wop_nuno]

Hi DCrake,
Thanks for the reply.

I tried what you said, and i got an error. It's says: "Microsoft Office Access doesn't find the object ' ID, NomeCandidato, DataEntrada FROM FichaCandidatura'.

 

[DCrake]

After a few simple tests it appears you cannot use an SQL statement as the recordsouce of the data you want to transfer. It must be a stored table or query.

You would need to change the sql of the query with QueryDefs prior to transfer.

 

[wop_nuno]

Hi DCrake,

You said: "It must be a stored table or query." I understand the table, but what do you mean a query? How can i make a "query"?

"You would need to change the sql of the query with QueryDefs prior to transfer" ---How do i do this?

 

[DCrake]

A query is an alternative view of a table. If you click on the queries tab and create a simple query from your table and try that.

 

[wop_nuno]

Sorry but my access version is in Portuguese.

Is a "query" the same as a view?

I created a view, called "test", so i tried this line

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, test, strFullPath & "Test.xls", False

but it gives me an error: it says he needs a argument "table name".

 

[DCrake]

What I suggest you do is to create a macro to do what you want and point it to your view named test. Save the marco and run it. If it works then go to Tools > Macros > convert macro to visual basic.

Let it convert it then edit the module it creates and examine the syntax

 

[wop_nuno]

Like i said i really newbie in Access.

What I suggest you do is to create a macro to do what you want and point it to your view named test. Save the marco and run it. If it works then go to Tools > Macros > convert macro to visual basic.

I've already did this. My macro points to a view that the only thing it does is select some fields from the table.

Let it convert it then edit the module it creates and examine the syntax

What should i look for in here?