Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

I need to fill a list box from an Excel File

Posted on 2005-03-08
9
Medium Priority
?
242 Views
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.

Thanks

        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
        oxl.Workbooks.Open(WorkBookName)
        'get the header count
        With oxl.Workbooks(1).Sheets(sName)
            .Activate()
            .Range("A23").Select()
            .Range("A23:A64").Select() '-4161 = xlToRight
            'fill headers in listbox
            For i = 1 To oxl.Selection.Count
                lstExcelData.Items.Add(.Cells(1, i).Value)
            Next
        End With
    End Sub
0
Comment
Question by:sherrick123
  • 4
  • 4
8 Comments
 
LVL 12

Expert Comment

by:S-Twilley
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)
            Next
        End With
    End Sub

==========================

Hope this works.. or at least helps
0
 

Author Comment

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

Expert Comment

by:S-Twilley
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

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

So I imported 5.0
0
 
LVL 12

Accepted Solution

by:
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
0
 

Author Comment

by:sherrick123
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)
           Next

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
0
 

Author Comment

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

Expert Comment

by:S-Twilley
ID: 13805661
Thanks...  it's all good as long as you reach the solution :P
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

572 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