C# & Excel

fletch

I am trying to write a program that will go through each sheet in an excel workbook. I do not know beforehand how many sheets will be in the workbook but what I do know it that they will be labelled Sheet1, Sheet2, Sheet3 etc...
I am writing my code in C#....any ideas?
Basically I need a while loop that will go through the workbook until there is no more sheets left.
I have tried to explain my problem as clearly as possible. Need anymore info, then just post....
Thanks in advance guys

fletch

bump

ressem

What part in particular is troubling you?

Sample below opens Excel sheet, steps through each worksheet in the workbook and stores the entry in cell 1,1.
You'd have to add exception handling of course.

			Excel.Application  app= new Excel.ApplicationClass();
		
			String strTemp;

			Excel.Workbook wb= app.Workbooks.Open(@"d:\csharp.xls",
				Type.Missing, Type.Missing, Type.Missing, Type.Missing,
				Type.Missing, Type.Missing, Type.Missing, Type.Missing,
				Type.Missing, Type.Missing, Type.Missing, Type.Missing
				);

			foreach(Excel.Worksheet a in wb.Worksheets )
			{
				strTemp = ((Excel.Range)a.Cells[1,1]).Value.ToString();

			}

fletch

ressem wrote:

What part in particular is troubling you?

I just don't know what the code is to say, "while there is no more sheets left, move to the next sheet"

Peace

ressem seems to have nailed it for you? it doesn't HAVE to be a while loop as ressem pointed out.

fletch

Peace wrote:

ressem seems to have nailed it for you? it doesn't HAVE to be a while loop as ressem pointed out.

When I enter the code

Excel.Application  app= new Excel.ApplicationClass();

I get an error message saying C:\.....cation3\Form1.cs(368): The type or namespace name 'Excel' could not be found (are you missing a using directive or an assembly reference?)

I have added a reference to the Excel object (In Visual Studio.net Project - Add Reference - COM - Microsoft Excel 11.0 Object Library
???

scojones

This should be moved to the programming board.

ressem

Yep it should be in programming...

Yeah, code provided was for Excel XP COM reference.

For Office 2003, are you using the MS Office System Project Wizard to start the project? Should show you wat you should be adding?

You require
using Excel = Microsoft.Office.Interop.Excel;

Also the Workbook.Open requires more arguments and you have to use value2 instead of value.

			Excel.Application  app= new Excel.ApplicationClass();
		
			String strTemp;

			Excel.Workbook wb= app.Workbooks.Open(@"d:\csharp.xls",
				Type.Missing, Type.Missing, Type.Missing, Type.Missing,
				Type.Missing, Type.Missing, Type.Missing, Type.Missing,
				Type.Missing, Type.Missing, Type.Missing, Type.Missing, 
				Type.Missing, Type.Missing
				);

			foreach(Excel.Worksheet a in wb.Worksheets )
			{
				strTemp = ((Excel.Range)a.Cells[1,1]).Value2.ToString();

			}

fletch

That's brilliant ressem, got it working!!! Thanks for your help!

fletch

How do I modify this line of code

foreach(Excel.Worksheet a in wb.Worksheets )

to say for each worksheet that contains information?
i.e. only process those sheets that contain information

Thanks again

Peace

How will you know if there is information in the sheet or not?

ressem

option 1: slap the fool creating the blank sheets.
option 2: look up the object browser in visual studio, examine the methods for something appropriate.
i.e browse microsoft.office.interop.excel, under worksheet you might find
UsedRange or something similar that's appropriate.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/modcore/html/deovrusingcurrentregionusedrangeproperties.asp

If you want to ask further qns can you do so in the programming thread though, before we're hit with the wrath of Midnight etc.