1. Computing

Reading Excel 2007 ".xlsx" in VB.NET

By October 30, 2007

Follow me on:

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
August 27, 2008 at 1:07 pm
(1) Christopher says:

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

September 9, 2008 at 3:25 pm
(2) visualbasic says:

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.

December 24, 2008 at 5:47 am
(3) Steve says:

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.

March 26, 2009 at 8:07 am
(4) Ashish says:

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.

March 27, 2009 at 12:42 pm
(5) visualbasic says:

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.

March 27, 2009 at 12:50 pm
(6) visualbasic says:

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

July 15, 2009 at 11:43 am
(7) Javier says:

Hi,

¿How to get the maxium number of row and columns a work sheet?

TX for the advice

December 3, 2009 at 1:23 pm
(8) JUDD says:

I WOULD CHECK ONE CELL FOR EACH RECORD THAT WOULD ALWAYS CONTAIN DATA(IF THERE IS A RECORD/ROW) FOR NULL VALUE. “IF NOT NULL-ROW+1, ELSE END COUNT”

January 31, 2010 at 1:55 am
(9) nausif says:

im trying to draw graphs using data values stored in excel in vb6.
can u help me how to get started
im new to visual basic
please help

January 31, 2010 at 10:20 am
(10) visualbasic says:

There’s a general ‘method’ that works for merging all versions of VB with VBA and all Office products. (Can’t get any more general than that!)

1 – You have to use the syntax required by your development environment (VB6 or VB.NET) to include the correct libraries for the Office product (Excel, Word, whatever) you’re using.

2 – To find the right Visual Basic statements to do what you want (say, create graphs), record the VBA macro in the Office product and then look at what was recorded to see how it was done. Copy these statements across to your VB development environment and work on converting the syntax.

Sometimes, it’s easier said than done and you have to invest some time discovering how to accomplish the conversion, but you can always do it if you work at it.

Since Excel graphs must be displayed as part of an Excel worksheet, you have to make Excel visible in order to display one in VB. This page from Microsoft explains the method:

http://support.microsoft.com/kb/142387

April 13, 2010 at 12:17 pm
(11) Lost441 says:

I was getting the application’ is ambiguous in the namespace Microsoft.Office.Interop.Excel‘ error.

I first added the Microsoft Excel 11.0 Object library. Then later I added the Microsoft Excel 12.0 Object library.

VB didnt know which one I was referring too, but one i removed the old reference. It worked. I used the following code to export a dataset to MS Excel 2007.

Private Sub fncExcelExport(ByVal strSql As String)

sql = strSql
Dim Excel As New Microsoft.Office.Interop.Excel.Application
Dim intColumn, intRow, intColumnValue As Integer
Dim strExcelFile As String
Dim strFileName As String
Dim conCurrent As New MySqlConnection
Dim varCommand As New MySqlCommand
Dim excelDA As New MySqlDataAdapter

excelDA = Nothing
ds = Nothing
ds = New DataSet

excelDA = New MySqlDataAdapter(sql, conn)
varCommand = New MySqlCommand(sql, conn2)
excelDA = New MySqlDataAdapter(varCommand)
excelDA.Fill(ds)

Dim strAppPath = System.Reflection.Assembly.GetExecutingAssembly.Location.Substring(0, System.Reflection.Assembly.GetExecutingAssembly.Location.LastIndexOf(“\”) + 1)

With Excel
.SheetsInNewWorkbook = 1
.Workbooks.Add()
.Worksheets(1).Select()
‘For displaying the column name in the the excel file.
For intColumn = 0 To ds.Tables(0).Columns.Count – 1
.Cells(1, intColumn + 1).Value = ds.Tables(0).Columns(intColumn).ColumnName.ToString
Next
‘For displaying the column value row-by-row in the the excel file.
For intRow = 0 To ds.Tables(0).Rows.Count – 1
For intColumnValue = 0 To ds.Tables(0).Columns.Count – 1
.Cells(intRow + 2, intColumnValue + 1).Value = ds.Tables(0).Rows(intRow).ItemArray(intColumnValue).ToString
Next

Next
‘Format columns D – G of totals section
.Range(“A1:O1″).Select()
With Excel.Selection
.Font.FontStyle = “Bold”
End With

Excel.Columns(“A:AY”).EntireColumn.AutoFit() ‘To adjust the column’s width.
Excel.Visible = True
.ActiveWorkbook().Activate()
End With
‘ MessageBox.Show(“File exported sucessfully.”, “Exporting done”, MessageBoxButtons.OK, MessageBoxIcon.Information)
NormalExit:
‘Excel.Quit()
‘Excel = Nothing
GC.Collect()
End Sub

….

fncExcelExport(“SELECT * FROM Customers”)

April 26, 2010 at 4:09 am
(12) crogiez says:

hello

VISTA wscript Office 2007

how make a “wscript.exe launch.vbs”
for open object excel
always in a maximazed window ???
(for a package)

set xl=createobject(“excel.application”)
xl.visible=true ‘make excel in screen down taskbar
xl.workbooks.open “workbook1.xlsm”
xl.displayfullscreen=false ‘for see menus
xl.displayscrollbars=true ‘for see toolbar right
xl.windows(1).windowstate=-4137 ‘maximised

but excel 2007 is always in minimised
in screen down taskbar

thank you

April 27, 2010 at 10:10 am
(13) crogiez says:

set oshell=createobject(“wscript.shell”)
cmd=”"
cmd=cmd & chr(34) & “long_path_excel” & chr(34)
cmd=cmd & chr(34) & “long_path_xls” & chr(34)
return=oshell.run(cmd,3)
regards

August 23, 2010 at 5:54 am
(14) B says:

Nice Article..It worked for me.

August 23, 2010 at 5:55 am
(15) Shah says:

Good one.

September 1, 2011 at 6:30 am
(16) John H., Liverpool, UK. says:

Some of the above is very useful. I learned alot from the code of Lost441.

Leave a Comment

Line and paragraph breaks are automatic. Some HTML allowed: <a href="" title="">, <b>, <i>, <strike>
  1. About.com
  2. Computing
  3. Visual Basic

©2014 About.com. All rights reserved.