Do you know of any resources/solutions to enhance the List’s “Export to Spreadsheet” function so that, once the data is opened in Excel, something would trigger VBA Macro(s) to start formatting the spreadsheet.
“Export to Spreadsheet” merely opens the Book1 standard spreadsheet, absent of any macros, etc. Might some code embedded in a List column be able to trigger the macros?
Marc is correct, there's no way to hook into the existing export to spreadsheet functionality.
You can however build your own. It's a big task, but doable. You would have to create your own function in code (as an event handler) to take the contents of a list and pump it into a spreadsheet. That spreadsheet can be your own making, including any VBA script or macros.
Then you would have to craft some ribbon UI modifications to a) hide the existing Export to Spreadsheet and b) surface your own version.
Lots of work and things to watch out for like name collisions in the menu items you create, but all doable. The question is it worth the effort? For something that can be as big and complicated as this I go back to what you're trying to do with the data and come up with a different solution to the problem. Perhaps a custom view or event handler can do what you're trying to accomplish.
I suggested to Charlie that he post the question here after he asked me about it. I did a blog post with my answer. Just to tie it in here: http://sympmarc.com/2011/05/27/can-you-enhance-the-export-to-spreadsheet-action-for-sharepoint-lists/
Bottom line: I don't know of a way to accomplish this, and I don't think it's possible.
EDIT: Not to bring commercial products into this (but to do so, anyway) You can do this pretty easily with OfficeWriter ( ). I've seen these guys' stuff in action, and it's slick.
I know this is old, but there is a slightly easier way as long as your ok with all your users opening one file....
