Forum Discussion

nittynair's avatar
nittynair
Occasional Contributor
13 years ago

Using For loop with different sheets in an excel

Hi,



My application has two pages



page 1 : i need to add company details

Page 2 : employee details of the above company



I have put company data in sheet 1 and employee detail in sheet 2.

eg . row 1 of sheet 1 has company 1 details

      row 1 and 2 of sheet 2 have details of employees of the above company 



      row 2 of sheet 1 has company 2 details

      row 3 and 4 of sheet 2 have details of employees of the above company



therefore i need a For loop which checks if the company name is company 1 and inputs details of the employees ( row 1 and 2 of sheet 2) to my application so on..



Kindly explain how to implement the abive



thanks in advance

2 Replies

Sort By
  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    13 years ago
    I wouldn't use for loops.  I'd use nested while loops using DDT.ExcelDrivers.



    It would look something like this.  Please note, this is pseudocode.  Accuracy and runability is not guarenteed.  Supplied only to give an idea of the methodology.





    function DoThisThing();

    {

    var CompanyDDT = DDT.ExcelDriver("C:\\MyPath\\MyWorkbook.XLS", "CompanySheet", true)

    var EmployeeDDT

    while (!CompanyDDT.EOF) 

    {

        EnterCompanyData();

        EmployeeDDT = DDT.ExcelDriver("C:\\MyPath\\MyWorkbook.XLS", "EmployeeSheet", true)

        EmployeeDDT..ADOCommandObject.CommandText = "SELECT * from [EmployeeSheet$] where [CompanyID] = '" + CompanyDDT.Value("CompanyID" + "'";

        while (!EmployeeDDT.EOF)

        {

            EnterEmployeeData();

            EmployeeDDT.Next();

        }

        DDT.CloseDriver(EmployeeDDT.Name);

        CompanyDDT.Next();

    }

    DDT.CloseDriver(CompanyDDT.Name);

    }
  • irina_lukina's avatar
    irina_lukina
    Super Contributor
    13 years ago

    Hi,


    I agree with Robert that using the For Loop operation is not a good idea. I'd either use an approach suggested by Robert, or try working with Excel via COM. For more information, please refer to the Working With Excel Files via COM help topic.