Bill Jelen, MrExcel, has marked this week to honor the VLOOKUP function in MS Excel. Around the online Excel development community You will find contributions, usually blog posts, that gives various aspects for using or not VLOOKUP.
I thought it would be interesting to create a case and see how VLOOKUP can be part of VB-solutions. Actually, for several years ago I did similar solutions but with classic VB (VB6) for a customer. Imagine that we have a price list for our products. Because of the frequent price updates the company has decided to store it in a MS Excel workbook. To keep it very simple for the sales staff they use a standalone VB-solution where they select products, retrieve the price for each product item and add some additional information manually to get a total price.
As most Excel developers should know, we can acquire data from workbooks in several ways. The most common approach is to treat the workbook as a flat database table. Technically we either use classic ADO or DAO to connect to the database table and using a SQL query to get the wanted records. This is a smooth solution but it assumes that the ADO/DAO library is available on all computers that will use the solutions. To avoid a situation where we must check if the library is installed or not we can use a solution where we automate Excel and execute the VLOOKUP function to get the wanted data. Practical we get a robust solution that is not depended on ADO/DAO and when working with small data sets the performance is fully acceptable.
The following screen shot shows how the price list workbook looks like:
As we can see it’s a simple table that is manually updated.
The next screen shot shows the VB-solution in action. The product list is picked up from a storage system. Sales person starts to select the products he/she wants to calculate the prices for, retrieve the prices, add number of items and then get a total price per product. The total prices including freight etc. is done in another part of the VB-solutions:
As You can see it’s a simple tool to work with. The last piece of the solution is the code I use to retrieve the prices and populate the DatagridView with.
The following code is in use and is executed when clicking on the Get Price button control:
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices
Public Class Form1
Private Sub btnVLOOKUP_Click(sender As System.Object, e As System.EventArgs) _
Handles btnVLOOKUP.Click
Dim Location As String = Environment.GetEnvironmentVariable("USERPROFILE") _
.ToString() + "\Documents\Product Lists\"
Dim File As String = "'" + Location + "[P_List_2011.xlsx]ProductList'!R1C2:R78C3,2,0"
Dim itemChecked As Object
If Me.CheckedListBox1.CheckedItems.Count = 0 Then
MessageBox.Show("Please select at least one product.")
Exit Sub
End If
Dim xlApp As New Excel.Application
Try
For Each itemChecked In Me.CheckedListBox1.CheckedItems
Dim Price As String = String.Empty
'Here we do the lookup based on the selected product name.
Price = xlApp.ExecuteExcel4Macro("VLOOKUP(""" + itemChecked.ToString() _
+ """," + File + ")")
Dim rowValues As String() = New String() {itemChecked.ToString, Price.ToString}
'Add the row with data to the data grid,
Me.dgwPList.Rows.Add(rowValues)
Me.dgwPList.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells
Next
Catch ex As Exception
MessageBox.Show(ex.ToString())
Finally
xlApp.Quit()
End Try
'Cleaning up.
If Not xlApp Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
xlApp.Quit()
End If
Marshal.FinalReleaseComObject(xlApp)
End Sub
Edit: I intentionally used the XLM macro program to show that we can still use it and with VB.NET. I thought that someone would point out that it can also be done by using xlApp.WorksheetFunction.VLookup. My friend Ken Puls, ExcelGuru, finally pointed it out.
Kind regards,
Dennis
VSTO & .NET & Excel 