none
C# ForEach Loop

    Question

  • I want to use a forEach loop to cycle through all worksheets in an excel workbook...below is what I have tried

    public static Excel._Worksheet xlsSheet;

    public static Excel._Workbook xlsWB;

    foreach (xlsSheet in xlsWB)

    {

    //run the code here

    }

    The errors I get are:

    Type and identifier are both required in a foreach statement

    foreach statement can not operate on variables of type 'microsofft.office.interop.Excel._Workbook

    xlsSheet is a field bus is used like a type

    How can I use C# to cycle through each worksheet in a workbook

    Thursday, January 10, 2013 5:10 PM

Answers

  • What we are missing here is context.  We haven't seen enough of the code to determine exactly where qt is going awry.  That being said, it sounds like your original question has been answered, which was what was wrong with your for loop.

    My first suggestion would be that you close this question and open your new question.  Not staying on point with your original question makes it difficult for others to search for solutions within the forums.

    Either way, you are going to need to post a more complete listing of your source for us to determine what is going wrong.

    Secondarily, you should not be using a public declared qt in the assignment side of your foreach loop.  The variable that is being assigned to in a foreach loop should be local to the foreach loop.

    My suggestion for a code posting should be the entire contents of the foreach loop that iterates over the workbook sheets.


    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    • Marked as answer by IndigoMontoya Thursday, January 10, 2013 6:24 PM
    Thursday, January 10, 2013 6:19 PM

All replies

  • What kind of excel file you have? I mean what is the file`s extension?

    Depends on that how you then read the file.

    And what foreach you want? To loop columns, rows or both (1st rows, then in each row columns)?


    Mitja

    Thursday, January 10, 2013 5:21 PM
  • The first argument in a foreach loop is a variable to which the items will be assigned.  Additionally, a workbook is not a collection of sheets.

    Perhaps changing:

    public static Excel._Worksheet xlsSheet;
    
    public static Excel._Workbook xlsWB;
    
    foreach (xlsSheet in xlsWB)
    
    {
    
    //run the code here
    
    }
    

    to something along the lines of:

    public static Excel._Workbook xlsWB;
    
    foreach (Excel._Worksheet currentSheet in xlsWB.Sheets)
    
    {
    
    //run the code here
    
    }
    

    would work for you.  That is presuming that you are correctly assigning an individual workbook to xlsWB.

    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    Thursday, January 10, 2013 5:31 PM
  • What kind of excel file you have? I mean what is the file`s extension?

    Depends on that how you then read the file.

    And what foreach you want? To loop columns, rows or both (1st rows, then in each row columns)?


    Mitja

    They are .xls files.

    How I read the file?  I am using C# to open the workbook, and then using C# to refresh querytables?  I just want the querytables on all worksheets refreshed.

    I only want the foreach to cycle the worksheets, not the columns/rows/etc

    Thursday, January 10, 2013 5:38 PM
  • I changed my code to:

    foreach (Excel.QueryTable qt in xlsSheet.QueryTbales)

    and it hits the qt.EnableRefresh - true;

    I get a debug error of NullReferenceException was unhandled?  Did I set up the foreach loop incorrect?

    Thursday, January 10, 2013 5:44 PM
  • I changed my code to:

    foreach (Excel.QueryTable qt in xlsSheet.QueryTbales)

    and it hits the qt.EnableRefresh - true;

    I get a debug error of NullReferenceException was unhandled?  Did I set up the foreach loop incorrect?


    Other than QueryTables is misspelled in you in statement, I have no idea where qt.EnableRefresh - true, which I am assuming is an = is in the code or if xlsSheet has ever been assigned to.  Why did you switch from working with Sheets to QueryTables?

    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    Thursday, January 10, 2013 5:51 PM
  • Hi,

    a foreach loop has the following form

    foreach (var item in collection)
    {
    }
    so your foreach loop seems to be ok.

    I think your xlsSheet is not correct initialized or xls.QueryTables is null.

    Regards,

    Chris

    Thursday, January 10, 2013 5:53 PM
  • I changed my code to:

    foreach (Excel.QueryTable qt in xlsSheet.QueryTbales)

    and it hits the qt.EnableRefresh - true;

    I get a debug error of NullReferenceException was unhandled?  Did I set up the foreach loop incorrect?


    Other than QueryTables is misspelled in you in statement, I have no idea where qt.EnableRefresh - true, which I am assuming is an = is in the code or if xlsSheet has ever been assigned to.  Why did you switch from working with Sheets to QueryTables?

    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    Those were merely typo's on my part.

    I was testing to see if I could get the code working properly with a single sheet, then move onto all sheets.  I henceforth have it working on a single sheet, so the problem does lie with working on multiple sheets.

    Thursday, January 10, 2013 5:56 PM
  • Your initial foreach loop was incorrect, which is why I provided a potential solution in my original post.  Have you tried something like I suggested?

    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    Thursday, January 10, 2013 6:01 PM
  • Hi,

    a foreach loop has the following form

    foreach (var item in collection)
    {
    }
    so your foreach loop seems to be ok.

    I think your xlsSheet is not correct initialized or xls.QueryTables is null.

    Regards,

    Chris


    xls.QueryTables is not null....so I must think that xlsSheet is not initialized properly.  How would you recommend initializing this so it cycles all sheets in the book?
    Thursday, January 10, 2013 6:01 PM
  • Your initial foreach loop was incorrect, which is why I provided a potential solution in my original post.  Have you tried something like I suggested?

    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    Yes...(Apalogies, I thought I had replied to that post)....

    When the code is modified to be foreach (Excel._Worksheet currentSheet in xlsWB.Sheets);

    I then declare public static Excel.QueryTable qt;

    And it highlights qt my declare statement and says qt is never assigned to, and will always have its default value null?

    Thursday, January 10, 2013 6:09 PM
  • What we are missing here is context.  We haven't seen enough of the code to determine exactly where qt is going awry.  That being said, it sounds like your original question has been answered, which was what was wrong with your for loop.

    My first suggestion would be that you close this question and open your new question.  Not staying on point with your original question makes it difficult for others to search for solutions within the forums.

    Either way, you are going to need to post a more complete listing of your source for us to determine what is going wrong.

    Secondarily, you should not be using a public declared qt in the assignment side of your foreach loop.  The variable that is being assigned to in a foreach loop should be local to the foreach loop.

    My suggestion for a code posting should be the entire contents of the foreach loop that iterates over the workbook sheets.


    It would be greatly appreciated if you would mark any helpful entries as helpful and if the entry answers your question, please mark it with the Answer link.

    • Marked as answer by IndigoMontoya Thursday, January 10, 2013 6:24 PM
    Thursday, January 10, 2013 6:19 PM