Export an Access table to Excel AND Format the Excel file

[jools68]

I have some code that exports an Access table to an Excel workbook, and I would like to learn how to format the spreadsheet's appearence. Is the formatting something I can do within the export function? Or do I need a new piece of code to be called after the export function is called? If I need a separate function to be called after the export, can that function be performed by an Access Module? Or will it need to be contained in an Excel Macro? I'm not very familiar with VBA so please bear with me. Let's start with formatting the first row to be bold text. If I can accomplish this I should be able to figure out the rest.

Here is my export code:

Option Compare Database

Function Export_Table1()

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Table1", "P:\Lapses\Table1.xls", True, "Table1"
End Function

How can I format the first row of Table1.xls to be bold font (preferably inside an Access module)?

 

[lameid]

Record a macro to do what you want in Excel and check out this thread...

thread705-1548704

I'm not sure if you can read code at all or not. If you can, you might be able to figure it out from here.

 

[lameid]

Oops forgot something.... When you automate another application like is done in the target thread, you have to reference that application...

When you are in a module in access, on the tools menu select references. Find the reference that begins Microsoft Excel and check it. Now all your code in the MDB can use the Excel object model.

 

[jools68]

Cool, ya I can read code fairly well. This should get me going. Thanks for the help!