Thursday, January 10, 2013

All you need to know with Visual Basic and Excel

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.

Excel Sample


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.
Excel in process

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


Download the sample project here: SampleInteropExcel.zip


EDIT: missing something (Garbage collection)

9 comments:

  1. Great post, useful information, i surely bookmark this...

    ReplyDelete
  2. What if I declare oRange, ws locally in a sub and call the sub from another place. Then I cannot use the Finally block given here. What is the solution in that case?

    ReplyDelete
    Replies
    1. What you could do is to create a module or a shared function. That function could take ByRef objects. That way, you could call your functions anywhere and keep the object oRange or ws. You could try to clean those object elsewhere.

      Delete
  3. Oh my god...finally....thanks for making a simple task simple. I have been messing about for hours trying to open an excel file.

    ReplyDelete
  4. hi, download link of the sample project is not working, can you check it
    thanks

    ReplyDelete
    Replies
    1. The link is working perfectly fine. Make sure your browser or your connection is OK. Check see if your JavaScript is on or off.

      Delete
  5. Hi, Nice description about Visual Basic and Excel.Thanks, its really helped me......

    -Aparna
    Theosoft

    ReplyDelete
  6. Very informative and simplified. Thanks. :). I didn't get the sPath part tho. Please assist, anyone. Thanks in advance

    ReplyDelete
  7. A first chance exception of type 'System.Runtime.InteropServices.COMException' occurred in Serial Port Interface.exe...........I am getting this exception after following the example and the spreadsheet does not open

    ReplyDelete