All you need to know with Visual Basic and Excel
You want to develop applications using Visual Basic (Visual Studio) that interact with Excel, but you do not know where to start? Read this article to learn more about the development of import and export Excel. You would be able to develop your own programs and interact with Excel files. Better than a tutorial, it includes most of the programming knowledge and a sample project at the end of article. This article is also perfect for VBA programmers who often have with the language in Excel or any other specialized software.
Please note that Excel 2010 is used for writing my article. The information may change from one version to another Excel or by other factors such as the version of Windows or the processor type.
COM Technologies and. NET
You've probably seen the notices COM and NET references when dealing with references or bookstores. The only difference is that the COM technology is not supported. In other words, there is no function tables and you must know by heart functions in order to use them. Dot NET are just the opposite. When loading a DLL. NET, Visual Studio (Visual Basic, C #, etc.) will automatically detect all the elements in the interior. There are other different, but they are not important for us here.
Visual Basic for Applications in Excel (VBA)
You can make macros in the VBA editor in Excel. The programming language used is Visual Basic. Noticed something interesting in this VBA editor. This is the direct link between your program and Excel spreadsheets. You can access the data in the leaves or cells easily. You also have access to Visual Basic functions for Excel to your code. How about normal, Excel VBA module is the interface Visual Basic and Excel.
Unfortunately, outside of Excel, your code may not work as easily. You must use the COM technology to be able to interface with Excel data and examples on the internet are not necessarily easy to implement.
Then read this article to become a king on Excel programming outside of Excel.
Important objects to use Excel
I'll spare you the explanation to go straight to the point. You must know how to use the 4 following
Application
Workbook
Worksheet
Range
And it is as simple as that. Everything else is either a result or superfluous. This is the basic focus you get the best with these four objects. Examples follow.
Keep in mind that you can not do anything outside of Excel as easily as if you were inside. Some features will also be unable to use unless you are using more sophisticated tools that are not addressed in this text.
Import the library in your Visual Basic
At the top line of your file vb, I put the following line:
Imports Excel = Microsoft.Office.Interop.Excel |
The key word is my Excel object and it's going to avoid using the long name to access the Excel application.
Open an Excel file with Application
I scored 3 lines you need to open an existing Excel file. sPath is the full path to access your existing Excel file. For example: "c: fichier.xlsx"
Dim oExcel As Excel.Application oExcel = New Excel.Application oExcel.Workbooks.Open(sPath) |
If you have a problem with these lines is that you do something different. For example, create a new Excel file. If this is the case, try:
Dim oExcel As Excel.Application oExcel = CreateObject("Excel.Application") oExcel.Workbooks.Open(sPath) |
Open Workbook (English Workbook)
Use the following line:
Dim wb As Excel.Workbook wb = oExcel.Workbooks(1) |
The purpose wb contain your workbook simply. The first index is always 1.
Open an Excel worksheet
The following code allows you to navigate between sheets available.
Dim ws As Excel.Worksheet For index2 = 1 To wb.Worksheets.Count Step 1 ws = wb.Worksheets(index2) Next index2 |
For performance reasons, avoid using Each function in your loops.
Display the name of Excel Worksheet
It's pretty simple thank you. Use the Name property to find the sheet you want to work.
MsgBox(ws.Name) |
Reading Range in Excel cells with
Range is the object to be used for the cells. Unfortunately the Cell object is shifted with time and it is highly likely that you have that using Range.
Here is an example that displays a value of all cells (Range). The example use a small special function to determine the last used column and the last row used.
Dim wb As Excel.Workbook Dim ws As Excel.Worksheet Dim oRange As Excel.Range Dim mRow As Integer = CInt(ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row) Dim mColumn As Integer = CInt(ws.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Column) For index1 = 1 To mRow For index3 = 0 To mColumn Step 1 'MsgBox(CChar(ChrW(65 + index3))) '65=>A oRange = ws.Range((CChar(ChrW(65 + index3)) & index1)) MsgBox (oRange.Value) Next index3 Next index1 |
Close the Excel object
Here is a simple line but insufficient
oExcel.Quit() |
In an ideal world, when you open an Excel file to read data, Excel will properly close. Excel will not reside in your job or process managers (EXCEL * 32). In reality, you need to provide more secure data recording Excel and the health of your Windows operating system. Otherwise, your program becomes unstable and unreliable.
Here is an example line to add to the end of your function to improve the closure of your Excel file.
oExcel.Quit() System.Runtime.InteropServices.Marshal.ReleaseComObject(oRange) System.Runtime.InteropServices.Marshal.ReleaseComObject(ws) System.Runtime.InteropServices.Marshal.ReleaseComObject(wb) System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) |
Whenever you create a COM object, a reference value is added. The goal is to reduce the value to zero when you are finished with your COM type objects. Otherwise, you may have a parasite or Excel resident in your process. You may have complications if you routinely use programs that do not clean properly its COM objects.
Also make sure that Excel is closed after a crash software (Excel or yours) using the function correctly try-catch-finally. Also avoid declaring and creating your items on a single line without which you could not check your objects clause Finally.
Finally If oExcel IsNot Nothing Then System.Runtime.InteropServices.Marshal.ReleaseComObject(oRange) System.Runtime.InteropServices.Marshal.ReleaseComObject(ws) System.Runtime.InteropServices.Marshal.ReleaseComObject(wb) System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel) oExcel = Nothing End If End Try |
EDIT: missing something (Garbage collection)
0 Response to "All you need to know with Visual Basic and Excel "
Posting Komentar