none
How do I make that my code works not only in excel template, it has to work in excel workbook too RRS feed

  • Question

  • Hello people,

    I have a problem with my excel project, I have to develop an excelTemplate application, whit code, my problem is that it does not work when I save my template in standar excel 2007 or 2010 file (.xlt .xltx)

    Hope you understand

    Greetings

    Tuesday, August 16, 2011 1:54 AM

Answers

  • Hi Aldenhg2011,

    Excel add-ins don't necessarily affect all Excel documents. I like the way add-ins can integrate with Excel and provide very specific functionality that can be enabled only when it's needed...

    For your template, a quick-and-dirty solution would be to create an Excel add-in that is able to recognize a workbook that's an invoice template workbook - there must be something consistent and typical of that specific template somewhere on the workbook - think of sheet names, specific content on specific sheets, column headers, all ways of validating that the workbook you're looking at is effectively an invoice template (edit: preferably user-proof!).

    Of course if you validate your template this way (say your validation criteria is "there's a sheet called 'Summary'"), it's possible to trick the macro into believing it's dealing with an invoice by creating a workbook that has a sheet called 'Summary', but like I said it's a quick-and-dirty solution. If you have a database table that's centralizing your invoice data, you can probably ask your IT department to add a nullable (to preserve history) varchar(max) field called 'xlSourceFile' (to store Workbook.FullFileName [perhaps comma-separated list of] values) and provide you with stored procedures to pull a list of valid Excel source files that you can load in a few seconds at start-up - this way you avoid hitting the database every time a user activates a worksheet and Excel / your add-in "knows" exactly what files on your network are to be considered as "valid" invoice template workbooks. Just make sure to catch and handle exceptions to avoid any runtime errors at application start-up.

    Whichever validation technique you choose, what you need is a method like "bool IsValidInvoiceTemplate(Workbook wb)", then you need a handler to listen to "Workbook_Activate" events, where you only enable the invoice-related functionality (like, a Ribbon menu or button) if IsValidIncoiceTemplate returned true for the workbook you received in the event handler. So yes, the handler runs whatever workbook gets activated, but the specific functionality you want for specific workbook types is only enabled when the active workbook is valid.

    Best regards,


    Thursday, August 18, 2011 4:21 AM
  • Hi Aldenhg2011,

    The trick is to register and listen to a couple key events that the Excel Application raises:

    • Workbook_Open: use this event to validate the format/filename of the active workbook and enable/disable specific functionality
    • Workbook_Activate: use this event to validate the format/filename of the active workbook and enable/disable specific functionality
    • Workbook_BeforeSave: use this event to trigger reading and exporting data from the active workbook

    What kind of tutorial would help you? There's a lot to cover here - Ribbon UI, forms UI, database operations, reading to and from worksheets, ...

    Do you code in VB.NET or C#? I'm not sure how LINQ works with VB (or if it works at all), but I like using Linq to SQL for database operations; in the end which technology you choose isn't really important, it all comes down to SELECT, INSERT, UPDATE and DELETE.

    For my own needs I had to automate data entries on an inventory/sales/orders planning & forecasting worksheets (all workbooks were built off the same template, so they all looked alike - I know sales figures go under column N and coming orders under column P, for example). So I created a class that represented one of these workbooks: in that class, I only created properties that uniquely identify the workbook - there was one of such workbooks for each style under a given division, so I had a "Style" and "Division" property, and a "SourceFile" property to hold the workbook's filename; then I added an "ID" property so I could find the record I needed with just one number. The reason I could use the full path and filename to identify a workbook is because the users worked off a network share so the files were always under the same folder and I could assume the file would not be valid if it weren't under that folder.

    Then I created another class to represent the most atomic unit of data that I could find on any given valid worksheet: a single week of data (the worksheets would each hold 35 of them). So there I had properties like "Week", "Sales", "Inventory", "Orders", etc. I added a "WorkbookID" property to tie the data to the appropriate workbook definition.

    On the database side, I created two tables with columns matching the properties I had created for the two classes, and created the appropriate foreign keys to enforce the relationship and data integrity. So by reading the first table I could know where each and every single workbook is located on the network, and by reading the second table I could pull the associated sales, inventory and orders data. I created a stored procedure that returned a list of all filenames - that procedure runs when my add-in starts, and the add-in stores the valid filenames in a IEnumerable<String> that's queried everytime a workbook or worksheet gets activated. I also created stored procedures to insert data into both tables, so users could easily define new valid workbooks and the add-in could write the worksheet data to the database.

    In later versions I created a view off the workbook data, and added a menu button to create pivot table reports. These reports eliminated the "roll-up" workbooks that would take forever to open and update in Excel (because of dozens of external links to other workbooks - opening that file was like opening 60 Excel files at once...).

    So basically the way to think about this is really in terms of data - not in terms of Excel ranges and cells: you need to create a data model that matches your Excel workbook template, and provide functionality for your users to manipulate this data. Again, the data technology you choose isn't important: you could persist your data on another Excel workbook and things might work just as well, it all depends on how much data you'll eventually store.

    Regards,

    Saturday, August 20, 2011 10:00 PM

