none
Looking for Word data entry and extraction solution RRS feed

  • Question

    1.        I would like to use a Word form with content controls set up for easy user data entry. When complete, the user emails the form to their supervisor for approval.
    2.        The supervisor would then have a tool or solution to “click a button” that would extract only the data and store it in an  Excel worksheet or Access database.
    3.        The collected data from many form submitters could then be used in various ways, for example, to compile a monthly report.

     Can you give me links to explanations and/or code samples (if possible) for each portion of that process?

    Word 2010 (2013 not an option at this time)

    PC, Windows 7 or 8

    Wednesday, June 26, 2013 12:53 AM

Answers

  • Hello,

    Thanks for your participation.

    1.        I would like to use a Word form with content controls set up for easy user data entry. When complete, the user emails the form to their supervisor for approval.
    2.        The supervisor would then have a tool or solution to “click a button” that would extract only the data and store it in an  Excel worksheet or Access database.

    For both of these two requirement, you can create an application-level add-in to achieve your goal.

    For your reference:

    Walkthrough: Creating Your First Application-Level Add-in for Excel

    I don't quite get what the user data entry is like. But you can add multiple kinds of controls in the ribbon by Ribbon Designer.

    For your reference:

    Ribbon Overview

    How to: Get Started Customizing the Ribbon

    Walkthrough: Creating a Custom Tab by Using the Ribbon Designer

    For example, you can add a button in the ribbon, and add these code to its click event handler to send the active workbook to your supervisor:

    using Outlook = Microsoft.Office.Interop.Outlook;
    using System.Runtime.InteropServices;
    
    ......
    
    string fileName = Globals.ThisAddIn.Application.ActiveWorkbook.FullName;
    
    Outlook.Application myOLApp;
    Outlook.MailItem mi;
    try
    {
        myOLApp = Marshal.GetActiveObject("Outlook.Application") as Outlook.Application;
    }
    catch
    {
        myOLApp = new Outlook.Application();
    }
    
    mi = (Outlook.MailItem)myOLApp.CreateItem(Outlook.OlItemType.olMailItem);
    mi.Attachments.Add(fileName, Outlook.OlAttachmentType.olByValue, 1, fileName);
    mi.Subject = fileName;
    mi.Recipients.Add("mysupervisor@hotmail.com");
    ((Outlook._MailItem)mi).Send();

    (This code snippet has used Outlook objects so you need to add reference to Microsoft.Office.Interop.Outlook)

    For your supervisor, you can also add a button for him/her to "extract the data".

    To reference another workbook:

    using Excel = Microsoft.Office.Interop.Excel;
    
    ......
    
    Excel.Workbook Wb = Globals.ThisAddIn.Application.Workbooks.Open("D:\\MyWB.xlsx");
    Excel.Workbook Ws1 = Wb.Worksheets["Sheet1"];

    I hope those information can help you.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 27, 2013 12:44 PM
    Moderator
  • Hi Texas Jewal

    It would probably be best to link the content controls to a Custom XML Part inside the document so that the content can be easily extracted.

    Data in a Custom XML Part can be easily accessed directly from the closed document by leveraging the Word Open XML file format, rather than needing to open and process it in Word. This means you can use standard XML tools to "convert" it to a database format, such as Access (or Excel).


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 27, 2013 2:27 PM
    Moderator

All replies

  • Hello,

    Thanks for your participation.

    1.        I would like to use a Word form with content controls set up for easy user data entry. When complete, the user emails the form to their supervisor for approval.
    2.        The supervisor would then have a tool or solution to “click a button” that would extract only the data and store it in an  Excel worksheet or Access database.

    For both of these two requirement, you can create an application-level add-in to achieve your goal.

    For your reference:

    Walkthrough: Creating Your First Application-Level Add-in for Excel

    I don't quite get what the user data entry is like. But you can add multiple kinds of controls in the ribbon by Ribbon Designer.

    For your reference:

    Ribbon Overview

    How to: Get Started Customizing the Ribbon

    Walkthrough: Creating a Custom Tab by Using the Ribbon Designer

    For example, you can add a button in the ribbon, and add these code to its click event handler to send the active workbook to your supervisor:

    using Outlook = Microsoft.Office.Interop.Outlook;
    using System.Runtime.InteropServices;
    
    ......
    
    string fileName = Globals.ThisAddIn.Application.ActiveWorkbook.FullName;
    
    Outlook.Application myOLApp;
    Outlook.MailItem mi;
    try
    {
        myOLApp = Marshal.GetActiveObject("Outlook.Application") as Outlook.Application;
    }
    catch
    {
        myOLApp = new Outlook.Application();
    }
    
    mi = (Outlook.MailItem)myOLApp.CreateItem(Outlook.OlItemType.olMailItem);
    mi.Attachments.Add(fileName, Outlook.OlAttachmentType.olByValue, 1, fileName);
    mi.Subject = fileName;
    mi.Recipients.Add("mysupervisor@hotmail.com");
    ((Outlook._MailItem)mi).Send();

    (This code snippet has used Outlook objects so you need to add reference to Microsoft.Office.Interop.Outlook)

    For your supervisor, you can also add a button for him/her to "extract the data".

    To reference another workbook:

    using Excel = Microsoft.Office.Interop.Excel;
    
    ......
    
    Excel.Workbook Wb = Globals.ThisAddIn.Application.Workbooks.Open("D:\\MyWB.xlsx");
    Excel.Workbook Ws1 = Wb.Worksheets["Sheet1"];

    I hope those information can help you.

    Regards,


    Damon Zheng
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, June 27, 2013 12:44 PM
    Moderator
  • Hi Texas Jewal

    It would probably be best to link the content controls to a Custom XML Part inside the document so that the content can be easily extracted.

    Data in a Custom XML Part can be easily accessed directly from the closed document by leveraging the Word Open XML file format, rather than needing to open and process it in Word. This means you can use standard XML tools to "convert" it to a database format, such as Access (or Excel).


    Cindy Meister, VSTO/Word MVP, my blog

    Thursday, June 27, 2013 2:27 PM
    Moderator