Supercharge Your Office 2007 Applications with VSTO

1. Supercharge Your Office 2007 Applications with VSTO Paul Addison Associate Professor Computer Information Systems Ivy Tech Community College Lafayette, Indiana

2. What is VSTO? • Visual Studio Tools for Office • Uses power and controls of Visual Basic • Lets you program a Word or Excel document • User does not need Visual Basic to run it • Now packaged with Visual Studio 2008 • Includes a visual ribbon designer for Office 2007

3. Visual Studio controls • Drop-down boxes • Date-time pickers (calendars) • Buttons • Labels • Text boxes • Other VB controls you know and love

4. How it works • You embed controls into Word or Excel • You use Visual Basic to program the actions • You can combine: • Results of user input: text boxes, drop down boxes • Results of calculations: programmed • Text for output combined with results • You need a working knowledge of VB 2008

5. Some examples • Vehicle Sales Contract in Word • See Enrichment Chapter of Corrine’s VB 2008 textbook • Printable Invoice in Word • That’s what we’re going to do! • Sales Results in Excel • Included in this presentation • We will do it if we have time

6. Printable invoice in Word • Start Visual Studio 2008 / Visual Basic • Close Start Page • Create a New Project • Office at left / Word 2007 template in right pane • Name the project LakesideBandTicketOrders • Create a new document button should be selected • You may need to allow access to Visual Basic Applications • Rename vb file frmLakesideBandTicketOrders.vb

7. Design the template • Specify narrow margins • Type heading: Band Concert Ticket Order Form • Center, 24 pt, Bold, Calibri • Create a 2x5 table (2 columns, 5 rows) • Top row merged: Insert picture lakesideBand.jpg, resize to 75% • Choose table style: Green column, Medium Grid 1, Accent 3 • Select and format bottom left 4 cells: • Shading: Olive Green, Accent 3, Lighter 40% • Home tab: Calibri, 14 pt, Bold, Left Aligned • Enter Name:, Street Address:, City/State/Zip:, Email:

8. Design the template (2) • (Finish the table) • From Toolbox, All Windows Forms, put text box in row 2, right cell • Stretch text box to fill, set font to Calibri 14 • Rename txtName, remove text • In Table layout alignment, change cell margins to 0” • Copy and paste text box into lower 3 cells • Rename them txtStreetAddress, txtCityStateZip, txtEmail • Click below table to position cursor • Enter 2 text lines at Calibri, 14 pt, Bold • Concert dates are Tuesdays: April 7 and May 5 [Shift-Enter]Tickets are $10 each ($8 if you buy 4 or more) [Enter]

9. Design template (3) • Create labels and controls for date and tickets • Drag label, rename lblPickDate, set font Calibri, 12 pt, Bold • Text: Select a date: • Stretch the text box to the left to fit text [space after] • Drag a Date-Time picker control to the right of the label • Rename it datPickDate, set font Calibri, 12 pt • Stretch the text box to the right to fit date [Enter] • Drag a label to line below, rename it lblNumTickets • Set font to Calibri, 12 pt, Bold • Text: Select number of tickets:, stretch box [space after] • Drag combo box to right of label, rename it cboNumTickets • Set font to Calibri, 12 pt, Set Text to: 1 • Items: Collection, enter numbers 1 thru 10 on separate lines • Click to right of combo box, and press [Enter]

10. Design template (4) • Create button and label to compute price • Drag button, rename btnComputePrice, Calibri 14 pt, Bold • Text: Compute Price, stretch to left to fit text [space after] • Drag label to right of button, lblDisplayPrice, Calibri 12 pt, Bold • Text: Price:, stretch box to right [Enter] • Create button and label to display invoice • Drag button, rename btnDisplayInvoice, Calibri 14 pt, Bold • Text: Display Invoice,stretch to right to fit text [Enter] • Drag label below, rename lblDisplayInvoice, Calibri 12 pt (not Bold) • Text: INVOICE: • Resize label to about 6” wide and 3” deep • Save project using name LakesideBandTicketOrders

11. Your Word template should look like this:

12. Program Compute Price button • Double-click Compute Price button • A page opens up with Visual Basic code • The cursor is placed between the heading and End Sub Private Sub btnComputePrice_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnComputePrice.Click • Dim intNumTickets As Integer • Dim decTicketRate As Decimal = 10D • Dim decFullPrice As Decimal • intNumTickets = cboNumTickets.SelectedItem • If intNumTickets >= 4 Then • decTicketRate = 8D • End If • decFullPrice = intNumTickets * decTicketRate • If datPickDate.Text <> "Tuesday, April 07, 2009" And datPickDate.Text <> "Tuesday, May 05, 2009" Then • lblDisplayPrice.Text = "ERROR..Invalid date.“ • Else • lblDisplayPrice.Text = decFullPrice.ToString("C") • End If End Sub

