Automate Microsoft Excel with Visual Studio 2010Enhancements in the.NET Framework 4 and Visual Studio 2010 make Office automation solutions easier than ever to write and deploy. Here's an Excel automation scenario that reflects solutions that I've seen requested by multiple clients.

1. Visual Studio Excel Project

By. Many computer users live in the Microsoft Office suite, using Word, Excel and Outlook as the core tools to perform the majority of their daily computer tasks. I am amazed at the number of serious.NET business software developers that fail to exploit this familiar and comfortable environment to deliver easy-to-use solutions.

Enhancements in the.NET Framework 4 and Visual Studio 2010 make Office automation solutions easier than ever to write and deploy, in either C# or Visual Basic.In this article I present an Excel automation scenario that is closely representative of solutions requested by multiple clients. My experience is that there is a strong demand for Excel automation solutions in the finance departments across a wide variety of industries.Chinook and Northwind Have MergedOur scenario is that the Chinook company has purchased the Northwind company and the Chinook CFO has hired you to provide automation to create consolidated financial statements. You must provide a solution that allows the Controller to simply pick a financial reporting period and click a button to produce a consolidated financial statement for that period. The statement must list invoices by date, show the Sales to Date for the customer alongside the order total, and highlight the largest sale order for the period in bold font. The Chinook invoice detail must be filled on a new worksheet tab for verification.

Excel reporting samples with SpreadsheetGear, a royalty free Microsoft Excel compatible spreadsheet component for the Microsoft.NET Framework featuring the fastest and most complete calculation engine available. Create, read, modify, calculate and write Microsoft Excel workbooks from your Microsoft.NET, ASP.NET, C#, VB.NET and Microsoft Office solutions.

Visual Studio Excel Project

A sample completed consolidated spreadsheet is shown in Figure 1.Click on image for larger view.Figure 1. Sample Consolidated Financial SheetYour research has shown that adding a custom ribbon tab to Excel will meet the client's requirements.

The custom tab will be named 'Accounting' and will contain a drop-down list of available financial periods (months) for the selected company or companies as shown in Figure 2. The Period drop-down lists the financial periods available based on which company is checked, as an example of dynamic data interaction based on Ribbon controls status. Once the desired period is selected, the user clicks the Load Data button and the result is a completed consolidated spreadsheet as shown in Figure 1 above. Custom Accounting Tab in Excel 2010Database Note: Both sample databases are implemented as SQL Compact 3.5 databases to permit the databases to install with the demo application.

The Chinook database is a sample database available for SQL Server and other databases at. The Northwind database is taken from the SQL Server Compact 3.5 installation at C:Program Files (x86)Microsoft SQL Server Compact Editionv3.5Samples. The Northwind Order dates were adjusted to correspond to the same years as Chinook Invoice dates to provide accounting periods with activity from both companies.

LINQ to SQL is used to access both databases.Creating the Excel SolutionVisual Studio 2010 offers a number of Office templates as shown in Figure 3. Choosing the Excel 2010 Workbook template opens a blank Excel workbook template that is the spreadsheet shown when the solution is run. This is a document level customization; the Accounting tab will appear only in this particular template when opened in Excel. Selecting this template offers the choice to create a new document or add to an existing document as shown in Figure 4.

Asus p5q pro turbo xeon bios download. In the BIOS, the voltage is set to 2.1 and frequency is forced at 1066. I am getting a replacement. If all left to default, POST shows these rams at 800 MHz.(The system has been running stable ever since I detected the faulty unit.

Select the option to create a new document.Figure 3. Available Office 2010 Visual Studio TemplatesFigure 4. Option to add customizations to a new or existing Excel documentTo provide a spreadsheet with proper formatting that can be filled in by our customization, the blank template is modified as shown in Figure 5.

The yellow column A contains keyword markers that identify which rows receive particular data. Column A is hidden after the data is filled on the template. This provides a positive visual cue to the user that the process has completed.Figure 5. Excel template to be filled by the customizationAdding a new item to a Workbook project offers the templates shown in Figure 6.

Choosing the Ribbon Designer produces a blank Ribbon tab. Figure 7 shows the blank Ribbon and controls available to be used on the Ribbon.

The customized Ribbon is shown in Figure 2 above.Figure 6. Office Templates for Excel 2010 Workbook SolutionsFigure 7. The blank Ribbon control to provide a user interface for Excel Workbook customization.The solution shown in Figure 8 consists of the Data.Chinook project to provide needed methods to query the Chinook sample database, the Data.Northwind project provides the needed methods to query the Northwind database, the Data.Shared provides a few common functions.

The Data projects contain some interesting LINQ-to-SQL queries but are otherwise outside the discussion of this article.Figure 8. Consolidated Reporting Solution.Almost all of our custom Ribbon code is contained in Ribbon.vb and Utility.vb of the Excel2010WorkbookVB project.