This question is not as simple as it looks because of that three-line offset.
TransferSpreadSheet is powerful but it has its limits. I looked it up before I answered. As I had correctly recalled, you cannot export TO a specific range. You CAN import FROM a given range, but for export, the range field of Docmd.TransferSpreadSheet MUST be blank or the command will fail.
Therefore, if you HAVE to have this done entirely in VBA, you might have to learn about creating and using application objects. You could do this in any of several ways, but two of those ways require you to open Excel via an application object and then manipulate the spreadsheet directly through code.
To see how this is done, use the SEARCH feature of this forum to look up "Application Object" and I'm sure you will see lots of code on the topic. However, since I cannot tell how comfortable you are with VBA, I'm not going to try to post a complex code solution until you can look at what is involved.
Here's the good news: If you choose to use app objects, it isn't terrible. Once the Excel object is opened, you can open a workbook, then select and make current a worksheet inside the workbook. When you do, the rows and columns are almost trivially easy to manipulate. However, you might need to open a recordset to export your data.
OR you could just do the export more or less as you showed in your code, but provide the flag that says "no column headers" (one of the optional arguments after the file name.) THEN open the app object, open the workbook, select the sheet, and insert three blank rows at the top. In either app object case, you would then close & save the workbook, tell Excel to Quit (through the application object), and do other cleanup as might be required. I'll avoid the controversy raging in another thread on just how much more cleanup is required, but you can always ask what else should be done if you choose one of these methods.
A third possibility, also a bit ugly, is to create a temporary empty table for export where the desired output fields are all strings (regardless of what they are in your actual table). Or you can create a static temporary table and erase it before you start this. Then create three blank records at the top of that table and the four records you wanted, also as strings that have already been formatted. Then export THAT using the DoCmd as you showed earlier. With the three blank records at top, it would look the same.
However, there is a trick to this, too, since tables have no inherent or guaranteed order. You would have one more field in this temporary table holding a number showing the order in which you want that record to be written, perhaps no more than a desired line number. Then instead of exporting directly from the table, export from a query similar to:
Code:
SELECT FieldA, FieldB FROM TempTable ORDER BY LineNumber ;
Then make the three blank records have line numbers 1, 2, and 3. Make your data records have line numbers 4, 5, 6, and 7. The line number does NOT have to be in the SELECT {fields} portion of the SQL statement. You can just have it in the ORDER BY clause.
This is part of a piece of code i sometimes use. It is faster than TransferSpreadsheet and it gets the job done nicely.
Code:
Public Sub ExportToExcel(rst As Recordset)
Dim appXL As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Set appXL = New Excel.Application 'or getobject existing excel object
Set wb = appXL.Workbooks.Add 'or open an existing excel workbook
Set ws = wb.Worksheets(1)
appXL.Visible = False
ws.Range("A4").CopyFromRecordset rst
appXL.Visible = True
wb.Save
Set ws = Nothing
Set wb = Nothing
Set appXL = Nothing
End Sub
Result Export to excel.png
