Control Excel Formatting From Access Query Export

[headly]

I think this may be beyond Access's DDE capabilities but thought I'd float
this out to see if anyone had an ideas/thoughts or leads/links:

I export an (open) Access query to Excel using

DoCmd.RunCommand (acCmdOutputToExcel)

Once my query data reaches Excel, I want to format

a) Page setup - for landscape/fit to page/legal etc
b) Column width/Row height
c) Wrap cell contents

 

[Douglas J. Steele]

Try using Automation.

Here's a excerpt from code I use to format the data after I export it. It
doesn't do everything you're asking for, but the rest of what you want is
rather simple to add:

Dim xlCurr As Object
Dim wkbCurr As Object
Dim wksCurr As Object
Dim strFile As String

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"qryExportToExcelPage1", strFile, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"qryExportToExcelPage2", strFile, False
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, _
"qryExportToExcelPage3", strFile, False

Set xlCurr = CreateObject("Excel.Application")
Set wkbCurr = xlCurr.Workbooks.Open(strFile)
Set wksCurr = wkbCurr.Sheets("qryExportToExcelPage1")
With wksCurr
.Name = "Page 1"
.Cells.Font.Name = "Tahoma"
.Cells.Font.Size = 8
.Rows("1:1").Font.Bold = True
.Columns("A:C").Columns.AutoFit
End With