• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1889
  • Last Modified:

How To Read Excel And Load Data To Listview in VB6?

Hello I've been searching the forums about how to read an excel file and load the data into a listview. What I got from the forum is how to load data from a listview into excel.

What I have in mind is to select an excel file using a common dialog control and loads the data into a listview.

0
Fi-es
Asked:
Fi-es
1 Solution
 
SinghAmandeepCommented:

First Perform uploading part :

Dim xlApp As Excel.Application
Dim Wb As Excel.Workbook
Dim ws As Excel.Worksheet

Set xlApp = New Excel.Application
Set Wb = xlApp.Workbooks.Open("C:\sample.xls")

Set ws = Wb.Sheets(1)
Msgbox ws.Cells(1, 3)

**************** Then Use this to Load data to listview in VB6

Public Sub Load(mPath As String, LVW As ListView)
    Dim FSO As FileSystemObject, TS As TextStream, strLine as String,  tabLine() As String, lItem As ListItem, j As Integer
   
    On Error Resume Next
    If Len(Dir(mPath)) > 0 Then
        Dim tabReplace(4)
        tabReplace(2) = "name": tabReplace(3) = "phone": tabReplace(4) = "Good"
       
        Set FSO = New FileSystemObject
        Set TS = FSO.OpenTextFile(mPath, ForReading, False)
        While Not TS.AtEndOfStream
          strLine = TS.ReadLine
          if strLine <> empty then
            tabLine = Split(strLine, ":")
            Set lItem = LVW.ListItems.Add(, , tabLine(0))
            For j = 1 To UBound(tabLine)
                lItem.ListSubItems.Add , , tabLine(j)
            Next j
            For j = UBound(tabLine) + 1 To 4
                lItem.ListSubItems.Add , , tabReplace(j)
            Next j
          end if
        Wend
        TS.Close
        Set TS = Nothing
        Set FSO = Nothing
    End If
End Sub
0
 
GrahamSkanRetiredCommented:
You could do it like this:
Private Sub Command1_Click()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim c As Integer
    Dim r As Integer
    Dim strItem As String
    
    With CommonDialog1
        .Filter = "Excel (*.xls*)|*.xls*"
        .ShowOpen
        On Error Resume Next
            Set xlApp = GetObject(, "Excel.Application")
        On Error GoTo 0
        If xlApp Is Nothing Then
            Set xlApp = CreateObject("Excel.Application")
        End If
        xlApp.Visible = True
        Set xlWbk = xlApp.Workbooks.Open(.FileName)
    End With
    
    Set xlWks = xlWbk.sheets(1)
    c = 1
    r = 1
    strItem = xlWks.Cells(r, 1).Value
    Do Until strItem = ""
        With ListView1
            .View = lvwList
            .ListItems.Add , , strItem
            r = r + 1
            strItem = xlWks.Cells(r, 1).Value
        End With
    Loop
End Sub

Open in new window

0
 
Fi-esAuthor Commented:
The solution you given is working fine. Thank you for your support
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now