13. Program Display Invoice button • Double-click Display Invoice button • A page opens up with Visual Basic code • The cursor is placed between the heading and End Sub Private Sub btnDisplayInvoice_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayInvoice.Click • Dim CRLF As String = Chr(13) + Chr(10) • lblDisplayInvoice.Text = "INVOICE:" & CRLF • lblDisplayInvoice.Text += txtName.Text & CRLF • lblDisplayInvoice.Text += txtStreetAddress.Text & CRLF • lblDisplayInvoice.Text += txtCityStateZip.Text & CRLF • lblDisplayInvoice.Text += txtEmail.Text & CRLF & CRLF • lblDisplayInvoice.Text += "Date of Concert: " & datPickDate.Text & CRLF • lblDisplayInvoice.Text += "Number of Tickets: " & cboNumTickets.SelectedItem & CRLF • lblDisplayInvoice.Text += "Total Price: " & lblDisplayPrice.Text & CRLF End Sub

14. Save and Run • Click File / Save All to resave project • Click Start Debugging button (green triangle) • Word opens: Scale View to 100% • Enter info, select date and # tickets • Click Compute Price button • Price based on number of tickets should display • Click Display Invoice button • Invoice with name/address and ticket info should display • Save template as document, close Word

15. Your Word document should look like this:

16. Issues • Please note: • We discovered that he certificate issue that prevented the program from compiling at the Course session can be bypassed by disabling User Account Security in the Control Panel. • Windows security issues • The dll file for the application must be moved with the document • You can change the Custom Property in _AssemblyLocation0 • You may have to also use the .NET Framework Configuration tool or wizard to grant FullTrust to the dll • You need to grant access to Visual Basic Applications • May need to check “Sign the Click Once Manifests” • If you get a certificate management error, you may need to disable User Account Security (see above)

17. Sales Results in Excel • Start Visual Studio 2008 / Visual Basic • Close Start Page • Create a New Project • Office at left / Excel 2007template in right pane • Name the project SalesReportWinner • Create a new document button should be selected • You may need to allow access to Visual Basic Applications • Rename first vb file sheetSalesReportWinner.vb

18. Design the template • Click in cell A1, type ABC Corporation • In A2, type 2008 Sales by Quarter • In A4, type SALESPERSON, resize column to fit • In B4:E4, type QTR1, QTR2, QTR3, QTR4 • In F4, type TOTAL • In F5, type =SUM(B5:E5) • Use fill handle to copy down to F6:F8

19. Design template (2) • FORMAT: • In A1, click Home tab, Cell Styles, Title • Select A1:F1, click Merge & Center • In A2, click Cell Styles, Heading 1, then select Cambria font • Select A2:F2, click Merge & Center • Select A4:F4, click Cell Styles, Heading 2 • Right-align cells B4:F4 • Select cells A8:F8, select Bottom border

20. Design template (3) • In A11, type HIGH SALES AMOUNT: • In A12, type =MAX(F5:F8) • In A13, type WINNING SALESPERSON: • In A14, type =OFFSET(A4,MATCH(A12,F5:F8),0) • This retrieves the first name that matches the highest value • Click View / Toolbox if necessary • Drag an Excel Control called Named Range to A14 • Confirm that $A$14 is the range • The control will have the name NamedRange1

21. Design template (4) • Drag a button to cell A16 • Rename it btnDisplayResults, set Font to Calibri, 12 pt, Bold • Change text to Display Results, resize to fit text • Drag a text box to A18 • Rename to txtDisplayResults • Change Font to Calibri, 14-pt Bold, ForeColor to Custom / Red • Change Text Align to Center, delete text from Text field • Stretch it to cover A18:F19 • Select A5:E8, Home tab, Format cells, Lock cell • This actually unlocks the cells when you protect the sheet • Select Format cells, Protect sheet • Leave a checkmark by Select Unlocked Cells • Remove checkmark by Select Locked Cells

22. Program Display Results button • Double-click Display Results button • A page opens up with Visual Basic code • The cursor is placed between the heading and End Sub Private Sub btnDisplayResults_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDisplayResults.Click • txtDisplayResults.Text = "Congratulations to Salesperson of the Year: " & NamedRange1.Text End Sub

23. Your Excel template should look like this:

24. Save and Run • Click File / Save All, save project • Click Start Debugging button (green triangle) • Excel opens • In A5:A8, enter four names • John Doe, Mary Doe, Lotta Dough, Play Doh • In B5:E8, enter amounts • Click Display Results button • Save template as document, close Excel

25. Your Excel sheet should look like this:

26. Supercharge Your Office 2007 Applications with VSTO Paul Addison Associate Professor Computer Information Systems Ivy Tech Community College Lafayette, Indiana THANK YOU!