Bhaskar Saikia wanted to open and read an Excel workbook in VB but the program wasn't working.
Private Sub Form_Load() Dim cPart As Range Dim cLoc As Range Dim oExcel As Excel.Application Dim oBook As Workbook Dim oSheet As Worksheet Set oExcel = CreateObject("Excel.Application") Set oBook = oExcel.Workbooks.Open("C:\Data.xls", , False) Set oSheet = oBook.Worksheets("LookupLists") ' To find range in excel and send them to combo box(cboName) For Each cPart In oSheet.Range("NameRng") With Me.cboName .AddItem cPart.Value .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value End With Next cPart End Sub
He wrote, "This code works on Excel VBA. I want to use an Excel worksheet as a backend data storage and do some calculation on other sheet. But I want to use a standard VB Form as a frontend."
He's right. The code provided is quite specific to Excel, especially since it's full of Excel namespace objects such as Excel.Application. This code looks like it was copied from a site that hasn't been updated to work with VB.NET. I decided to see exactly what changes would be required to make the same code work with Excel 2007 (Excel 12.0) and VB.NET 2005.
First, I created a quick Excel 2007 spreadsheet and named a range of cells NameRng and the worksheet LookupLists to match the code he was using and saved the whole thing as Data.xlsx.
Then I created a standard Windows application and added a reference to Microsoft Excel 12.0 Object library (right-click the project and add the reference under the COM tab).
Here's the code that works (the old statements are commented out):
Imports Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Form1_Load( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles MyBase.Load Dim cPart As Range 'Dim cLoc As Range Dim oExcel As Application = _ CreateObject("Excel.Application") Dim oBook As Workbook = _ oExcel.Workbooks.Open( _ "C:\Users\ThisUser\Documents\Data.xlsx", , False) Dim oSheet As Worksheet oSheet = oBook.Worksheets("LookupLists") ' To find range in excel and send them to combo box(cboName) For Each cPart In oSheet.Range("NameRng") With Me.cboName .Items.Add(cPart.Value) '.AddItem(cPart.Value) '.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value End With Next cPart oBook.Close() End Sub End Class
A few things to watch for. The ComboBox component no longer supports multiple columns so the statement ....
.List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
... can't be used anymore.
But one of the most important changes is the addition of the statement ...
Without this statement, Excel continues to run as a process on your computer even after the application ends. If you try to open the same workbook in Excel again, you get the error that ...
Excel file locked for editing by 'my name'
You have to terminate the Excel process in Task Manager to open the file again. (You see a lot of advice on the web that tells you to delete your temporary files to unlock the Excel file. That doesn't work.)
But other than that, it works pretty good!