Link to home
Start Free TrialLog in
Avatar of Conernesto
ConernestoFlag for United States of America

asked on

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
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

sorry, BUT No..
the only way you can do that is to use VBA codes...
Avatar of Conernesto

ASKER

Do you have the code to export the query results to en Excel worksheet without the field names?
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.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
TransferSpreadsheet is one line of code and includes the option to include/not include column headers.
Be aware that TransferSpreadsheet is limited to 64K rows of output.
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.
Thank you all for your feedback.
Great code. Thank you.
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.
@Pat

please don't insist on your TransferSpreadsheet method, because it is not gonna work for the OP requirement..
@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.
<  the request was to export the data without headers.  The TransferSpreadsheet does that.>
Show us, maybe we can learn from this.
@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.
I give up.  Write all the code you want even when a simple VBA action does exactly what is requested.
@Pat

Is it possible to use TransferSpreadsheet to update or overwrite an existing worksheet or only create/overwrite an entire workbook?
If the target file name already exists, Access will add a sheet with the query name or overwrite an existing one.
.

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

ether of the two will do ;-)