Automatically sorting imported data

[Guest]

My spreadsheet uses data from an external text file in CSV format. I appreciate the fact that it can be automatically refreshed every time I open the spreadsheet, but alas the imported data must be sorted, and the text file is not. How do I tell Excel that every time it imports data from the external file it must resort it on column C with ascending order
Thank yo
Pietro

 

[Frank Kabel]

Hi
one easy way: record a macro with these steps:
- start the recording
- import your data
- do the sorting
- stop the recording

In the future invoke this macro for importing data

--
Regards
Frank Kabel
Frankfurt, Germany

My spreadsheet uses data from an external text file in CSV format. I

appreciate the fact that it can be automatically refreshed every time I
open the spreadsheet, but alas the imported data must be sorted, and
the text file is not. How do I tell Excel that every time it imports
data from the external file it must resort it on column C with
ascending order?

 

[Guest]

This way, however, I am unable to automatize data refresh from the input file. I thought that there would be some way to hook some behavior to a data refresh event. However, the solution you proposed suits my actual needs. Thank you very much
Pietro

 

[Frank Kabel]

Hi
you may have an additional look at the sheetchange event of your
workbook (though not sure it will get triggered by an automatic
refresh). See
http://www.cpearson.com/excel/events.htm
for more details about event procedures

--
Regards
Frank Kabel
Frankfurt, Germany

This way, however, I am unable to automatize data refresh from the

input file. I thought that there would be some way to hook some
behavior to a data refresh event. However, the solution you proposed
suits my actual needs. Thank you very much.

 

[Jed]

Macro to update list in ascending order automatically

Right-click on the worksheet tab, select View Code, and paste this code into
the code pane:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 3 Then Target.CurrentRegion.Sort Target, xlDescending
End Sub

Note :Assumes that data is in column C.
Hope this helps

Regards
Jed

My spreadsheet uses data from an external text file in CSV format. I

appreciate the fact that it can be automatically refreshed every time I open
the spreadsheet, but alas the imported data must be sorted, and the text
file is not. How do I tell Excel that every time it imports data from the
external file it must resort it on column C with ascending order?

 

[Guest]

Thank you all for your valuable feedback. Here is the resulting macro (written with a little help of the macro editor):

Sub RefreshAndSortData()
'
' RefreshAndSortData Macro
' Macro recorded and edited 17/03/2004 by Pietro Braione
'

'
With Worksheets("Studenti")
Worksheets("Studenti").QueryTables("iscritti").Refresh BackgroundQuery:=False
Worksheets("Studenti").QueryTables("iscritti").ResultRange.Sort Key1:=.Range("C1"), _
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Worksheets("Studenti").Range("C:E").Copy Destination:=.Range("L:N")
Worksheets("Studenti").Range("L:N").Sort Key1:=.Range("M1"), Order1:=xlAscending, _
Key2:=.Range("N1"), _
Order2:=xlAscending, Key3:=.Range("L1"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortNormal
End With
End Sub

By now hooked to a new toolbar button, maybe in future I will hook it to a time trigger instead. Now I'm working on certifying it so I can keep macro security high.

Pietro