Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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?

share|improve this question
add comment

3 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
    
Thanks Bill. Good advice- –  ChasEpes May 31 '11 at 16:40
add comment

I know this is old, but there is a slightly easier way as long as your ok with all your users opening one file....

  1. Export to excel
  2. Save that file for your users to access
  3. Refresh the list every time the Spreadsheet is opened using macros
  4. After refreshing on open, copy the list to another sheet and format it the way you would like
  5. you can even save this excel file on sharepoint and have it refresh/copy/format on open...
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.