Create an Excel Spread with multiple worksheets

[hewstone999]

I have a button that when I click on it, I want it to create an Excel Spreadsheet with 5 worksheets.

Also how can I change the cell formats in Excel from Access? i.e. I have an export function in Access that exports a table into excel, however I want to change the format in Excel i.e. Make the Column headings bold.

Any ideas how I can do this using Access VBA?

 

[KenHigg]

Your best bet is going to be to do a little reading up on how to do these things in excel with vba first. Then in access you can execute the same commands. Here's a good link to start with:

http://www.access-programmers.co.uk/forums/showthread.php?t=139559

 

[chergh]

You can create what you want like this:

dim xlapp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

set xlapp = new excel.application

set wb = xlapp.workbooks.add

do until wb.worksheets.count = 5

   wb.worksheets.add

loop

To change a cell format so it's bold then it would be doen like this:

thisworkbook.worksheets("sheetName").range("A1:E1").font.bold = true

 

[KenHigg]

Or you can cut and paste chergh's code -