Exporting to excel

[Joe C]

I have a queery which I build off of a number of forms submitted to the user through a macro. It all works perfect when it is down I dowload the queery to excel.
The only problem I have is I wanted to download using a templated excel sheet. Is this possible.
Any help is appreciated, I dont seem to be able to do this with the output to or transfer spreadsheet macros. Am I missing something.
Any help is greatly appreciated!

 

[Corticus]

template

Hi,

I would suggest code as follows:

DoCmd.OutputTo acReport, "MyReport", acFormatXLS

The key to this code is that your exporting a report, and not a table. Formatting applied to the report in Access will be transferred to your Excel table, effectively, a template.

If you can't achieve the look you want in your Excel report, you can adjust the formating of the report from Access, but it is a major pain...try:

Sub export()

'name spreadsheet for object reference
Dim ExcelSheet As Object

'name string vars for dir and spreadsheet file name
Dim savename As String

'Add reference to Excel library
Call XLLibrary

'get path info
savename = inputbox("Where would youlike to save?")

'transfer qryXL to file 'savename', closes file
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "YourQuery/Table", Savename, True

'opens new file
Workbooks.Open Filename:=savename

'set object 'savename'(excel spreadsheet) for action
Set ExcelSheet = Workbooks.Application.ActiveWorkbook

'these are just some random formatting examples
With ExcelSheet
    .Application.Visible = True
    .Sheets(1).Range("A1:E1").Font.Bold = True
    .Sheets(1).Columns("A:A").Columns.AutoFit
    .Sheets(1).Columns("B:B").Columns.AutoFit
    With Sheets(1)
        With Columns("C:C").Columns
            .AutoFit
            .HorizontalAlignment = xlRight
            .NumberFormat = "#,##0"
            .Range("C1:E1").HorizontalAlignment = xlCenter
        End With
        With Columns("D:D").Columns
            .AutoFit
            .HorizontalAlignment = xlRight
            .NumberFormat = "#,##0"
        End With
        With Columns("E:E").Columns
            .AutoFit
            .HorizontalAlignment = xlRight
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:="0"
            .FormatConditions(1).Font.ColorIndex = 2
            .FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, Formula1:="0"
            .FormatConditions(2).Font.ColorIndex = 3
        End With
        Range("A1:E1").FormatConditions.Delete
        Range("C1:E1").HorizontalAlignment = xlCenter
    End With
    .Save
    .Quit
End With

End Sub

Sub XLLibrary()

'Add reference to Excel library

On Error Resume Next
Application.References.AddFromGuid "{00020813-0000-0000-C000-000000000046}", 2, 3

End Sub

This code will definately need to be customized. This method is a pain, so I suggest the first if at all possible.

HTH,