Export to excel & format workbook

[Nathan Bloomfield]

I would like to export a query to excel and perform some basic
formatting on the new workbook.

If it is not too difficult, can some give me an example how I would
achieve the following in VBA:

export [qryTest] to "X:\TestWorkbook.xls"
Insert 4 rows at top of sheet 1
Insert text "Report Name:" into cell A1
Insert query name into cell A4

I imagine that the basic process would be to
a) export the data
b) open the xls workbook
c) format the sheet
d) close the workbook

Any advise will be much appreciated.

Cheers,
Nathan

[Rog]

Dim xlfile As String
xlfile = "C:\temp\spreadsheet1.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9 (or
whatever version you have), "Query1", xlfile, True
Dim xlApp As Object, sheet As Object, x As Integer
Set xlApp = CreateObject("Excel.Application")
xlApp.Workbooks.Open (xlfile)
Set sheet = xlApp.ActiveWorkbook.Sheets(1)
sheet.Rows("1:1").Select
For x = 1 To 4
xlApp.Selection.Insert Shift:=xlDown
Next
sheet.cells(1, 1) = "Report name:"
sheet.cells(4, 1) = "Query1"
xlApp.Save
xlApp.Quit
Set xlApp = Nothing

You need to set a reference to Excel to do this. Open any module,
select Tools/Reference and check Microsoft Excel x.x object library.