Automatically sorting imported data

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

Z said:
My spreadsheet uses data from an external text file in CSV format. I
Click to expand...
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?
 
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
 
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

Z said:
This way, however, I am unable to automatize data refresh from the
Click to expand...
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.
 
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



Z said:
My spreadsheet uses data from an external text file in CSV format. I
Click to expand...
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 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
 
Back
Top