Reading Excel 2007 ".xlsx" in VB.NET
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 ...
oBook.Close()
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!
Comments
I am trying in to implement this code, but I have encountered an unexpected error: An error occurred creating the form. See Exception.InnerException for details. The error is: Object reference not set to an instance of an object.
The code works when I test with MsgBox(Cell.Value) but not when I try to use the cboBox.Items.Add(Cell.Value). I am using Visual Studio 2008 and Excel 2003.
Here is the code:
Private Sub PopulateFields()
Dim Cell As Range
Dim FieldData As New Object
FieldData = CreateObject("Excel.Application")
Dim DataBook As Workbook
DataBook = FieldData.Workbooks.Open("C:\Documents and Settings\critter\Desktop\Data.xls", , False)
Dim DataSheet As Worksheet
DataSheet = DataBook.Worksheets("Data")
'Cell = DataSheet.Range("Field")
For Each Cell In DataSheet.Range("Fields")
With Me.AssayBox()
.Items.Add(Cell.Value)
End With
Next Cell
DataBook.Close()
Return
End Sub
I don’t quite know what to tell you. I copied your code (using Excel 2007, but other than that, the same as your configuration) and it worked like a shot for me.
This has been doing my head in for a long time now, ok all I want to do is open an Excel workbook and read the cell values, sounds simple, but its not.
I can create the excel, workbook and the sheet objects and it all works fine, but when I try and read the values of a worksheet below row 1 it just returns an empty string, also if I use the UsedRange.Rows.Count it will only return 1 even if the workbook is full, what’s going on??
So basically what’s going on is that it is only reading row 1
Imports Microsoft.Office.Interop.Excel
Dim strFileName As String = ShowFileDialog() ‘ returns a string to open (strFileName)
Dim cPart As Range
Dim oExcel As Application = CreateObject(”Excel.Application”)
Dim oBook As Workbook = oExcel.Workbooks.Open(strFileName, , False)
Dim oSheet As Worksheet = oBook.Worksheets(1)
For Each cPart In oSheet.Range(”A1:A2″)
MsgBox(cPart.Value)
Next cPart
oBook.Close()
MsgBox (Range(“A1”).Value.ToString()) ‘this works!!
MsgBox (Range(“A2”).Value )( or .Text / .FormulaR1C1) ‘this returns an empty string!!! But it’s not empty
I am using xp x64, Visual Studio 2008 with Excel 2002 – could it be that I am using 2002 version of Excel?
I’ve tried to use different references Com objects and .Net and they all do the same any help we be greatly appreciated Thanks.
I am adding reference for ‘Microsoft Excel 12.0 Object library‘ and import it on my .aspx page under .Net framework 2.0 in visual studio 2005
but getting error at -
“Dim oExcel As Application = CreateObject(”Excel.Application”)”
error is – ‘application’ is ambiguous in the namespace Microsoft.Office.Interop.Excel‘.
i am also referecing ‘Microsoft Excel 5.0 Object library‘
but the same code and library are working fine on my other computer. both system have microsoft office 2007 on windows xp profession. both installed from same CD.
i dont know what is the problem.
on ther system when i reference above said library i also get ‘Interop.Microsoft.Office.Core.dll‘ in my bin directory but my this system does not have this after referencing same library.
Apologies to Steve for not noticing that this message was here. I only saw it when I looked for the comment left by Ashish.
The problem with Steve’s code appears to be that the Range “A1:A2″ is only on one row. If you include all the rows that you want to read, something like “A1:A99″, then it works fine.
Ashish,
To resolve this error, use a fully-qualified reference or a namespace alias, for example:
Dim xlApp as Microsoft.Office.Interop.Excel.Application
Or:
Imports Excel = Microsoft.Office.Interop.Excel
…
Dim xlApp As Excel.Application
Hi,
¿How to get the maxium number of row and columns a work sheet?
TX for the advice