Just create my Excel VSTO add-ins with C#. Hope my experience can help you guys 😀
Requirement
So the problem that the user had is…. blindness. Wait a minute… The user is blind? Yes she is, and that’s tough. But luckily she had a helper program already in her notebook. This program can spoke the current status of the program, but of course sound based aid apps has its own limit. She currently does cashier function of her own restaurant. Until now, she is using a defined excel document to print the bill. Now, the user want to get a helper in excel that can do 2 simple task. The first feature is, she want a button to print her current page. For example, imagine now she currently selecting sheet 1 cell A300. When she want to print the current page, she need to know what’s the current page, click print, and input the number, etc or else the whole sheet will be printed and it will be messy. It is inconvenient for her. It is much more easier to click the button and the application do all the messy job. The second feature that see need is navigation. She currently using HP netbook (I forgot the exact type), and in this device, there’re no any page down or page up button. Well, it is troubling cos the visual aiding apps can’t read the current page number. So we need the substitution for navigational button here.
Analysis & Design
We have several options. We can use macro, or VSTO. Well, in this case I think VSTO is more appropriate. Macro is very limited I believe. The decision in this phase is arbitrary anyway. Now after VSTO is chosen. Now I must choose whether I need ribbon, task pane, or else. In the end, what is needed by her is the shortcut button anyway. After googling for awhile, I had an answer that the button shortcut cannot be overridden, and developing ribbon is easier to develop. So I choose ribbon to implement my UI. There’s a restriction on ribbon, which is the shortcut, is limited to Alt+X+… But, I believe it is okay. At least until I found the way to override Office standard shortcut someday.
Implementation
Printing the current page
——————————————————————————————————————–
int TotalRowsinOnePage = Convert.ToInt32(editjumlahhalaman.Text); //Get the variable from textbox. standard for 1 page = 60 rows
//get the current range
Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
//calculate the pagenumber
int row = rng.Row;
int pageNumber = (row / TotalRowsinOnePage) + 1;
/*void PrintOut(Object From,Object To,Object Copies,Object Preview,Object ActivePrinter,Object PrintToFile,Object Collate,Object PrToFileName)*/
((Microsoft.Office.Interop.Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).PrintOut(pageNumber, pageNumber, 1, false, Type.Missing, Type.Missing, Type.Missing, Type.Missing); //print the document
——————————————————————————————————————–
So, there’s a unique problem that I found when develop this procedure. I can’t find any method to return the current active page. Well, it’s troubling I believe. So, I’m doing some turn around. My assumption is the number of row that builds a page is constant. So, knowing the current row, we can compute the current page number. For the technical code, you guys can check my code up there. I believe the comment is more than enough to explain the code meaning 😀
Navigation
——————————————————————————————————————–
//Page Down Procedure
int TotalRowsinOnePage = Convert.ToInt32(editjumlahhalaman.Text); // Get the variable from textbox. standard 1 page = 60 rows
//get the current range
Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
//calculate the pagenumber
int row = rng.Row;
int NextpageNumber = (row / TotalRowsinOnePage) + 1; //compute next page
int NextRow = (NextpageNumber * TotalRowsinOnePage) + 1;
String theTargetCells = “A” + Convert.ToString(NextRow); //make the string
//Select the destinated cells
Microsoft.Office.Interop.Excel.Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
var range = activeSheet.get_Range(theTargetCells, theTargetCells);
range.Select();
——————————————————————————————————————–
Like I said before, VSTO don’t give method to return current page number. And the more troubling problem is the select procedure need range object to operate on. So what’s strange ? Hohoho, range is an abstract class, and we can’t construct that class out of the blue… And at last, I found that cool get_Range method (it really saves my day 😀 ). But there’s another problem. This method is asking for cells name (we can’t input the column and row integer number). In the end, what I do is making the cells string (theTargetCells) by concatenation and then after the string is made, I’m passing it to the procedure.
Conclusion
This is my first time in building Excel VSTO. Damn hard it is 😦 Not because it is crazily complicated (cos as you guys can see, my code is not that long). The real problem is the documentation. VSTO documentation is very rare. About one month ago, I’m creating digital signature add ins in Microsoft Outlook, and reference for outlook is more sufficient, not like Excel VSTO. And for you guys who start doing Excel VSTO, my tips is… start searching the MSDN. That’s your only reference, that’s your only friend, and MSDN is usually not the top three list when you googling about this topic.
N.B. Well, maybe someday if it is possible, I will post my outlook VSTO program logic 😀
May Help
-Archie-
My thinking 