ListView Contents to Excel

Jack_Jones
Jack_Jones used Ask the Experts™
on
Greetings,

 I have a completed listview, once in this listview it can be tweaked. I would then like to export the tweaked listview back to excel. Please help with working code, Thanks!

 Here is how I got the data from excel into the listview, it might not be the best way but it works.

 ListView1.Items.Add(oSheet.Range("A3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("D3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("F3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("G3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("I3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("N3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("R3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("V3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("Y3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("Z3").Value)
        ListView1.Items(0).SubItems.Add(oSheet.Range("AA3").Value)

        ListView1.Items.Add(oSheet.Range("A4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("D4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("F4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("G4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("I4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("N4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("R4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("V4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("Y4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("Z4").Value)
        ListView1.Items(1).SubItems.Add(oSheet.Range("AA4").Value)

        ListView1.Items.Add(oSheet.Range("A5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("D5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("F5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("G5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("I5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("N5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("R5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("V5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("Y5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("Z5").Value)
        ListView1.Items(2).SubItems.Add(oSheet.Range("AA5").Value)

        ListView1.Items.Add(oSheet.Range("A6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("D6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("F6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("G6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("I6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("N6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("R6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("V6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("Y6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("Z6").Value)
        ListView1.Items(3).SubItems.Add(oSheet.Range("AA6").Value)

        ListView1.Items.Add(oSheet.Range("A7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("D7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("F7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("G7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("I7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("N7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("R7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("V7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("Y7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("Z7").Value)
        ListView1.Items(4).SubItems.Add(oSheet.Range("AA7").Value)

        ListView1.Items.Add(oSheet.Range("A8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("D8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("F8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("G8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("I8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("N8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("R8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("V8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("Y8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("Z8").Value)
        ListView1.Items(5).SubItems.Add(oSheet.Range("AA8").Value)

        ListView1.Items.Add(oSheet.Range("A9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("D9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("F9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("G9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("I9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("N9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("R9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("V9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("Y9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("Z9").Value)
        ListView1.Items(6).SubItems.Add(oSheet.Range("AA9").Value)

        ListView1.Items.Add(oSheet.Range("A10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("D10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("F10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("G10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("I10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("N10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("R10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("V10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("Y10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("Z10").Value)
        ListView1.Items(7).SubItems.Add(oSheet.Range("AA10").Value)

        ListView1.Items.Add(oSheet.Range("A11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("D11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("F11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("G11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("I11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("N11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("R11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("V11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("Y11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("Z11").Value)
        ListView1.Items(8).SubItems.Add(oSheet.Range("AA11").Value)

        ListView1.Items.Add(oSheet.Range("A12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("D12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("F12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("G12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("I12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("N12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("R12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("V12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("Y12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("Z12").Value)
        ListView1.Items(9).SubItems.Add(oSheet.Range("AA12").Value)

        ListView1.Items.Add(oSheet.Range("A13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("D13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("F13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("G13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("I13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("N13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("R13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("V13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("Y13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("Z13").Value)
        ListView1.Items(10).SubItems.Add(oSheet.Range("AA13").Value)

        ListView1.Items.Add(oSheet.Range("A14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("D14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("F14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("G14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("I14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("N14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("R14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("V14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("Y14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("Z14").Value)
        ListView1.Items(11).SubItems.Add(oSheet.Range("AA14").Value)

        ListView1.Items.Add(oSheet.Range("A15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("D15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("F15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("G15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("I15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("N15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("R15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("V15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("Y15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("Z15").Value)
        ListView1.Items(12).SubItems.Add(oSheet.Range("AA15").Value)

        ListView1.Items.Add(oSheet.Range("A16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("D16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("F16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("G16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("I16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("N16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("R16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("V16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("Y16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("Z16").Value)
        ListView1.Items(13).SubItems.Add(oSheet.Range("AA16").Value)

        ListView1.Items.Add(oSheet.Range("A17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("D17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("F17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("G17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("I17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("N17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("R17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("V17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("Y17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("Z17").Value)
        ListView1.Items(14).SubItems.Add(oSheet.Range("AA17").Value)

        ListView1.Items.Add(oSheet.Range("A18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("D18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("F18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("G18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("I18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("N18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("R18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("V18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("Y18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("Z18").Value)
        ListView1.Items(15).SubItems.Add(oSheet.Range("AA18").Value)

        ListView1.Items.Add(oSheet.Range("A19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("D19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("F19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("G19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("I19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("N19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("R19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("V19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("Y19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("Z19").Value)
        ListView1.Items(16).SubItems.Add(oSheet.Range("AA19").Value)

        ListView1.Items.Add(oSheet.Range("A20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("D20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("F20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("G20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("I20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("N20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("R20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("V20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("Y20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("Z20").Value)
        ListView1.Items(17).SubItems.Add(oSheet.Range("AA20").Value)

        ListView1.Items.Add(oSheet.Range("A21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("D21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("F21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("G21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("I21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("N21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("R21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("V21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("Y21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("Z21").Value)
        ListView1.Items(18).SubItems.Add(oSheet.Range("AA21").Value)

        ListView1.Items.Add(oSheet.Range("A22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("D22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("F22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("G22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("I22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("N22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("R22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("V22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("Y22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("Z22").Value)
        ListView1.Items(19).SubItems.Add(oSheet.Range("AA22").Value)

        ListView1.Items.Add("Team Total") ' Total Team
        ListView1.Items(20).SubItems.Add(oSheet.Range("D24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("F24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("G24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("I24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("N24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("R24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("V24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("Y24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("Z24").Value)
        ListView1.Items(20).SubItems.Add(oSheet.Range("AA24").Value)

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What you meant by putting back to Excel, all listitem values into one column of a spreadsheet?
Most Valuable Expert 2012
Top Expert 2014
Commented:
First, you can simplify your existing code as below

 
For i As Integer = 0 to 20
        ListView1.Items.Add(oSheet.Range("A" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("D" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("F" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("G" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("I" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("N" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("R" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("V" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("Y" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("Z" & i+3).Value)
        ListView1.Items(i).SubItems.Add(oSheet.Range("AA" & i+3).Value)   
Next

Open in new window



Now to put the results back to Excel

 
For i As Integer = 0 to 20
        oSheet.Range("A" & i+3).Value = ListView1.Items(i).Text
        oSheet.Range("D" & i+3).Value = ListView1.Items(i).SubItems(i).Text
        oSheet.Range("F" & i+3).Value = ListView1.Items(i).SubItems(i+1).Text
        oSheet.Range("G" & i+3).Value = ListView1.Items(i).SubItems(i+2).Text
        oSheet.Range("I" & i+3).Value = ListView1.Items(i).SubItems(i+3).Text
        oSheet.Range("N" & i+3).Value = ListView1.Items(i).SubItems(i+4).Text
        oSheet.Range("R" & i+3).Value = ListView1.Items(i).SubItems(i+5).Text
        oSheet.Range("V" & i+3).Value = ListView1.Items(i).SubItems(i+6).Text
        oSheet.Range("Y" & i+3).Value = ListView1.Items(i).SubItems(i+7).Text
        oSheet.Range("Z" & i+3).Value = ListView1.Items(i).SubItems(i+8).Text
        oSheet.Range("AA" & i+3).Value = ListView1.Items(i).SubItems(i+9).Text
Next

Open in new window

Author

Commented:
Overload resolution failed because no Public 'Add' is most specific for these arguments:
    'Public Overrides Function Add(text As String) As System.Windows.Forms.ListViewItem':
        Not most specific.
    'Public Overrides Function Add(value As System.Windows.Forms.ListViewItem) As System.Windows.Forms.ListViewItem':
        Not most specific.

Not sure if that helps, come sup after the first ("A" & i + 3).Value)

Author

Commented:
I found somehow I changed the 20 to 18 and it was not correct. After setting it back to 20 it worked like a charm. Thank you so much codecuriser!
Most Valuable Expert 2012
Top Expert 2014

Commented:
Glad to help :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial