Formatting commands to Excel from Access

[DBEngine]

Periodically, I have to dump approximately 50 Excel spreadsheets from an Access 97 database, each by customer. For esthetics, I go into each one and format it before sending it as an email attachment to each customer.

To save a LOT of time, after the TransferSpreadsheet, I'd like to tell Excel to:

1. Bold Row 1
2. AutoFit the columns
3. Freeze panes at B2

Would someone point me in the right direction? Once I get this going, maybe the emailing of the files could be automated too.

TIA!

--DB

 

[jebry]

Hi!

You will need to the worksheets as an object in Access before you can format them. Make sure you have referenced the Excel 8.0 object library so you can do this. As for the actual formatting commands, I am not sure what they are, but I know how you can easily find out. Go into Excel and start a macro recording, do the tasks you want to automate and then end the macro. Now go look at the macros and select the one you just recorded and click on the edit button. This will take you to the VB window and the VB commands for your actions will be there. You can use these command in Access through the Excel object to automate the formatting.

hth
Jeff Bridgham

 

[DBEngine]

Thanks, Jeff. I spent the weekend reading about automation and think I can solve this now. I already had an Excel macro to do this and, with your guidance, have the formatting commands:

' bold row 1
Rows("1:1&quot.Select
Selection.Font.Bold = True
' autofit columns
Cells.Select
Selection.Columns.AutoFit
' freeze panes
Range("B2&quot.Select
ActiveWindow.FreezePanes = True

Seems pretty straightforward now, with one exception. I didn't want to make Excel visible to do this so I'll have to work around the "ActiveWindow" object.

Anyway, thanks again,

--DB

 

[jebry]

Hi!

Glad it helped, there is one last thing. It seems to me that I read somewhere that you can run an Excel macro from Access after opening the workbook. So, if you are exporting your information to an existing workbook, maybe you can just run the macro instead of coding it in Access. I think that the Excel object has a RunMacro method.

hth
Jeff Bridgham