locked
Excel Automatic Update Management between Workbook RRS feed

  • Question

  • Master!. i know this is hard to understand what I'm trying to do but I will 
    appreciate your taught and help to solve this. My humble pleasure to you all. 
    
    I have a large two workbook  (destination.xls and source.xls), that track 
    training of employee. The source.xls is link to the database such a way that 
    whenever i open, it  pull Automatic update from the database. Manager has 
    list of employee under him that have undergone the training and these names 
    sometimes occur more than one time because they have two or more training.
     I am trying to set up way that after update pulled by source.xls from the 
    database should Automatically copies to Destination.xls on a particular rows 
    and also only pick one name at a time if they appear more than one time"maybe 
    pick the first occured of each name" and cordinate the name by Manager.   For 
    example   
    
    "SOURCE.XLS"
               A                    B             C                  D
    A1    EMPLOYEE                ID        COURSE NAME           MANAGER
           Kim Bell              002       Sales Training         Brian
           Kim Bell              002       Safety Training        Brian
           Lee Paul              003       Light Training          Mark
           Lee Paul              003       Sales Training         Mark.
    "DESTINATION.XLS"
    
                B                  C            D                          
             
    B11   MANAGER            EMPLOYEE           ID
            BRIAN            Kim Bell           002 
            MARK             Lee Paul           003
    
    I mean the names should appear on the destination.xls automatically 
    according to thier MANAGER and each name should appear once on the 
    destination.xls "sheet1" even if they appeared 10times in the source.xls.  
    The COURSE NAME ROW is neccesary and it can be scrap out if it will prevent 
    us to achieve what we want.    
    
    Please your taught and help will be much more welcome and appreciate your 
    time. Thank you and look forward to  hear from you.
    
    Wednesday, July 1, 2009 10:09 PM

Answers

  •  

    Hi,

     

    You can copy the distinct records to excel file by Simple .Net data access code and Excel Automation. First you need to open the source file using OLEDBConnection and get the distinct records in a DataTable object as follows,

     

                string strExcelPath = @"D:\Priyank\Practice\C#\WindowsApplication1\WindowsApplication1\bin\Debug\Book1.xlsx";
               string strConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
              
               DataTable
    dt = new DataTable();
               OleDbConnection objConn = new OleDbConnection(strConnectionString);
             
               objConn.Open();
               
              OleDbDataAdapter
    objDataAdapter = new OleDbDataAdapter("Select Distinct Employee, Manager from [Sheet1$]", objConn);
             
              objDataAdapter.Fill(dt);


    Here,  "dt" would contain only the distinct data of Employee and Managers. Then, you would need to read the data from the DataTable Object "dt" and use the Excel Object Model for creating destination excel file.

     

    Hope this helps!

     

    Thanks,

    Priyank

    Friday, July 24, 2009 2:26 AM
    Moderator

All replies

  • Hi,

     

    Thank you for contacting Microsoft Innovate on Office Advisory Services.

    I have started working on this issue and will get back to you as soon as possible.

     

    Regards,

    Chaitanya

    Wednesday, July 8, 2009 5:05 PM
    Moderator
  •  

    Hi,

     

    You can copy the distinct records to excel file by Simple .Net data access code and Excel Automation. First you need to open the source file using OLEDBConnection and get the distinct records in a DataTable object as follows,

     

                string strExcelPath = @"D:\Priyank\Practice\C#\WindowsApplication1\WindowsApplication1\bin\Debug\Book1.xlsx";
               string strConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strExcelPath + ";Extended Properties='Excel 12.0 Xml;HDR=YES';";
              
               DataTable
    dt = new DataTable();
               OleDbConnection objConn = new OleDbConnection(strConnectionString);
             
               objConn.Open();
               
              OleDbDataAdapter
    objDataAdapter = new OleDbDataAdapter("Select Distinct Employee, Manager from [Sheet1$]", objConn);
             
              objDataAdapter.Fill(dt);


    Here,  "dt" would contain only the distinct data of Employee and Managers. Then, you would need to read the data from the DataTable Object "dt" and use the Excel Object Model for creating destination excel file.

     

    Hope this helps!

     

    Thanks,

    Priyank

    Friday, July 24, 2009 2:26 AM
    Moderator