Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1960
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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