I want to keep Report look and fell in Excel.

[columbo1977]

Hi All
I have the following code in my Datbase for emailing the access report.


DoCmd.SendObject acSendReport, , acFormatRTF, _
"email@domain.co.uk", , , _
"Test Email", "test", False, False


If I export to RTF the report looks just as it does in Access which is what I want but my users would prefer the infromation in Excel as they can then fill in the blanks. Problem is when I export into excel I lose the look and feel of it and when opened in Excel the Title from the top of the report is gone, any ideas how I can get it to stay?
Thanks
Graham

[geekgirlau]

The short answer is it's never going to look identical to your report.

There are lots of different ways you can handle this, so I think you need to do some research and work out which method is going to give you the closest result to what you want.

If you want specific formatting, I'd suggest that you create a template in Excel with a link to the query or table that acts as the data source for your report. Then all your code has to do is launch the template and refresh the data. You should be able to find lots of code examples on how to do this.

[columbo1977]

What I have decided to do is output the excel file wiht this :


DoCmd.OutputTo acOutputReport, "rptNotAllocated", acFormatXLS, sTemp & "\" & Fname & ".xls", False

then open the excel file :


Set appexcel = CreateObject("Excel.Application")
appexcel.workbooks.Open sTemp & "\" & Fname & ".xls"
appexcel.Visible = True

then I need to be able to format the excel sheet, which is what I am having trouble with now, if you can help?

This is what I am working on?



'With appexcel.Worksheets("rptNotAllocated").Range("A1:I1")

'.ColumnWidth = 15
'.Font.ColorIndex = Black
'.Font.Bold = True
'.Font.Name = "Ariel"
'.Selection.Merge

'.HorizontalAlignment = xlCenter
'.VerticleAlignment = xlCenter
'End With


Can you help??

Graham

[geekgirlau]

In the last section of code, you need to refer to [Application].[Workbook].[Sheet].[Range]. This will probably be something like

With appexcel.Workbooks("rptNotAllocated").Sheet("MySheetName").Range("A1:I1")

The rest of that code should be ok, except that Font.ColorIndex is looking for a number, and the correct spelling of the font is "Arial". I would recommend that you just select some cells in Excel and record a macro where you make those changes. This will let you check that the syntax is correct.