how to keep query design properties when export it into Excel?

[Uriya Levy]

hi all,

I'm trying to export a query by calling:

 DoCmd.TransferSpreadsheet(acExport, , [Queryname], [FileName], True) 

but the excel design is different from how to query's looks like.
for example, my query sheet direction is from right-to-left
but in the excel file it's from left-to-right.

how can I preserve my design settings in the excel file?

thanks,

[Narender Sagar]

Hi Levy,
I understand it as you want tokeep same formatting in excel while exporting data from query.
Can you tell us which version of Access you are using. if you are using Access 2007 or Access 2010, then at the time of exporting, wizard will show you a window wherein you can choose options of "Export data with formatting and layout"

[Uriya Levy]

Thanks Narender Sagar for reply,
you got it right, I want to export my query in the same format, as I see it in the access windows.
sorry that I didn't mention it before, Im using Access 2007.
but keep it in mind that Im using DoCmd.TransferSpreadsheet method (using VBA/Access script) to export the query, I cannot use any wizard or something GUI-ly.
but if there any setting option that need to be tick or change I will be glad to know...

thanks,

[Narender Sagar]

Open the desired query, and then over Ribbon you'll find "External Data". Go to External Data and under "Export" you will find Export to Excel Button quite prominently. If you are unable to find above then probably you need to check service pack for your office 2007 from Microsoft (Because I remember, in previous version of Office 2007, this feature was missing.)
Ref. following link : http://support.microsoft.com/kb/934833
"Unless you install the Office 2007 suite Service Pack 2, you cannot export a report to an Excel format in Access 2007"

[Uriya Levy]

thank you for your reply,
but it seems you didn't get my question...I'll try to make it more clear - I don't want to use any GUI to export my query, I want to only use the DoCmd.TransferSpreadsheet method, and still keep the query's display settings (as you post it earlier).
I don't know how to do it...
I hope it's more clear now..

thanks,