Conernesto
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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, acSpreadsheetTypeExcel12Xm l, "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 acSpreadsheetTypeExcel12Xm l.
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xm
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 acSpreadsheetTypeExcel12Xm
ASKER
Thank you all for your feedback.
ASKER
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..
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.
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.
Show us, maybe we can learn from this.
@Pat
write a code loop to paste the data row by rowThat 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?
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
there will always be header, no matter what the condition is using transferSpreadsheet
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 ;-)
ether of the two will do ;-)
the only way you can do that is to use VBA codes...