Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Load excel list (VB6)

    I'm trying to load a saved excel spread sheet that contains phone area codes and then 7 digit phone numbers (2 columns) into a VB listbox (2 columns). I'm not really sure how to go about doing this... i don't really know how to refer to the cells in the spreadsheet as i cycle through the rows. im using a commondialog load screen.

    im using the general code sample for calling the commondialog from MS Help, and then wrote a load list function(for a regular .txt file for now)

    Private Sub mnuloadlist_Click()
    ' CancelError is True.
    On Error GoTo ErrHandler
    ' Set filters.
    CommonDialog1.Filter = "All Files (*.*)|*.*|Text Files(*.txt)|*.txt|Batch Files (*.bat)|*.bat"
    ' Specify default filter.
    CommonDialog1.FilterIndex = 2

    ' Display the Open dialog box.
    CommonDialog1.ShowOpen
    ' Call the open file procedure.
    If CommonDialog1.Action = Cancel Then 'I added this, not sure if it actually does anything
    Exit Sub
    Else
    Call xLoadList(CommonDialog1.FileName, List1)
    Exit Sub
    End If

    ErrHandler:
    ' User pressed Cancel button.
    Exit Sub

    End Sub

    Public Sub xLoadList(FileName As String, List As ListBox)
    Dim lstInput As String
    On Error Resume Next
    Open FileName$ For Input As #1
    While Not EOF(1)
    Input #1, lstInput$
    'DoEvents
    List.AddItem lstInput$
    Wend
    Close #1
    End Sub]
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Load excel list (VB6)

    To read an Excel file, you must use Automation, i.e. create an Excel application object and use it to run Excel VBA code. Here is an example. It uses late binding, i.e. you don't need to set a reference to the Excel object library. Excel MUST be installed on the PC running your application, though. The code reads data from column A in the first worksheet of the workbook selected by the user. (A standard list box in VB6 has only one column)

    Private Sub mnuLoadList_Click()
    Dim strFilename As String
    Dim objXL As Object
    Dim objWB As Object
    Dim objWS As Object
    Dim lngRow As Long
    Dim lngMaxRow As Long

    On Error GoTo ErrHandler

    With CommonDialog1
    .Filter = "Excel workbooks (*.xls)|*.xls"
    .ShowOpen
    If .FileName = "" Then
    Exit Sub
    End If
    strFilename = .FileName
    End With
    Set objXL = CreateObject("Excel.Application")
    Set objWB = objXL.Workbooks.Open(strFilename)
    Set objWS = objWB.Worksheets(1)
    lngMaxRow = objWS.Range("A65536").End(-4162).Row
    For lngRow = 1 To lngMaxRow
    List1.AddItem objWS.Cells(lngRow, 1)
    Next lngRow

    ExitHandler:
    On Error Resume Next
    objWB.Close SaveChanges:=False
    objXL.Quit
    Set objWS = Nothing
    Set objWB = Nothing
    Set objXL = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Load excel list (VB6)

    would the code for that vary depending on the version of excel being used?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Load excel list (VB6)

    This code should work in Excel 97 or later. Excel 95 worksheets had only 16,384 rows, starting with Excel 97 it has been 65,536 rows.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Load excel list (VB6)

    ok, the versions would only vary from 2000 to 2003 anyway, thanks Hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Load excel list (VB6)

    this function works as said, however when i do this, the list goes to the right instead of down.... (scroll bar is on the bottom of the list) i don't understand why its doing this... any ideas?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Load excel list (VB6)

    nevermind, i deleted the list boxes and put new ones in.. i must have had some sort of columns set or something. code works great, thanks hans!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •