The literature says you can't, but as others have remarked you can name a specific range ("mydata") within a named Excel spreadsheet ("C:/Marks.xls") when exporting from Access to Excel. However, I am not figured out how to eliminate the first line of the query, which is the definition of the fields (e.g. "student no.", "name", "mark"). Using either the "true" or "false" switch didn't eliminate the first line.
How do I eliminate the first line of the query (i.e. the names of the various fields)?
The literature says you can't, but as others have remarked you can name a specific range ("mydata") within a named Excel spreadsheet ("C:/Marks.xls") when exporting from Access to Excel.
What literature says you can't? Have you check the Access Help in your version of Access. It shows that it can be done in the Access help file for Access 2010.
From the Access Help file about exporting the field names
When you export Access table or select query data to a spreadsheet, the field names are inserted into the first row of the spreadsheet no matter what you enter for this argument.
Try using Excel Automation and CopyFromRecordset
' changed to
' using late binding so you do not need to set a reference to the Excel lib
Dim objExcel As Object
Dim objWB As Object
Dim objWS As Object
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
' create a workbook
Set objWB = objExcel.Workbooks.Add
Set objWS = objWB.ActiveSheet
' copy recordset
' save file with the desired name
objExcel.UserControl = True
Let us not be defensive. Not to be a smartass, but here is what Access help says:
"Range Optional Variant. A string expression that's a valid range of cells or the name of a range in the spreadsheet. This argument applies only to importing. Leave this argument blank to import the entire spreadsheet. When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail."
Not true. I was not the clever one to figure this out!
Sorry if I can across as defensive. I was just curious where you say it. When you said literature I I was think you meant a book or some other publication that was probably printed, not the Help File. My misunderstanding.
In a version prior to 2003, IIRC, that help file statement was true. It has sense been fixed, but it appears that the help file was never properly updated.
I did check a machine with Access 2003 and it does have the help file that states it incorrectly. Even MSDN has the same thing here.
I normally use Excel automation when exporting. I have not have not run into this issue since I normally don't use the TransferSpreadsheet method.