I need to fill a list box from an Excel File

Posted on 2005-03-08
Medium Priority
Last Modified: 2010-04-23
I need to fill a list box from the contents of a Excel File
I have the following code but I pretty sure it is failing on the range part.


        Dim oxl As Object
        Dim i As Long
        Dim WorkBookName As String
        Dim sName As String

        'get workbook name
        WorkBookName = "H:\Bridge\Special Assignments\Stephen\PreStress Cells\Prestress Variables.xls"
        'get sheet name
        sName = "SuperStructure"

        oxl = CreateObject("Excel.Application")
        oxl.Visible = False
        'get the header count
        With oxl.Workbooks(1).Sheets(sName)
            .Range("A23:A64").Select() '-4161 = xlToRight
            'fill headers in listbox
            For i = 1 To oxl.Selection.Count
                lstExcelData.Items.Add(.Cells(1, i).Value)
        End With
    End Sub
Question by:sherrick123
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
LVL 12

Expert Comment

ID: 13487977
If I remember, letters in Excel increment going across... and numbers going down (which represent row numbers.  From what I can see, your range is stretching downwards in the first column... so should this line be change to reflect that

lstExcelData.Items.Add(.Cells(i, 1).Value)    ' .Cell(RowNumber, ColumnNumber


On a different note, I found it easier importing the Microsoft Excel Object Library... then used similar code to what you're doing (although its untested)

Imports Excel

Public Class Form1
    Inherits System.Windows.Forms.Form


    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim sheetName As String = "SuperStructure"

        Dim myExcel As New Excel.Application()
        Dim myDoc As Excel.Workbook = myExcel.Workbooks.Open("H:\Bridge\Special Assignments\Stephen\PreStress Cells\Prestress Variables.xls")
        Dim mySheet As Worksheet = myDoc.Sheets(sheetName)
        Dim iRow As Integer

        With mySheet
            For iRow = 23 To 64
                lstExcelData.Items.Add(.Cells(iRow, 1).Value)
        End With
    End Sub


Hope this works.. or at least helps

Author Comment

ID: 13488163
It does not allow a "New" Excel.Application
LVL 12

Expert Comment

ID: 13488237
Which Object Library did you import?

I imported the reference to "Microsoft Excel 10.0 Object Library"

and Im using VB.NET 2002... and it allowed me to
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 13488467
I imported the reference to 11.0 and it did not allow me to Imports Excel

So I imported 5.0
LVL 12

Accepted Solution

S-Twilley earned 375 total points
ID: 13488639
Seems strange...  by the way...did you try changing your original code with

lstExcelData.Items.Add(.Cells(i, 1).Value)  

... I think that's the correct way around, but I can' run Excel at the moment because of M$ activation thing being stupid

Author Comment

ID: 13489371
I went ahead and did it like this.  Not very elegant BUT its done...

         For i = 23 To 62 '1 To oxl.Selection.Count
                test = .range("A" & i).value
                lstExcelData.Items.Add(test & ".dgn") '(.Cells(i, 1).Value)

I never could get the dang Range thing to work.
            .Range(oxl.Selection, oxl.selection(???????).Select())

If you can get it to work.  Let me know.  I suppose if I work on this more I will need a better way to get the range of cells.

Any ideas on that???

Other wise you answered my question

Thanks again

Author Comment

ID: 13805570
I am going to give the points to S-Twilley,  He pointed me in the right direction
LVL 12

Expert Comment

ID: 13805661
Thanks...  it's all good as long as you reach the solution :P

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question