Automating Excel through C#

[mikecole79]

I have been having nothing but trouble trying to get C# to read excel. I haven't even gotten to the writing part, but I imagine it's going to be a challenge, too. FYI, I'm pretty new at this, and don't have a ton of experience, so I'm sure I'm missing something that's obvious to someone who's been around a bit.

Basically, what I'm trying to do is write a simple program that will read information from 2 different workbooks, reading each of 4 columns from one book into a different list<string>, and reading the 2 columns from the other book into a dictionary<string, string>. Then I plan to spit all that information back out again, but it's going to be seperated into multiple sheets in 3 different books in a different order so that it fits everyones needs. Currently, the company I work for uses spreadsheets for just about everything, and when you're ordering a series of 1500 new phone numbers, and it's all on one spreadsheet, but needs to be seperated out as described above to go to different vendors, it can take all freakin day to do one. I have been using a Marcro recorder to do it since my boss assigned it to me, but that doesn't make me happy, and will still take 2 hours to do. Hence I'm trying to write this program.

Below is a code example for what I have so far for part of a read method.

do
{
try
{
excelApp.ActiveCell.Text.ToString();
string Temp = excelApp.ActiveCell.Text.ToString();
LATA.Add(Temp);
excelApp.ActiveCell.FindNext(Temp);
rowIndex++;
}

catch (Exception e)
{
MessageBox.Show("Exception " + e.Message + " Stack Trace: " + e.StackTrace);
}

}
while (excelApp.Cells[rowIndex, colIndex] != null);

This particular method is supposed to circle through the information in the workbook, and if it is in a specific column (this is for column A), it reads the contents of the cell into LATA (which is list<string>). It doesn't work, and I have tried many, many different variations to try to get it. The closest I've come is with the line:

excelApp.ActiveCell.Text.ToString();

which will correctly read the information, but I can't seem to figure out the right syntax to get it to move to the next cell in the column, or even just the next cell period.

Any ideas or hints on what I can do to try to get this thing working? Any ideas at all, even if you think they're stupid or won't work, would be great. I've been arguing with this program for days, and have tried several different things, and so far the built in interop on VS2008 is the best solution I have (without spending several hundred dollars).

Thanks!

Mike

[memeloo]

are doing something like this? http://support.microsoft.com/kb/302084
use the get_Range method to get the right cell

[mikecole79]

are doing something like this? http://support.microsoft.com/kb/302084
use the get_Range method to get the right cell

That page has been the bane of my existence. Along with another very similar page. The problem is that the examples they give me on there are just not clicking in my head with what I'm trying to do. I believe I tried the get_Range method, but I will give it another try and see if maybe I had the wrong syntax. It's too the point where I'm so frustrated with the thing that I decided to actually take a BREAK to eat lunch. That's something that I don't do. I had to try to remember where the cafeteria was. lol

Thanks for the idea. I'll see where it gets me.

Mike