asked on

VB / Excel Question

Greetings,

I am able to read the data from an xlsx file into textboxes into vb. What I would like to do is read the .xlsx file into a listview so that the data could be sorted by highest value to lowest value. Then have it export it back out to a new excel file, is this possible? Could someone help me with code, it's the only part im stuck on. I currently have the .xlsx data goign into textboxes, but think it might not be the greatest method.

Hawkvalley1

Wouldn't it be easier to load the data into a List(Of Integer)? Then call the Sort method on the List and send the data back.

Jack_Jones

ASKER

Yeah, im a newb so I started the hard way i guess. If you could help with that I would be so releaved. Having 125 textboxes is not fun lol.

Hawkvalley1

Dim myInts As New List(Of Integer)

When looping thru your range you simply use the add method. Then after the loop you use myInts.Sort
If your going from highest to lowest then iterate backwards thru your List

For I As Integer = myInts.Count - 1 To 0 Step - 1
Excel cell = myInts(i)
Next

Jack_Jones

ASKER

Yep im totally lost, not sure even what the above does sadly.

Hawkvalley1

How are you currently looping thru the excel file?

Jack_Jones

ASKER

Just haveing vb read 1 cell at a time, and then putting it's value inside of a textbox.

Jack_Jones

ASKER

after opeinging the excel file do this

drosiond1.Text = oSheet.Range("Z3").Value
        drosiond2.Text = oSheet.Range("Z4").Value
        drosiond3.Text = oSheet.Range("Z5").Value
        drosiond4.Text = oSheet.Range("Z6").Value
        drosiond5.Text = oSheet.Range("Z7").Value
        drosiond6.Text = oSheet.Range("Z8").Value
        drosiond7.Text = oSheet.Range("Z9").Value
        drosiond8.Text = oSheet.Range("Z10").Value
        drosiond9.Text = oSheet.Range("Z11").Value
        drosiond10.Text = oSheet.Range("Z12").Value
        drosiond11.Text = oSheet.Range("Z13").Value
        drosiond12.Text = oSheet.Range("Z14").Value
        drosiond13.Text = oSheet.Range("Z15").Value
        drosiond14.Text = oSheet.Range("Z16").Value
        drosiond15.Text = oSheet.Range("Z17").Value

Open in new window

Hawkvalley1

For Each n In Range("Z4:Z12) ' Substitute your range here
myInts.Add(n.Value)
Next n

Jack_Jones

ASKER

Cool, how would I then take the integers and put them into this datalist. Trying to word this the best I can and thaks for taking the time to help a nub lol.

Nasir Razzaq

I guess you have already got the Excel data to Listview now. Can you not use ADO.NET to read from Excel and then display the results in a DataGridView? Let me know if interested.

Hawkvalley1

In my example I am adding each interger into the List(Of Integer) which is a collection. After you fill it with the loop you call the Sort method. Now you have a collection of Integers that are sorted from lowest to highest. Now loop thru this List and place them into your excel range.

Dim k As Integer
For Each j As Integer In myInts
oSheet.Range(COL + k, ROW).Value = j
k += 1
Next