dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] trying to export listview items to excel it shows only 1 Item why?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Resolved [RESOLVED] trying to export listview items to excel it shows only 1 Item why?

    hey,
    i have a listview that i want to export to excel.
    now when i run it ,it opens excel and show me But
    only 1 item from the listview why?
    i am looping in the listview
    this is my code
    Code:
        Dim StrItemName As String
        Dim QtyName As Double
        Dim TotalSumName As Double
        Dim I As Long
        Dim K As Integer
        For K = 1 To LsVw.ListItems.Count
            StrItemName = LsVw.ListItems(K).Text
            QtyName = LsVw.ListItems(K).SubItems(1)
            TotalSumName = LsVw.ListItems(K).SubItems(2)
        Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
            xlApp.Range("K" & Trim(Str(1))).Value = "Item"
            xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
            xlApp.Range("G" & Trim(Str(1))).Value = "Total"
    
        I = 2
            xlApp.Range("K" & Trim(Str(I))).Value = StrItemName
            xlApp.Range("I" & Trim(Str(I))).Value = QtyName
            xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(TotalSumName)
            I = I + 1
            Totaltsum = FormatCurrency(Lbltotal.Caption)
        Next
    Tnx for your help
    salsa

  2. #2
    Lively Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    81

    Re: trying to export listview items to excel it shows only 1 Item why?

    Your constantly resetting I

    Code:
     I = 2
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Re: trying to export listview items to excel it shows only 1 Item why?

    Quote Originally Posted by Goggy View Post
    Your constantly resetting I

    Code:
     I = 2
    beacuse i need to loop
    what do i do then?

  4. #4
    Lively Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    81

    Re: trying to export listview items to excel it shows only 1 Item why?

    Just set I once, outside the loop
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  5. #5

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Re: trying to export listview items to excel it shows only 1 Item why?

    Quote Originally Posted by Goggy View Post
    Just set I once, outside the loop
    i dont understand
    where?

  6. #6
    Lively Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    81

    Re: trying to export listview items to excel it shows only 1 Item why?

    Maybe something like so:

    I haven't tested it , but i think you get the idea

    Code:
    Dim StrItemName As String
        Dim QtyName As Double
        Dim TotalSumName As Double
        Dim K As Integer
    
        Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
        xlApp.Range("K" & Trim(Str(1))).Value = "Item"
        xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
        xlApp.Range("G" & Trim(Str(1))).Value = "Total"
    
        For K = 1 To LsVw.ListItems.Count
            xlApp.Range("K" & Trim(Str(I))).Value = LsVw.ListItems(K + 1).Text
            xlApp.Range("I" & Trim(Str(I))).Value = LsVw.ListItems(K+ 1).SubItems(1)
            xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(LsVw.ListItems(K + 1).SubItems(2))
            Totaltsum = FormatCurrency(Lbltotal.Caption)
        Next
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  7. #7

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Re: trying to export listview items to excel it shows only 1 Item why?

    Quote Originally Posted by Goggy View Post
    Maybe something like so:

    I haven't tested it , but i think you get the idea

    Code:
    Dim StrItemName As String
        Dim QtyName As Double
        Dim TotalSumName As Double
        Dim K As Integer
    
        Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
        xlApp.Range("K" & Trim(Str(1))).Value = "Item"
        xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
        xlApp.Range("G" & Trim(Str(1))).Value = "Total"
    
        For K = 1 To LsVw.ListItems.Count
            xlApp.Range("K" & Trim(Str(I))).Value = LsVw.ListItems(K + 1).Text
            xlApp.Range("I" & Trim(Str(I))).Value = LsVw.ListItems(K+ 1).SubItems(1)
            xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(LsVw.ListItems(K + 1).SubItems(2))
            Totaltsum = FormatCurrency(Lbltotal.Caption)
        Next
    the excel sheet is empty lol

  8. #8
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,432

    Re: trying to export listview items to excel it shows only 1 Item why?

    Hi Salsa,

    try this...
    Code:
    Private Sub Command3_Click()
    Dim xlApp As Object, xlBook As Workbook, xlsheet As Object, i As Integer
        Dim s As String
        Dim lst As ListItem, lst1 As ListSubItem, row As Integer, col As Integer
       
        Set xlApp = New Excel.Application
        Set xlBook = xlApp.Workbooks.Add 'creates a blank workbook
        Set xlsheet = xlBook.Worksheets.Item(1) 'to select the sheet within the workbook
       
        row = 1
        col = 1
        For i = 1 To ListView1.ColumnHeaders.Count
            xlsheet.Cells(row, col) = ListView1.ColumnHeaders(i)
            col = col + 1
        Next
        col = 1
        row = row + 1
        For Each lst In ListView1.ListItems
            col = 1
            xlsheet.Cells(row, col) = lst.Text
            col = col + 1
            For Each lst1 In lst.ListSubItems
                xlsheet.Cells(row, col) = lst1.Text
                col = col + 1
            Next
            row = row + 1
        Next
        xlBook.Close savechanges:=True, FileName:="c:\ExceltestExport.xls"
        xlApp.Quit
    End Sub
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  9. #9
    Lively Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    81

    Re: trying to export listview items to excel it shows only 1 Item why?

    the moment i posted it was already obsolete. thanks to Chris )))

    Would you be so kind as to try this?

    Code:
        Dim XlApp As Object 'Holds an excel app
        Dim XlWb As Object  'Holds a workbook
        Dim XlWs As Object  'Holds a worksheet
        Dim ColumnIndex As Integer
        Dim RowIndex As Integer
        
        Set XlApp = CreateObject("Excel.Application")
        'Add a new workbook
        Set XlWb = XlApp.workbooks.Add
        'Get the first worksheet
        Set XlWs = XlWb.worksheets(1)
        
        With XlWs
            ' Set ColumnHeaders
            RowIndex = 1
            For ColumnIndex = 1 To 3
                .cells(RowIndex, ColumnIndex).Value = "Column_" & CStr(i)
            Next
            ' Loop through a List, and fill the cells.
            For RowIndex = 1 To LsVw.ListItems.Count
                XlWs.cells(RowIndex + 1, 1).Value = LsVw.ListItems(RowIndex).Text
                XlWs.cells(RowIndex + 1, 2).Value = LsVw.ListItems(RowIndex).SubItems(1)
                XlWs.cells(RowIndex + 1, 3).Value = FormatCurrency(LsVw.ListItems(RowIndex).SubItems(2))
            Next
        End With
        'Save the workbook
        XlWb.save
        XlWb.Close
        'Close the excel application
        XlApp.quit
    End Sub
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  10. #10

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Re: trying to export listview items to excel it shows only 1 Item why?

    i tried both methods still some errors
    i rather use my old code combine and fixed with your Help!!
    Code:
    Dim StrItemName As String
        Dim QtyName As Double
        Dim TotalSumName As Double
        Dim K As Integer
    
        Set xlwk = xlApp.Workbooks.Open(App.Path & "\Excel\General.xls")
        xlApp.Range("K" & Trim(Str(1))).Value = "Item"
        xlApp.Range("I" & Trim(Str(1))).Value = "Qty"
        xlApp.Range("G" & Trim(Str(1))).Value = "Total"
    
        For K = 1 To LsVw.ListItems.Count
            xlApp.Range("K" & Trim(Str(I))).Value = LsVw.ListItems(K + 1).Text
            xlApp.Range("I" & Trim(Str(I))).Value = LsVw.ListItems(K+ 1).SubItems(1)
            xlApp.Range("G" & Trim(Str(I))).Value = FormatCurrency(LsVw.ListItems(K + 1).SubItems(2))
            Totaltsum = FormatCurrency(Lbltotal.Caption)
        Next

  11. #11

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Re: trying to export listview items to excel it shows only 1 Item why?

    tnx all with all codes here i got it working

  12. #12
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,596

    Re: [RESOLVED] trying to export listview items to excel it shows only 1 Item why?

    First, you are overwriting your 'header' K1, I1, G1, etc. In your first part, you put Item, Qty and Total there.
    Then, the first time through your LV, you overwrite them with whatever is in lsVw.ListItems(K+1).Text.

    Then, you increment K in your loop, but not I...I stays as zero (assuming you dimmed it).

    Suggest you walk through your loop in the debugger and see for yourself how K changes, yet I does not.

  13. #13
    PowerPoster SamOscarBrown's Avatar
    Join Date
    Aug 2012
    Location
    NC, USA
    Posts
    6,596

    Re: trying to export listview items to excel it shows only 1 Item why?

    Sorry...late post...wife got me doing other things after I started it...

  14. #14
    Lively Member Goggy's Avatar
    Join Date
    Oct 2017
    Posts
    81

    Re: trying to export listview items to excel it shows only 1 Item why?

    Quote Originally Posted by SamOscarBrown View Post
    Sorry...late post...wife got me doing other things after I started it...
    Lol �� no problem, better late then never.
    Except at a party after all the beer is gone!
    Utterly useless, but always willing to help

    As a finishing touch god created the dutch

  15. #15

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,728

    Re: [RESOLVED] trying to export listview items to excel it shows only 1 Item why?

    Except at a party after all the beer is gone!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width