Excel Automatic Update Management between Workbook

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.

Answer 1

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

Answer 2

 

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