Extracting a range to a listbox

[Dandlv]

I have a range in an excel document which I need to extract into a listbox. I can do this through .txt file, but really would prefer to source the information from excel. Any ideas?

Many tks for yr help in advance.

Best Rds.

[alex_read]

You'll need to...

• Start a new vb project
• Add 1 listbox and 1 command button to the form
• Goto the project menu > references option & select the Microsoft Excel Library line
• Create an excel file with the first 10 cells A1 to A10 with data in, then save this file as "C:\file.xls"

Private Sub Command1_Click()
    Dim objXLApp As Excel.Application
    Dim intLoopCounter As Integer
    
    Set objXLApp = New Excel.Application
    
    With objXLApp
        .Workbooks.Open "C:\File.xls"
        .Workbooks(1).Worksheets(1).Select
    
        For intLoopCounter = 1 To 10
            List1.AddItem .Range("A" & intLoopCounter)
        Next intLoopCounter
        
        .Workbooks(1).Close False
        .Quit
    End With
    
    Set objXLApp = Nothing
End Sub

[Dandlv]

Tks for the help..... I understand this code. And it works fine assuming the list in excel doesn't grow.

Is there a way to display a range which has been "named"

Example:

Range("Test")

Where Range("test") = A1 to A20

Tks for yr help!

[nswan]

You could loop through until you come to a cell that is blank.

With objXLApp
        .Workbooks.Open "C:\File.xls"
        .Workbooks(1).Worksheets(1).Select

    count = 1
    while strComp(.cells(count,1),"")<>0
        List1.AddItem .Range("A" & count)

        count = count + 1
    wend

end with

i haven't check this so the syntax may be wrong but the idea will work!!!!

[Dandlv]

I tried yr code I get teh following error msg:

Comile error:

Function or interface marked as restricited, or the function uses an Automation type not supported in Visual Basics.

Any ideas?

Tks for yr help.

[nswan]

put

dim count as integer

above the count = 1 line

[alex_read]

Sorry, only just noticed you replied to this - if the suggestions above don't work, you should be able to use this one:

Private Sub Command1_Click()
    Dim objXLApp As Excel.Application
    Dim intLoopCounter As Integer
    
    Set objXLApp = New Excel.Application
    
    With objXLApp
        .Workbooks.Open "C:\File.xls"
        .Workbooks(1).Worksheets(1).Select
    
        For intLoopCounter = 1 To CInt(.ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row)
            List1.AddItem .Range("A" & intLoopCounter)
        Next intLoopCounter
        
        .Workbooks(1).Close False
        .Quit
    End With
    
    Set objXLApp = Nothing
End Sub

[dnivi]

Have been following this thread as I'm doing something similar; however, I'm having a problem listing a range. Please see "not working" comment in code below.
Thanks for any help offered.

Private Sub Command1_Click()
    Dim objXLApp As Excel.Application
    Dim intRowCounter As Integer
    Set objXLApp = New Excel.Application
    With objXLApp
        .Workbooks.Open "C:\File.xls"
        .Workbooks(1).Worksheets(1).Select
        For intRowCounter = 1 To 25
            'Test for empty cell in column 2
            If StrComp(.Cells(intRowCounter, 2), "") <> 0 Then
                List1.AddItem .Range("A" & intRowCounter)
                'Not working: List1.AddItem .Range("A" & intRowCounter, "C" & intRowCounter)
            End If:
        Next intRowCounter
        .Workbooks(1).Close False
        .Quit
    End With
    Set objXLApp = Nothing
End Sub

[alex_read]

To specify a range, you have to use this format - A1:C1 rather than the comma, try altering the commented line to this:

List1.AddItem .Range("A" & intRowCounter & ":C" & intRowCounter)

[ITboy]

try using this ... your list box should be named "list"
Private Sub UserForm_Initialize()
Dim lngRow As Long
Dim strTxt As String
lngRow = 1
Do Until IsEmpty(Cells(lngRow, 1))
strTxt = strTxt & Cells(lngRow, 1) & vbLf
lngRow = lngRow + 1
Loop
list.Text = Left(strTxt, Len(strTxt) - 1)
End Sub

[djgeon]

I have a range in an excel document which I need to extract into a listbox. I can do this through .txt file, but really would prefer to source the information from excel. Any ideas?

Many tks for yr help in advance.

Best Rds.

Dim objXLApp As Excel.Application

I have paste the entire code and also created a button and a list box on the form as well as created the desired tables in the excel file.

on the buttons click event the application gives me this error

" User define type not defined " for Dim objXLApp As Excel.Application

[si_the_geek]

Welcome to VBForums

You haven't added the reference, as specified at the top of post #2.

You may find my Excel Tutorial (link in my signature) useful.

[preethgideon]

Hey Alex,
I am unable to find the "Microsoft Excel Library" in the Project emnu. Any other suggestions.

Thanks and Regards,
Gideon.

[westconn1]

I am unable to find the "Microsoft Excel Library" in the Project emnu. Any other suggestions.

to add a reference in VBA, menu > tools > references
vb6 is different, there it would be in the project menu
but if you are doing this in excel you do not need to do
if you are using late binding you do not need to do

if you have a question start a new thread, instead of reserrecting one that was started in 2003, and ask a full question with information about what program or application you are writing your code in