Remove field names from a query in Access

I have an access database.
The database has a query with field names : Zip, City, and State. I copy the results of the query to an Excel worksheet. Is there a way to remove the field names from the query results?

Have:
Postal      City      State
10701      YONKERS      NY
10702      YONKERS      NY

Want:
10701      YONKERS      NY
10702      YONKERS      NY
ConernestoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
sorry, BUT No..
the only way you can do that is to use VBA codes...
0
ConernestoAuthor Commented:
Do you have the code to export the query results to en Excel worksheet without the field names?
0
aikimarkCommented:
You can use Office/Excel automation and then use the CopyFromRecordset method of an Excel range object to paste the values without the field names (column headings).  If you do a search in EE or the Internet for "CopyFromRecordset", you will see many code samples.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Rey Obrero (Capricorn1)Commented:
sub export2Excel()
dim xlObj as object
dim rs as dao.recordset

set rs=currentdb.openrecordset("yourQueryNameHere")
set xlObj=createobject("excel.application")
      xlObj.workbooks.add
      with xlObj
           .visible=true
           .worksheets(1).select
           .range("a1").copyfromrecordset rs
      end with
      xlObj.quit

end sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PatHartmanCommented:
TransferSpreadsheet is one line of code and includes the option to include/not include column headers.
0
aikimarkCommented:
Be aware that TransferSpreadsheet is limited to 64K rows of output.
0
PatHartmanCommented:
I believe the rowcount limit depends on what version of excel you specify in the expression.  This expression -
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "qTEST_top100000", "C:\Pat\Test100000.xlsx", True

Transferred 100,000 rows.  I don't know what the max is for Excel 2013 (32 bit).  I know I've done at least 250,000 in Excel 2010 (64 bit)

I'm not sure who made up the constant names.  You'd think that acSpreadsheetTypeExcel12 would be the xlsx file we know and love but it isn't.  You must use acSpreadsheetTypeExcel12Xml.
0
ConernestoAuthor Commented:
Thank you all for your feedback.
0
ConernestoAuthor Commented:
Great code. Thank you.
0
PatHartmanCommented:
If you are going to use Rey's code, you should probably set .visible to false.  Populating the sheet via code is slower than via TransferSpreadsheet and even more so if you have Excel visible so you can watch it.  The exports I work with are too large to do this way.
0
Rey Obrero (Capricorn1)Commented:
@Pat

please don't insist on your TransferSpreadsheet method, because it is not gonna work for the OP requirement..
0
PatHartmanCommented:
@Rey,
I don't know how you can say that.  the request was to export the data without headers.  The TransferSpreadsheet does that.  There is no reason to write a code loop to paste the data row by row.  For small recordsets it won't matter.  For larger ones it will, especially if Excel is visible.  Although, I  sometimes like to watch my automations post data.  It can be entertaining but I generally turn it off for speed.
0
Rey Obrero (Capricorn1)Commented:
<  the request was to export the data without headers.  The TransferSpreadsheet does that.>
Show us, maybe we can learn from this.
0
aikimarkCommented:
@Pat
write a code loop to paste the data row by row
That is not what I described in my comment nor is it what Rey coded (in the accepted solution comment).  There is no row/field iteration.  Just instantiate the recordset object and then invoke the Excel range object's CopyFromRecordset method.
0
PatHartmanCommented:
I give up.  Write all the code you want even when a simple VBA action does exactly what is requested.
0
aikimarkCommented:
@Pat

Is it possible to use TransferSpreadsheet to update or overwrite an existing worksheet or only create/overwrite an entire workbook?
0
PatHartmanCommented:
If the target file name already exists, Access will add a sheet with the query name or overwrite an existing one.
0
Rey Obrero (Capricorn1)Commented:
.

there will always be header, no matter what the condition is using transferSpreadsheet
0
PatHartmanCommented:
You are correct.  Apparently I haven't done this in some time.  The flag currently only works for importing but not for exporting.
TransferSpreadsheet
0
aikimarkCommented:
It's so weird to refer to you as "Rey" when it has been "cap1" for so many years.
0
Rey Obrero (Capricorn1)Commented:
@aikimark,

ether of the two will do ;-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.