VB / Excel Question

Jack_Jones
Jack_Jones used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.

Author

Commented:
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.
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
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Author

Commented:
Yep im totally lost, not sure even what the above does sadly.
How are you currently looping thru the excel file?

Author

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

Author

Commented:
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


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

Author

Commented:
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.
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
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
Why not use ADO???

Author

Commented:
I am interested in this ADO, but not sure how it works or even to code it. I made it this far using other solutions that were solved to get this far.
Here you go... I should have posted this with my comment.

http://support.microsoft.com/kb/257819
Most Valuable Expert 2012
Top Expert 2014
Commented:
The above is for VB and VBA. Use ADO.NET for VB.NET

http://vb.net-informations.com/datagridview/vb.net_datagridview_import.htm

Author

Commented:
Got the datagrid to work, kinda ugly so im going to look into away to clean it up and grab only what I need.
Hey Jack that's what I trying to show you.

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