# Export a simple query result to an excel file



## wop_nuno (Mar 9, 2009)

Hi everyone,

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

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

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
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "strSQL", strFullPath & "Test.xls", False

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

Can anyone help me?


----------



## slurpee55 (Oct 20, 2004)

You haven't defined it as a type of object. However, I would simply build a regular query - say called qryexport - and use that instead of defining the export in the code (it is far easier and faster to alter a query, and that would be all you needed to do).
That would give you something like
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "qryexport", strFullPath & "Test.xls"


----------



## wop_nuno (Mar 9, 2009)

Hi slurpee55,

Thanks for the reply.

What i'm trying to do is exactly what you said.
In my case" strSQL" is the same of your "qryexport", the only problem is that i don't know how, and where to "define" the query.

Can you help me?

Thanks


----------



## slurpee55 (Oct 20, 2004)

Have you actually built a simple query and named it "strSQL"?
Also, make sure all spellings are exactly the same - " strSQL" isn't the same as "strSQL".


----------



## wop_nuno (Mar 9, 2009)

Hi slurpee55,

My code is in my initial post.

strSQL = "SELECT ID, NomeCandidato, DataEntrada FROM FichaCandidatura 

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")

Isn't this my query "SELECT ID, NomeCandidato, DataEntrada FROM FichaCandidatura" (strSQl)?


----------



## slurpee55 (Oct 20, 2004)

A SQL statement should read like this 
SELECT FichaCandidatura .ID, FichaCandidatura .NomeCandidato, FichaCandidatura.DataEntrada FROM FichaCandidatura;

Unless you are adept at SQL (and I know only a few who are - not me!) it literally is much easier to build a query with what you want in Access - don't try to code it in your VBA.
Then just name the query as the source of the data in your VBA.


----------



## wop_nuno (Mar 9, 2009)

Hi slurpee55,

But that's what i'm doing....


----------



## slurpee55 (Oct 20, 2004)

No, you are attempting to define the query within the code.
In Access (you never stated this, but i assumed this export was from Access to Excel - you do state the Excel part), go to Queries.
Click on Create query in Design view.
In the pop-up, find FichaCandidatura (Is it a table or a query? Your names don't tell me.)
Drag the fields ID, NomeCandidato, and DataEntrada to the query.
Click on save and name it - let's call it strSQL for simplicity.
(Note, if those 3 fields are all that is in FichaCandidatura, you don't need to do the above, but I don't think they are, given your attempt to define the SQL.)

At any rate, once you have built an actual query named strSQL, your code should work.

If you go tothe query and look at it in Design view and then go to SQL view, you will find that your SQL is what I wrote above, that is:

SELECT FichaCandidatura .ID, FichaCandidatura .NomeCandidato, FichaCandidatura.DataEntrada FROM FichaCandidatura;


----------



## wop_nuno (Mar 9, 2009)

Ok, i've already did what you told me.

I created a view (i think is the same as a querie) called strSQL.

I've used this code line:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "strSQl", strFullPath & "Test.xls", False

and i get this error: Microsoft Office doesn't find the object strSQL.


----------



## slurpee55 (Oct 20, 2004)

What are you using? I have no idea what you are talking about with a "view." Databases have tables, queries, forms and reports, with some additional items varying by program and versions - but none that I know of called a view.


----------



## wop_nuno (Mar 9, 2009)

I said view, because it's in portuguese. 

So i've created a querie called strSQL, but i still get the message i told you.


----------



## slurpee55 (Oct 20, 2004)

Oh, I would bet you are using MySQL. My bad.
More later as I read up on views.


----------



## slurpee55 (Oct 20, 2004)

In Portuguese it is called what? A pergunta? A questão?
And this code is placed in Access, I presume?


----------



## wop_nuno (Mar 9, 2009)

Could it be because i'm using Access connecting to a SQL database (ADP)?


----------



## wop_nuno (Mar 9, 2009)

LOLLLL

Query literally translating is " questão" (question), but it's called a "view".

So you don't know how i can get trough this?


----------



## slurpee55 (Oct 20, 2004)

If the the table or the query exists and you are still getting this same error message, the problem is probably due to database corruption. Not good news.
Make a full copy of your data base by building a new one and importing all the tables, queries, etc. into it. Do not copy it - you will need to work on redoing code later. First, just get a clean and uncorrupted copy of your data.


----------



## slurpee55 (Oct 20, 2004)

wop_nuno said:


> Could it be because i'm using Access connecting to a SQL database (ADP)?


Okay, now we are getting to the heart of the problem. First, what version of Access are you using? I have read several things about Access 2007 having problems with SQL 2008, for instance, so I should know both these specifics.


----------



## wop_nuno (Mar 9, 2009)

No,if i put a table name it works.
It doesn't work is when i put the querie name.


----------



## wop_nuno (Mar 9, 2009)

I use Access 2003 and SQL Server 2005.


----------



## slurpee55 (Oct 20, 2004)

First, I would like to apologize - I was getting confused, and should have gone back and read your first post again...led us astray here.
The quickest workaround I know of would be to make your query a make table query, and then point the VBA at it.
But let me see if my friend OBP is around - he has forgotten things about Access and other programs that I will never even learn.


----------



## slurpee55 (Oct 20, 2004)

nuno, I see you have reposted this in the Business Apps section - probably a better place for it, but cross-posting is not something forums and people generally like (I know because OBP told me about it!)
So please mark this thread as Solved - OBP will be able to help you.


----------



## wop_nuno (Mar 9, 2009)

Yes i did.

I also told him that you were helping me.

Thanks for the help you gave.


----------



## slurpee55 (Oct 20, 2004)

Please use the button at the top of the page to mark this thread as Solved (only the thread originator or the moderators are able to do this.)
Thanks - see you at the other thread!


----------

