Congratulations to Mr. Martin Pascal ! His article was vote the best among the contributions MSDN 15-03-2010 - 15-04-2010

How to Use Automation with Excel and VB.NET?

Preparations

To control Excel, you must add to your project a reference to Microsoft Excel Object Library (in COM components) or directly reference the Primary Interop Assemblies (PIA) Microsoft.Office.Interop.Excel.dll, Microsoft.VBE.Interop . dll and Office.dll

In this example I will use the second solution.

About Primary Interop Assemblies

Although Microsoft recommends using a version of the PIA corresponding to the Office installed together with your application version, I found that there is a good backward compatibility between these assemblies. So I made it a habit to use an old version of the PIA, those of Office XP. They have the advantage of allowing my application to run on all versions of Office XP to 2007.

You can download these assemblies on the Microsoft website:

The basics of Excel automation

Creation and assignment of the Excel object

XlApp As Microsoft.Office.Interop.Excel.Application = New Microsoft.Office.Interop.Excel.Application() Dim xlApp As New Microsoft.Office.Interop.Excel.Application Microsoft.Office.Interop.Excel.Application = ()

Creating a Workbook

XLDestBook As Microsoft.Office.Interop.Excel.WorkbookXLDestBook = XlApp.Workbooks.Add Sun XLDestBook As Microsoft.Office.Interop.Excel.WorkbookXLDestBook = XlApp.Workbooks.Add

Open an existing workbook

) XLDestBook XlApp.Workbooks.Open = ("c: \ monfichier.xls")

Adding a sheet

xlSheet As Microsoft.Office.Interop.Excel.WorksheetxlSheet = XLDestBook.Worksheets.Add Sun xlSheet As Microsoft.Office.Interop.Excel.WorksheetxlSheet = XLDestBook.Worksheets.Add

Add text in a cell

xlSheet.Cells (1, 2). value = "Test writing in cell B1"

Add a formula in a cell

xlSheet.Cells (1, 3). formula = "1 = 5"

The forms must be written in the language of Excel. If your users are likely to use Excel versions in different languages ​​so you should bring your formulas. For this you can use this reference

Recommendations

Make it a habit before processing automation disable the Excel screen refresh, and prevent user input. The performance will be greatly improved, and this will prevent your users from disrupting the treatment.

For this, use the following lines before any treatment:

XlApp.ScreenUpdating = FalseXlApp.Interactive = False

Iron these parameters to True when your treatment is completed, otherwise Excel will be unusable.

For more information please refer to the Microsoft Knowledge Base:

Attention to language

If you have users using an English version of Windows, there are chances that your application crashes with message: Error: 0x80028018 (-2147647512) - Description: Old Format or Invalid Type Library

This scenario occurs when the user has customized the Windows regional settings. This is a case that I regularly met with expatriates. They select the locale in their country of residence, and change the decimal separator and the currency to match those of their country of origin.

Source: social.msdn.microsoft.com