All replies

  • Hi aldenhg2011,

     

    Thanks for posting in the MSDN Forum.

     

    According to your description, I would recommend you create an application-level project (create a VSTO add-in with specific Office version. In your issue, you need to create an Excel 2007 add-in to handle it).

     

    If you still have any questions, please feel free to let me know.

     

    Have a good day,

     

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, August 17, 2011 3:03 AM
    Moderator
  • Hello Tom_Xu and thanks for your answer.

    I think your recommendation is not what I need, because if I create and application-level project (Add-in) its behavior will affect all excel document in my PC for example, and that is not what I want.

    I explain you more carefully.

    I am trying to develop an application use for make invoice in my work, and I pretend when anybody opens the invoice's template, ti has to do some action and follow some instruction, for example when I press the save button, before save the document, I need to save a list of products show in listobject control including in my excel.

    This is only one example of what I want to do.

    Hope that you understand. Greetings

    Aldenhg2011

    Wednesday, August 17, 2011 4:52 AM
  • Hi Aldenhg2011,

    Excel add-ins don't necessarily affect all Excel documents. I like the way add-ins can integrate with Excel and provide very specific functionality that can be enabled only when it's needed...

    For your template, a quick-and-dirty solution would be to create an Excel add-in that is able to recognize a workbook that's an invoice template workbook - there must be something consistent and typical of that specific template somewhere on the workbook - think of sheet names, specific content on specific sheets, column headers, all ways of validating that the workbook you're looking at is effectively an invoice template (edit: preferably user-proof!).

    Of course if you validate your template this way (say your validation criteria is "there's a sheet called 'Summary'"), it's possible to trick the macro into believing it's dealing with an invoice by creating a workbook that has a sheet called 'Summary', but like I said it's a quick-and-dirty solution. If you have a database table that's centralizing your invoice data, you can probably ask your IT department to add a nullable (to preserve history) varchar(max) field called 'xlSourceFile' (to store Workbook.FullFileName [perhaps comma-separated list of] values) and provide you with stored procedures to pull a list of valid Excel source files that you can load in a few seconds at start-up - this way you avoid hitting the database every time a user activates a worksheet and Excel / your add-in "knows" exactly what files on your network are to be considered as "valid" invoice template workbooks. Just make sure to catch and handle exceptions to avoid any runtime errors at application start-up.

    Whichever validation technique you choose, what you need is a method like "bool IsValidInvoiceTemplate(Workbook wb)", then you need a handler to listen to "Workbook_Activate" events, where you only enable the invoice-related functionality (like, a Ribbon menu or button) if IsValidIncoiceTemplate returned true for the workbook you received in the event handler. So yes, the handler runs whatever workbook gets activated, but the specific functionality you want for specific workbook types is only enabled when the active workbook is valid.

    Best regards,


    Thursday, August 18, 2011 4:21 AM
  • *extending the database-involving scenario...

    Eventually an Excel add-in that works like this, with an SQL Server database in the back, can turn into a monster. Well in VBA that is. Thanks to VSTO, complex Excel solutions can now be developed, but more importantly version-controlled, maintained and supported with professional development tools. In the invoices scenario, your add-in could easily feature a form/window where users could create new invoices, edit existing ones, perhaps click on a button an send this information in an email via a connected Outlook account - who knows! In the real world this kind of add-in quickly becomes the best thing since sliced bread, because it leverages the Excel object model and external data - your add-in brings Excel up a notch in your users' hearts, because it makes their lives so much easier.

    If you come from the VBA world and have never dealt with database programming, you should read up on databases, T-SQL, and LINQ, and learn about the Entity Framework - trust me it's fascinating and if you like coding macros you'll love this! Other than that you'll have to resort to running stored procedures or web services to retrieve your data.

    From a business user's point of view, the requirements are met and the application delivers. Users can spend less time manipulating data and more time analyzing it.

    From a developer's point of view, this add-in is entering an area where the very usage of Excel becomes questionable: rather than developing a user interface for a data application that's embedded in Excel, the developer's typical intuition would be to opt for a Web application that accomplishes just that, perhaps much better than Excel would (ASP.net MVC seems made for that).

    On the other hand, users have all the data they need (why not have the add-in create pivot table reports off the table data?) at the tip of their fingers, in a format that they can easily copy, paste and format as they see fit. And well, you're connected to a database server, you can implement this logic and do pretty much anything in Excel - forecast sales, print dashboard reports, plan invoices, pivot data from cubes in your data warehouse... and seamlessly integrate into the Ribbon to provide all this functionality to your users. The Excel user in me calls that beautiful.

     

    (perhaps this would belong in some kind of "tips & how-to's" section...)

    Thursday, August 18, 2011 6:15 AM
  • Hello xlWarlock thanks for your great answer, I will study it carefully.

    Nevertheless, could you send me some information or tutorials about this?

    Greetings

    Aldenhg2011

    aldenhg2004@yahoo.es

    Saturday, August 20, 2011 3:45 AM
  • Hi Aldenhg2011,

    The trick is to register and listen to a couple key events that the Excel Application raises:

    • Workbook_Open: use this event to validate the format/filename of the active workbook and enable/disable specific functionality
    • Workbook_Activate: use this event to validate the format/filename of the active workbook and enable/disable specific functionality
    • Workbook_BeforeSave: use this event to trigger reading and exporting data from the active workbook

    What kind of tutorial would help you? There's a lot to cover here - Ribbon UI, forms UI, database operations, reading to and from worksheets, ...

    Do you code in VB.NET or C#? I'm not sure how LINQ works with VB (or if it works at all), but I like using Linq to SQL for database operations; in the end which technology you choose isn't really important, it all comes down to SELECT, INSERT, UPDATE and DELETE.

    For my own needs I had to automate data entries on an inventory/sales/orders planning & forecasting worksheets (all workbooks were built off the same template, so they all looked alike - I know sales figures go under column N and coming orders under column P, for example). So I created a class that represented one of these workbooks: in that class, I only created properties that uniquely identify the workbook - there was one of such workbooks for each style under a given division, so I had a "Style" and "Division" property, and a "SourceFile" property to hold the workbook's filename; then I added an "ID" property so I could find the record I needed with just one number. The reason I could use the full path and filename to identify a workbook is because the users worked off a network share so the files were always under the same folder and I could assume the file would not be valid if it weren't under that folder.

    Then I created another class to represent the most atomic unit of data that I could find on any given valid worksheet: a single week of data (the worksheets would each hold 35 of them). So there I had properties like "Week", "Sales", "Inventory", "Orders", etc. I added a "WorkbookID" property to tie the data to the appropriate workbook definition.

    On the database side, I created two tables with columns matching the properties I had created for the two classes, and created the appropriate foreign keys to enforce the relationship and data integrity. So by reading the first table I could know where each and every single workbook is located on the network, and by reading the second table I could pull the associated sales, inventory and orders data. I created a stored procedure that returned a list of all filenames - that procedure runs when my add-in starts, and the add-in stores the valid filenames in a IEnumerable<String> that's queried everytime a workbook or worksheet gets activated. I also created stored procedures to insert data into both tables, so users could easily define new valid workbooks and the add-in could write the worksheet data to the database.

    In later versions I created a view off the workbook data, and added a menu button to create pivot table reports. These reports eliminated the "roll-up" workbooks that would take forever to open and update in Excel (because of dozens of external links to other workbooks - opening that file was like opening 60 Excel files at once...).

    So basically the way to think about this is really in terms of data - not in terms of Excel ranges and cells: you need to create a data model that matches your Excel workbook template, and provide functionality for your users to manipulate this data. Again, the data technology you choose isn't important: you could persist your data on another Excel workbook and things might work just as well, it all depends on how much data you'll eventually store.

    Regards,

    Saturday, August 20, 2011 10:00 PM