VSTO se sorting problem

MothDoc

Hi Ji

I am trying to do something similar but I am afraid I am getting nowhere. I am trying to build a function within excel that will allow one to stipulate the performance of calculations and other formulae based on the cell background colour. For instance I have a column of figures B2:B9 with the following values and cell colours
B2: 2 pink
B3: 3 white/no colour
B4: 0 pink
B5: 0 white/no colour
B6: 1 pink
B7: 8 white/no colour
B8: 2 pink
B9: 3 pink
It is desirable to: sum the pink’s, count the pink’s, countif the pink’s are greater than zero. It is also desirable to do the same on white/no colours.

It is greatly annoying that Visual Basic can’t spell. Is there anyway to correct the spelling of color to the correct colour.

ATB

John (a medic not a programmer)

Ji Zhou – MSFT

Hi John,

For it is another question, I will splite it into a separate one and we will discuss there:-)

Now I will make sure that I understand your question in a right way. You are just trying to get the sum of pinks(it's 8 in your example), count of pinks:5, and count of pinks which are greater than zero:4. Right

But I do not know what do you mean exactly when you say:"Visual Basic can't spell". Do you mean that it does not get the right RGB value of color you see in Excel's cell

If I have any misunderstanding about your issue, please feel free to let me know!

Thanks

Ji

MothDoc

Hi Ji

Thank you. Yes you are correct in assuming that I am trying to "to get the sum of pinks (it's 8 in your example), count of pinks:5, and count of pinks which are greater than zero:4"

The background to this is a friend of mine, who is a professional Lepidopterist, has recorded data in spreadsheets he has been asked to split it into absolute and estimated. I have suggested adding extra columns and
splitting the data between the columns. This he assures me may sound simple but leaves him with a massive task of rebuilding his whole data collecting system and he has colour coded things and would like to analyse data on the basis of the cell colour. I have tried a number of things including some code that sums on cell colour but it does not allow count if not zero as if counts all zeros not just pink ones. If you know of some Visual Basic code that I can embed into the spreadsheet it would solve the problem. I have historical medical data I have not reanalysed because of having to
do a lot of manual sorting and this coud ease the burden and make that data-mining worthwhile.

Regarding the problem of colour and color, this is seperate issue, don't trouble yourself with it as neither of us can solve that one.
It is an age long battle between British English and
American English.

I hope this makes sense now. Many thanks for your help.

John

Ji Zhou – MSFT

Hi John,

I write some C# codes which implement your object. And I think you can convert it to VB version easily

Code Snippet

private void Form1_Load(object sender, EventArgs e)

{

const int color1Index = 3;

const int color2Index = 5;

const int color3Index = 10;

int sumColor1 = 0, countColor1 = 0, countColor1GTZ = 0;

int sumColor2 = 0, countColor2 = 0, countColor2GTZ = 0;

int sumColor3 = 0, countColor3 = 0, countColor3GTZ = 0;

object missing = Type.Missing;

myApp = new Microsoft.Office.Interop.Excel.Application();

myApp.Visible = true;

Excel.Workbook myWB = myApp.Workbooks.Open(@"C:\test.xlsx", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

Excel.Worksheet myWS = myApp.ActiveSheet as Excel.Worksheet;

Excel.Range myRange = null;

for (int i = 1; i <= 10; i++)

{

myRange = myApp.Cells[i, 1] as Excel.Range;

if (Convert.ToInt32(myRange.Interior.ColorIndex) == color1Index)

{

int content = Convert.ToInt32(myRange.Text);

countColor1++;

sumColor1 += content;

if (content > 0)

{

countColor1GTZ++;

}

}

else if (Convert.ToInt32(myRange.Interior.ColorIndex) == color2Index)

{

int content = Convert.ToInt32(myRange.Text);

countColor2++;

sumColor2 += content;

if (content > 0)

{

countColor2GTZ++;

}

}

else if (Convert.ToInt32(myRange.Interior.ColorIndex) == color3Index)

{

int content = Convert.ToInt32(myRange.Text);

countColor3++;

sumColor3 += content;

if (content > 0)

{

countColor3GTZ++;

}

}

else

{

MessageBox.Show("There are still colors which are not dealt with");

}

}

MessageBox.Show(sumColor1.ToString()+ " " +countColor1.ToString() + " " + countColor1GTZ.ToString()+ "\n"

+ sumColor2.ToString() + " " + countColor2.ToString() + " " + countColor2GTZ.ToString() + "\n"

+ sumColor3.ToString() + " " + countColor3.ToString() + " " + countColor3GTZ.ToString() + "\n");

}

Hope this helps!

Thanks

Ji

MothDoc

Hi Ji

Gosh! Wow! Thanks I see you have gone to some trouble to write some special code in C#. I am afraid I only have the most basic idea of codes. Can I implement this directly into excel or do I have to convert it to VB If it needs to be converted you say that is easy, can I beg a bit of guidance on that one.

Many thanks

John

MothDoc

http://www.developerfusion.co.uk/utilities/convertcsharptovb.aspx

I am trying this to convert


John

MothDoc

Hi Ji

I converted the code to VB using above utility and got

Sub colour()
Private Sub Form1_Load(ByVal sender As Object, ByVal e As EventArgs)

Const color1Index As Integer = 3
Const color2Index As Integer = 5
Const color3Index As Integer = 10

Dim sumColor1 As Integer = 0, countColor1 As Integer = 0, countColor1GTZ As Integer = 0
Dim sumColor2 As Integer = 0, countColor2 As Integer = 0, countColor2GTZ As Integer = 0
Dim sumColor3 As Integer = 0, countColor3 As Integer = 0, countColor3GTZ As Integer = 0

Dim missing As Object = Type.Missing

myApp = New Microsoft.Office.Interop.Excel.Application()

myApp.Visible = True

Dim myWB As Excel.Workbook = myApp.Workbooks.Open("C:\test.xlsx", missing, missing, missing, missing, missing, _
missing, missing, missing, missing, missing, missing, _
missing, missing, missing)

Dim myWS As Excel.Worksheet = TryCast(myApp.ActiveSheet, Excel.Worksheet)
Dim myRange As Excel.Range = Nothing
For i As Integer = 1 To 10

myRange = TryCast(myApp.Cells(i, 1), Excel.Range)

If Convert.ToInt32(myRange.Interior.ColorIndex) = color1Index Then

Dim content As Integer = Convert.ToInt32(myRange.Text)
countColor1 += 1
sumColor1 += content


If content > 0 Then
countColor1GTZ += 1

End If
ElseIf Convert.ToInt32(myRange.Interior.ColorIndex) = color2Index Then

Dim content As Integer = Convert.ToInt32(myRange.Text)
countColor2 += 1
sumColor2 += content

If content > 0 Then

countColor2GTZ += 1
End If
ElseIf Convert.ToInt32(myRange.Interior.ColorIndex) = color3Index Then

Dim content As Integer = Convert.ToInt32(myRange.Text)
countColor3 += 1
sumColor3 += content

If content > 0 Then

countColor3GTZ += 1

End If
Else


MessageBox.Show ("There are still colors which are not dealt with")


End If
Next

MessageBox.Show (sumColor1.ToString() + " " + countColor1.ToString() + " " + countColor1GTZ.ToString() + "" & Chr(10) & "" + sumColor2.ToString() + " " + countColor2.ToString() + " " + countColor2GTZ.ToString() + "" & Chr(10) & "" + sumColor3.ToString() + " " + countColor3.ToString() + " " + countColor3GTZ.ToString() + "" & Chr(10) & "")


End Sub


I then copied this to the VB bit of my workbook and made it module one. I test ran it and it bitched at me. Even if it had run smoothly what commands/functions do I use to sum and count the colours on the spreadsheet

Sorry to be a pain.

ATB

John

Ji Zhou – MSFT

MothDoc wrote:

Hi Ji Gosh! Wow! Thanks I see you have gone to some trouble to write some special code in C#. I am afraid I only have the most basic idea of codes. Can I implement this directly into excel or do I have to convert it to VB If it needs to be converted you say that is easy, can I beg a bit of guidance on that one. Many thanks John

Hi John,

"Can I implement this directly into excel". The answer is yes if you have Visual Studio and can create a C# Windows project. The codes above manipulate excel via Office automation but not VBA. So when I say "convert it to VB version", in fact I mean write VBA codes by your own for this forum is targeted at VSTO issue. I am not very familiar with VBA. But I think whether via VBA, Office automation or VSTO, we all program on Office PIAs.

MothDoc

Hi Ji

IC. Well I am not that familiar with codes to be able to write my own, copy snipets is about my limit. I have downloaded a free version of Visual Studio and will try that approach. I just past in the code and save as a C# project How do I test Then what do I do to get it into Excel

I am not sure what you mean "Office automation or VSTO, we all program on Office PIAs"

Have a good weekend

John

Ji Zhou – MSFT

Hi John,

Thanks:-) Good weekend too!

Office automation means manipulate Office documents through other applications which can be Console applications or Windows applications. In my example, I use the C# window project.

You can create a C# windows project, and add Office PIAs(Excel) as reference, then past the codes in the the Form_Load method to run.

But my code is only an sample for I do not know about the excel file you are working on. I create an excel file in "C:\test.xlsx"(You can see this from my codes). In my test.xlsx, there are 10 rows in three kind of cell colors(red, green, blue, and these three colors are corresponding with three colorIndex value in my codes 3,5,10).

So if you want these codes work for your worksheet, you should modify them according to how much rows and cell colors in your worksheet

Thanks

Ji

MothDoc

Hi Ji

I think I better take this step by step. I still run Office 2K so can I achieve this in 2K I am going this as a favour and I think Peter has 2003 or XP so if I can't do it here I will have to do it for him on his machine. I have found this resource http://msdn2.microsoft.com/en-us/library/ms173077(VS.80).aspx. I need to down load a programme called Visual Studio or VS 2005 and then create new project and paste in your code and then link to an excel sheet Which in your example is text.xlsx I am used to excel sheets only having a three letter extention. Does this work in .xls documents .xlsx is a bit new to me.

I am determined to master this.

All the best

John

MothDoc

Hi Ji

I think I need to take this step by step by I am determined to master it. Can I achieve this in office 2K I currently run this but Peter who I am doing this for as a favour runs XP or 2003. Do I need to download a programme VS or VS 2005 I then create a new project, past in your code and link to the excel file in question

All the best

John

Ji Zhou – MSFT

Hi John,

I write these codes in Excel 2007. And I make sure it works on Excel 2003 for I have tested it just now, but am not sure about Excel 2000. You can download a Visual Studio C# 2005 Express Edition. It is free and competent for your work.

Steps you should do are as follows:

1. Create a C# windows application in C#2005 Express Edition

2. Add Excel pia as reference in your project. (In solution explorer, right click Reference, click Add Reference, navigate to Com tab, find Microsoft Excel 12.0 Object Library in the list, choose it and click Ok)

3. Double click the form in Design View. The editor of Form1.cs appears. Insert codes above into Form1_Load method

4. Import the Namespace of Office and Excel(using following codes at the begin of the file)
using Office = Microsoft.Office.Core;
using Word = Microsoft.Office.Interop.Word;

5. Add an Excel application variable at the begin of the Class Definition (In the prepost, I only copy codes in the Load method)

Excel.Application myApp = new Excel.Application();

6. Now, you can press F5 to do a test if a file named test.xlsx (test.xls is for Excel 2003 and before. Test.xlsx is for Excel 2007. You should modify the codes according to your Excel version) exists in "C:\". And in the worksheet, there should only be three kinds of colors of the cells.(They are Red,Green,Blue because the codes just use their colorIndex 3,5,10 to do a test)

Thanks

Ji

MothDoc

Hi Ji

Many thanks for that. I have got as far as adding Excel PIA as ref'..... I guess as I am running Excel 2000 I do not have Microsoft Excel 12.0 Object Library, I only have Microsoft Excel 9.0 Object Library and Microsoft Excel 5.0 Object Library. This suggests to me I won't be able to get it to work in Excel 200 and will have to find out if Peter is running Excel 2003 first. I will get back to you as I progress.

ATB

John