none
Implementign a monitoring "app" in Excel RRS feed

  • Question

  • Hello,

    I need to implement a simple monitoring app in Excel. It is for monitoring a small team of around 15 people. The leader has a "central" excel file in which he can browse the progress of each team member and the team as a whole, while each team member has their own Excel doc in which they report their personal progress and it gets "synced" to the leader's document.

    I had this implemented as a PHP web app, but I need to implement it using Excel, and I don't have much experience with Microsoft products. What I really have no idea about is how to sync everything. Is there a simple Microsoft server that has an API that allows this?

    Appreciate any suggestions,

    Thanks,

    SZ

    Thursday, June 21, 2012 9:10 PM

Answers

  • I think on the client computers an AddIn would be good for your task. This is the same as any application in that you can use whatever APIs you want to connect to the server (boss' computer), but has three main advantages over a standalone application:

    1) It's 'in process'. This means, for example, that you can hook onto save events, and make Excel wait before saving while you do whatever you want in your code. 'In process' automation is slightly faster, too.

    2) The AddIn will start whenever Excel starts.

    3) You can customise the Ribbon (assuming you're using Office 2007 and later). Maybe you want a checkbox there which, when checked, will mean Excel sends data to your boss' PC (which presumably you don't want to do for every spreadsheet).

    C# and VB are basically as easy as one another to use, and can be used in the same situations as far as Office solutions go. If you have a paid up version of Visual Studio ('Professional' or above) then you can use Visual Studio Tools For Office (VSTO) which makes things a bit easier.

    If you're not using VSTO, this walkthrough/article is very helpful (example code in C#).

    If you are using VSTO, I think this walkthrough is the best place to start (example code in C# and VB).

    It's up to you how to send information to the server. Like I said you can run whatever APIs you want from inside the AddIn, so there are hundreds of possibilities. Or, you can have the server application connect via the AddIn's COM interface. What method you choose for server communication will dictate the kind of server application you need to write. If you want more input on this, I advise you start a new thread, because I'm not going to be much help. Currently I'm using Winsock for that exact purpose, but that's C++, and so far none of my VSTO projects have needed to do this.

    EDIT: I should say, there are Application Level AddIns and Workbook Level AddIns. The latter will only be started when a particular workbook is opened. I've linked to an Application Level AddIn walkthrough, because unless you're going to go crazy with customizing the UI it's probably not going to be too invasive to have it load with every instance of Excel (indeed, you can not customise the UI at all, so the user basically wouldn't know it was even running).


    Sunday, June 24, 2012 7:42 AM

All replies

  • I don't think there's any quick way to do that. Unless you use the Excel web app, which works in browser, and allows collaborative editing to a certain extent (it's not quite 'live' updates, but it will sync every so often).

    The 'ideal' solution for you would be to use Automation/Interop, but it's going to be time consuming. Also, officially, Microsoft don't recommend automation for server side processing. This is purportedly because prompts can appear which require a user to click 'Okay' on. But that's rare, and lots of people do it without a problem.

    Edit: What language are you thinking of using? You can really use anything, because Automation/Interop is based around COM/OLE which is language agnostic. I'm just wondering about examples to link to. C++ is generally more work than VB/C#, as with everything.

    • Edited by JosephFox Saturday, June 23, 2012 10:51 PM
    Saturday, June 23, 2012 10:45 PM
  • Thanks for your reply. I've never heard of Automation/Interop, but recently a friend recommended me using Access and Sharepoint to do this. I guess that I would work in either VB or C# just because it usually allows for faster development. I would really appreciate some examples if you could link me to them.

    Thanks,

    SZ


    • Edited by sazpaz Sunday, June 24, 2012 1:53 AM
    Sunday, June 24, 2012 1:06 AM
  • I think on the client computers an AddIn would be good for your task. This is the same as any application in that you can use whatever APIs you want to connect to the server (boss' computer), but has three main advantages over a standalone application:

    1) It's 'in process'. This means, for example, that you can hook onto save events, and make Excel wait before saving while you do whatever you want in your code. 'In process' automation is slightly faster, too.

    2) The AddIn will start whenever Excel starts.

    3) You can customise the Ribbon (assuming you're using Office 2007 and later). Maybe you want a checkbox there which, when checked, will mean Excel sends data to your boss' PC (which presumably you don't want to do for every spreadsheet).

    C# and VB are basically as easy as one another to use, and can be used in the same situations as far as Office solutions go. If you have a paid up version of Visual Studio ('Professional' or above) then you can use Visual Studio Tools For Office (VSTO) which makes things a bit easier.

    If you're not using VSTO, this walkthrough/article is very helpful (example code in C#).

    If you are using VSTO, I think this walkthrough is the best place to start (example code in C# and VB).

    It's up to you how to send information to the server. Like I said you can run whatever APIs you want from inside the AddIn, so there are hundreds of possibilities. Or, you can have the server application connect via the AddIn's COM interface. What method you choose for server communication will dictate the kind of server application you need to write. If you want more input on this, I advise you start a new thread, because I'm not going to be much help. Currently I'm using Winsock for that exact purpose, but that's C++, and so far none of my VSTO projects have needed to do this.

    EDIT: I should say, there are Application Level AddIns and Workbook Level AddIns. The latter will only be started when a particular workbook is opened. I've linked to an Application Level AddIn walkthrough, because unless you're going to go crazy with customizing the UI it's probably not going to be too invasive to have it load with every instance of Excel (indeed, you can not customise the UI at all, so the user basically wouldn't know it was even running).


    Sunday, June 24, 2012 7:42 AM