Hello,

I have some data on a spreadsheet. The data is a list of folders that
I later showing using the Treeview control.

The data is the direct output of a DOS command: dir <Folder Name>/a:d
/b/s > file.txt

I am reading in the data from the file.txt file and putting it on a
spreadsheet

If I choose the Folder Name as "C:", that has 3 folders "Satish",
"Temp" and "Satish Work", and each folder contains one sub-folder as
shown below -
C:\Satish
C:\Satish Work
C:\Temp
C:\Satish\Audacity
C:\Satish Work\Excel
C:\Temp\Buttons

Note: Each row is in one Cell, say column A.

The above DOS command first lists the main folders and then one-by-one
will list the sub-folders within each main folder. But, I want
something like -
C:\Satish
C:\Satish\Audacity
C:\Satish Work
C:\Satish Work\Excel
C:\Temp
C:\Temp\Buttons

I want it to list all the sub-folders within a folder before moving on
to the next folder. I sort of achieved it by sorting the data using:

ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
Key1:=Worksheets(1).Range("A1")

But the default sort of Excel lists the data like -
C:\Satish
C:\Satish Work
C:\Satish Work\Excel
C:\Satish\Audacity
C:\Temp
C:\Temp\Buttons

Please note that C:\Satish\Audicity has appeared after C:\Satish
Work\Excel. This is because Excel sorts the data in the following
order -
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `
{ | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

Space comes before "\". Hence I have this problem.

Is there any way I can make Excel sort this data in the order I want
(as mentioned above)?
Please help me, because the treeview that I later use uses some logic
and it has to have data in the correct order.

Thanks!
Satish

i'm not sure whether this work or not.
first relace \ with < , and (space) with >.
after sorting, relace back < with \, and > with (space)
this is a test code with your data.

Sub testsort()
With Range("A1:A6")
.Replace What:="\", Replacement:="<", LookAt:=xlPart
.Replace What:=" ", Replacement:=">", LookAt:=xlPart
.Sort Key1:=Range("A1"), Order1:=xlAscending
.Replace What:="<", Replacement:="\", LookAt:=xlPart
.Replace What:=">", Replacement:=" ", LookAt:=xlPart
End With
End Sub

keizi

"Satish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hello,
>
> I have some data on a spreadsheet. The data is a list of folders that
> I later showing using the Treeview control.
>
> The data is the direct output of a DOS command: dir <Folder Name>/a:d
> /b/s > file.txt
>
> I am reading in the data from the file.txt file and putting it on a
> spreadsheet
>
> If I choose the Folder Name as "C:", that has 3 folders "Satish",
> "Temp" and "Satish Work", and each folder contains one sub-folder as
> shown below -
> C:\Satish
> C:\Satish Work
> C:\Temp
> C:\Satish\Audacity
> C:\Satish Work\Excel
> C:\Temp\Buttons
>
> Note: Each row is in one Cell, say column A.
>
> The above DOS command first lists the main folders and then one-by-one
> will list the sub-folders within each main folder. But, I want
> something like -
> C:\Satish
> C:\Satish\Audacity
> C:\Satish Work
> C:\Satish Work\Excel
> C:\Temp
> C:\Temp\Buttons
>
> I want it to list all the sub-folders within a folder before moving on
> to the next folder. I sort of achieved it by sorting the data using:
>
> ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
> Key1:=Worksheets(1).Range("A1")
>
> But the default sort of Excel lists the data like -
> C:\Satish
> C:\Satish Work
> C:\Satish Work\Excel
> C:\Satish\Audacity
> C:\Temp
> C:\Temp\Buttons
>
> Please note that C:\Satish\Audicity has appeared after C:\Satish
> Work\Excel. This is because Excel sorts the data in the following
> order -
> 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `
> { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
>
> Space comes before "\". Hence I have this problem.
>
> Is there any way I can make Excel sort this data in the order I want
> (as mentioned above)?
> Please help me, because the treeview that I later use uses some logic
> and it has to have data in the correct order.
>
> Thanks!
> Satish
>

Thanks a lot! I figured it out myself just after I posted the question.
Thanks for your reply though...I checked your code works!! :-)
Cheers!
Satish

kounoike wrote:
> i'm not sure whether this work or not.
> first relace \ with < , and (space) with >.
> after sorting, relace back < with \, and > with (space)
> this is a test code with your data.
>
> Sub testsort()
> With Range("A1:A6")
> .Replace What:="\", Replacement:="<", LookAt:=xlPart
> .Replace What:=" ", Replacement:=">", LookAt:=xlPart
> .Sort Key1:=Range("A1"), Order1:=xlAscending
> .Replace What:="<", Replacement:="\", LookAt:=xlPart
> .Replace What:=">", Replacement:=" ", LookAt:=xlPart
> End With
> End Sub
>
> keizi
>
> "Satish" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hello,
> >
> > I have some data on a spreadsheet. The data is a list of folders that
> > I later showing using the Treeview control.
> >
> > The data is the direct output of a DOS command: dir <Folder Name>/a:d
> > /b/s > file.txt
> >
> > I am reading in the data from the file.txt file and putting it on a
> > spreadsheet
> >
> > If I choose the Folder Name as "C:", that has 3 folders "Satish",
> > "Temp" and "Satish Work", and each folder contains one sub-folder as
> > shown below -
> > C:\Satish
> > C:\Satish Work
> > C:\Temp
> > C:\Satish\Audacity
> > C:\Satish Work\Excel
> > C:\Temp\Buttons
> >
> > Note: Each row is in one Cell, say column A.
> >
> > The above DOS command first lists the main folders and then one-by-one
> > will list the sub-folders within each main folder. But, I want
> > something like -
> > C:\Satish
> > C:\Satish\Audacity
> > C:\Satish Work
> > C:\Satish Work\Excel
> > C:\Temp
> > C:\Temp\Buttons
> >
> > I want it to list all the sub-folders within a folder before moving on
> > to the next folder. I sort of achieved it by sorting the data using:
> >
> > ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
> > Key1:=Worksheets(1).Range("A1")
> >
> > But the default sort of Excel lists the data like -
> > C:\Satish
> > C:\Satish Work
> > C:\Satish Work\Excel
> > C:\Satish\Audacity
> > C:\Temp
> > C:\Temp\Buttons
> >
> > Please note that C:\Satish\Audicity has appeared after C:\Satish
> > Work\Excel. This is because Excel sorts the data in the following
> > order -
> > 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `
> > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
> >
> > Space comes before "\". Hence I have this problem.
> >
> > Is there any way I can make Excel sort this data in the order I want
> > (as mentioned above)?
> > Please help me, because the treeview that I later use uses some logic
> > and it has to have data in the correct order.
> >
> > Thanks!
> > Satish
> >

Hi kounoike

There is a slight problem...

Infact the folder names can also contain underscores, dots and all
other valid characters that a folder name allows.

With the idea you provided, it works well for (space), but is there any
way I can handle it for underscores and other characters. The main
requirement is that "\" must precede anything else while sorting. With
that in mind, I tried replace "\" with """ (first invalid character in
the default sort order) and space with ">" . Still, it leaves scope
for characters 0 to 9 that can be part of the file name and may precede
"\" in the sort.

Anyone, any ideas?

Thanks
Satish

Satish wrote:
> Thanks a lot! I figured it out myself just after I posted the question.
> Thanks for your reply though...I checked your code works!! :-)
> Cheers!
> Satish
>
> kounoike wrote:
> > i'm not sure whether this work or not.
> > first relace \ with < , and (space) with >.
> > after sorting, relace back < with \, and > with (space)
> > this is a test code with your data.
> >
> > Sub testsort()
> > With Range("A1:A6")
> > .Replace What:="\", Replacement:="<", LookAt:=xlPart
> > .Replace What:=" ", Replacement:=">", LookAt:=xlPart
> > .Sort Key1:=Range("A1"), Order1:=xlAscending
> > .Replace What:="<", Replacement:="\", LookAt:=xlPart
> > .Replace What:=">", Replacement:=" ", LookAt:=xlPart
> > End With
> > End Sub
> >
> > keizi
> >
> > "Satish" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Hello,
> > >
> > > I have some data on a spreadsheet. The data is a list of folders that
> > > I later showing using the Treeview control.
> > >
> > > The data is the direct output of a DOS command: dir <Folder Name>/a:d
> > > /b/s > file.txt
> > >
> > > I am reading in the data from the file.txt file and putting it on a
> > > spreadsheet
> > >
> > > If I choose the Folder Name as "C:", that has 3 folders "Satish",
> > > "Temp" and "Satish Work", and each folder contains one sub-folder as
> > > shown below -
> > > C:\Satish
> > > C:\Satish Work
> > > C:\Temp
> > > C:\Satish\Audacity
> > > C:\Satish Work\Excel
> > > C:\Temp\Buttons
> > >
> > > Note: Each row is in one Cell, say column A.
> > >
> > > The above DOS command first lists the main folders and then one-by-one
> > > will list the sub-folders within each main folder. But, I want
> > > something like -
> > > C:\Satish
> > > C:\Satish\Audacity
> > > C:\Satish Work
> > > C:\Satish Work\Excel
> > > C:\Temp
> > > C:\Temp\Buttons
> > >
> > > I want it to list all the sub-folders within a folder before moving on
> > > to the next folder. I sort of achieved it by sorting the data using:
> > >
> > > ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
> > > Key1:=Worksheets(1).Range("A1")
> > >
> > > But the default sort of Excel lists the data like -
> > > C:\Satish
> > > C:\Satish Work
> > > C:\Satish Work\Excel
> > > C:\Satish\Audacity
> > > C:\Temp
> > > C:\Temp\Buttons
> > >
> > > Please note that C:\Satish\Audicity has appeared after C:\Satish
> > > Work\Excel. This is because Excel sorts the data in the following
> > > order -
> > > 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ `
> > > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
> > >
> > > Space comes before "\". Hence I have this problem.
> > >
> > > Is there any way I can make Excel sort this data in the order I want
> > > (as mentioned above)?
> > > Please help me, because the treeview that I later use uses some logic
> > > and it has to have data in the correct order.
> > >
> > > Thanks!
> > > Satish
> > >

Satish,
Why not change logic and do the directory listing in VBA (plenty of examples
available on the web).
Then you can control the listing order and will not have to correct it
after.
The listing will (I guess) be slower than the DOS output, but not much.

NickHK

"Satish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi kounoike
>
> There is a slight problem...
>
> Infact the folder names can also contain underscores, dots and all
> other valid characters that a folder name allows.
>
> With the idea you provided, it works well for (space), but is there any
> way I can handle it for underscores and other characters. The main
> requirement is that "\" must precede anything else while sorting. With
> that in mind, I tried replace "\" with """ (first invalid character in
> the default sort order) and space with ">" . Still, it leaves scope
> for characters 0 to 9 that can be part of the file name and may precede
> "\" in the sort.
>
> Anyone, any ideas?
>
> Thanks
> Satish
>
> Satish wrote:
> > Thanks a lot! I figured it out myself just after I posted the question.
> > Thanks for your reply though...I checked your code works!! :-)
> > Cheers!
> > Satish
> >
> > kounoike wrote:
> > > i'm not sure whether this work or not.
> > > first relace \ with < , and (space) with >.
> > > after sorting, relace back < with \, and > with (space)
> > > this is a test code with your data.
> > >
> > > Sub testsort()
> > > With Range("A1:A6")
> > > .Replace What:="\", Replacement:="<", LookAt:=xlPart
> > > .Replace What:=" ", Replacement:=">", LookAt:=xlPart
> > > .Sort Key1:=Range("A1"), Order1:=xlAscending
> > > .Replace What:="<", Replacement:="\", LookAt:=xlPart
> > > .Replace What:=">", Replacement:=" ", LookAt:=xlPart
> > > End With
> > > End Sub
> > >
> > > keizi
> > >
> > > "Satish" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Hello,
> > > >
> > > > I have some data on a spreadsheet. The data is a list of folders
that
> > > > I later showing using the Treeview control.
> > > >
> > > > The data is the direct output of a DOS command: dir <Folder
Name>/a:d
> > > > /b/s > file.txt
> > > >
> > > > I am reading in the data from the file.txt file and putting it on a
> > > > spreadsheet
> > > >
> > > > If I choose the Folder Name as "C:", that has 3 folders "Satish",
> > > > "Temp" and "Satish Work", and each folder contains one sub-folder as
> > > > shown below -
> > > > C:\Satish
> > > > C:\Satish Work
> > > > C:\Temp
> > > > C:\Satish\Audacity
> > > > C:\Satish Work\Excel
> > > > C:\Temp\Buttons
> > > >
> > > > Note: Each row is in one Cell, say column A.
> > > >
> > > > The above DOS command first lists the main folders and then
one-by-one
> > > > will list the sub-folders within each main folder. But, I want
> > > > something like -
> > > > C:\Satish
> > > > C:\Satish\Audacity
> > > > C:\Satish Work
> > > > C:\Satish Work\Excel
> > > > C:\Temp
> > > > C:\Temp\Buttons
> > > >
> > > > I want it to list all the sub-folders within a folder before moving
on
> > > > to the next folder. I sort of achieved it by sorting the data
using:
> > > >
> > > > ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
> > > > Key1:=Worksheets(1).Range("A1")
> > > >
> > > > But the default sort of Excel lists the data like -
> > > > C:\Satish
> > > > C:\Satish Work
> > > > C:\Satish Work\Excel
> > > > C:\Satish\Audacity
> > > > C:\Temp
> > > > C:\Temp\Buttons
> > > >
> > > > Please note that C:\Satish\Audicity has appeared after C:\Satish
> > > > Work\Excel. This is because Excel sorts the data in the following
> > > > order -
> > > > 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^
_ `
> > > > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
> > > >
> > > > Space comes before "\". Hence I have this problem.
> > > >
> > > > Is there any way I can make Excel sort this data in the order I want
> > > > (as mentioned above)?
> > > > Please help me, because the treeview that I later use uses some
logic
> > > > and it has to have data in the correct order.
> > > >
> > > > Thanks!
> > > > Satish
> > > >
>

I wish I could change that...but unfortunately, that is something not
under my control.

I personally agree with you though...

Thanks
Satish


NickHK wrote:
> Satish,
> Why not change logic and do the directory listing in VBA (plenty of examples
> available on the web).
> Then you can control the listing order and will not have to correct it
> after.
> The listing will (I guess) be slower than the DOS output, but not much.
>
> NickHK
>
> "Satish" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi kounoike
> >
> > There is a slight problem...
> >
> > Infact the folder names can also contain underscores, dots and all
> > other valid characters that a folder name allows.
> >
> > With the idea you provided, it works well for (space), but is there any
> > way I can handle it for underscores and other characters. The main
> > requirement is that "\" must precede anything else while sorting. With
> > that in mind, I tried replace "\" with """ (first invalid character in
> > the default sort order) and space with ">" . Still, it leaves scope
> > for characters 0 to 9 that can be part of the file name and may precede
> > "\" in the sort.
> >
> > Anyone, any ideas?
> >
> > Thanks
> > Satish
> >
> > Satish wrote:
> > > Thanks a lot! I figured it out myself just after I posted the question.
> > > Thanks for your reply though...I checked your code works!! :-)
> > > Cheers!
> > > Satish
> > >
> > > kounoike wrote:
> > > > i'm not sure whether this work or not.
> > > > first relace \ with < , and (space) with >.
> > > > after sorting, relace back < with \, and > with (space)
> > > > this is a test code with your data.
> > > >
> > > > Sub testsort()
> > > > With Range("A1:A6")
> > > > .Replace What:="\", Replacement:="<", LookAt:=xlPart
> > > > .Replace What:=" ", Replacement:=">", LookAt:=xlPart
> > > > .Sort Key1:=Range("A1"), Order1:=xlAscending
> > > > .Replace What:="<", Replacement:="\", LookAt:=xlPart
> > > > .Replace What:=">", Replacement:=" ", LookAt:=xlPart
> > > > End With
> > > > End Sub
> > > >
> > > > keizi
> > > >
> > > > "Satish" <(E-Mail Removed)> wrote in message
> > > > news:(E-Mail Removed)...
> > > > > Hello,
> > > > >
> > > > > I have some data on a spreadsheet. The data is a list of folders
> that
> > > > > I later showing using the Treeview control.
> > > > >
> > > > > The data is the direct output of a DOS command: dir <Folder
> Name>/a:d
> > > > > /b/s > file.txt
> > > > >
> > > > > I am reading in the data from the file.txt file and putting it on a
> > > > > spreadsheet
> > > > >
> > > > > If I choose the Folder Name as "C:", that has 3 folders "Satish",
> > > > > "Temp" and "Satish Work", and each folder contains one sub-folder as
> > > > > shown below -
> > > > > C:\Satish
> > > > > C:\Satish Work
> > > > > C:\Temp
> > > > > C:\Satish\Audacity
> > > > > C:\Satish Work\Excel
> > > > > C:\Temp\Buttons
> > > > >
> > > > > Note: Each row is in one Cell, say column A.
> > > > >
> > > > > The above DOS command first lists the main folders and then
> one-by-one
> > > > > will list the sub-folders within each main folder. But, I want
> > > > > something like -
> > > > > C:\Satish
> > > > > C:\Satish\Audacity
> > > > > C:\Satish Work
> > > > > C:\Satish Work\Excel
> > > > > C:\Temp
> > > > > C:\Temp\Buttons
> > > > >
> > > > > I want it to list all the sub-folders within a folder before moving
> on
> > > > > to the next folder. I sort of achieved it by sorting the data
> using:
> > > > >
> > > > > ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
> > > > > Key1:=Worksheets(1).Range("A1")
> > > > >
> > > > > But the default sort of Excel lists the data like -
> > > > > C:\Satish
> > > > > C:\Satish Work
> > > > > C:\Satish Work\Excel
> > > > > C:\Satish\Audacity
> > > > > C:\Temp
> > > > > C:\Temp\Buttons
> > > > >
> > > > > Please note that C:\Satish\Audicity has appeared after C:\Satish
> > > > > Work\Excel. This is because Excel sorts the data in the following
> > > > > order -
> > > > > 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^
> _ `
> > > > > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
> > > > >
> > > > > Space comes before "\". Hence I have this problem.
> > > > >
> > > > > Is there any way I can make Excel sort this data in the order I want
> > > > > (as mentioned above)?
> > > > > Please help me, because the treeview that I later use uses some
> logic
> > > > > and it has to have data in the correct order.
> > > > >
> > > > > Thanks!
> > > > > Satish
> > > > >
> >

I don't know this is the way to go. but if you want "\" must precede
anything else while sorting as you said, i would make my special sorting
which can recognize "\" as the smallest of all other characters.
using your former data, the code below seems to work in case of (space). so,
i think this should work in case of underscores and other characters. but
i'm not sure because i did'nt test this in other cases.

assuming below are in "A1:A6"
C:\Satish
C:\Satish Work
C:\Temp
C:\Satish\Audacity
C:\Satish Work\Excel
C:\Temp\Buttons

"sorttest" will produce the sorting result in "B1:B6".
C:\Satish
C:\Satish\Audacity
C:\Satish Work
C:\Satish Work\Excel
C:\Temp
C:\Temp\Buttons

Sub sorttest()
Dim a
a = Range("a1:a6")
qsort2md a, 1, 6
Range("b1:b6") = a
End Sub

Sub qsort2md(v As Variant, ByVal left As Long, ByVal right As Long)
'translated from qsort, Brian W.Kernighan and Dennis M.Ritchie,
'The C Programming Language 2nd Japanese version", page 106
Dim i As Long, last As Long

If left >= right Then
Exit Sub
End If
swapm2 v, left, (left + right) \ 2
last = left
i = left + 1
Do While (i <= right)
If compstr2(v, i, left) < 0 Then
last = last + 1
swapm2 v, last, i
End If
i = i + 1
Loop
swapm2 v, left, (last)
qsort2md v, left, (last - 1)
qsort2md v, (last + 1), right
End Sub

Sub swapm2(v As Variant, ByVal i As Long, ByVal j As Long)
'translated from qsort, Brian W.Kernighan and Dennis M.Ritchie,
'The C Programming Language 2nd Japanese version", page 106
Dim tmp
tmp = v(i, 1)
v(i, 1) = v(j, 1)
v(j, 1) = tmp
End Sub

Function compstr2(v As Variant, ByVal i As Long, ByVal j As Long) As Long
Dim UL As Long, k As Long, m As Long

If Len(v(i, 1)) > Len(v(j, 1)) Then
UL = Len(v(j, 1))
m = 1
ElseIf Len(v(i, 1)) < Len(v(j, 1)) Then
UL = Len(v(i, 1))
m = -1
Else
UL = Len(v(i, 1))
m = 0
End If

For k = LBound(v, 1) To UL
If Mid(v(i, 1), k, 1) <> Mid(v(j, 1), k, 1) Then
If Mid(v(i, 1), k, 1) = "\" Then
compstr2 = -1
Exit Function
ElseIf Mid(v(j, 1), k, 1) = "\" Then
compstr2 = 1
Exit Function
ElseIf Mid(v(i, 1), k, 1) > Mid(v(j, 1), k, 1) Then
compstr2 = 1
Exit Function
ElseIf Mid(v(i, 1), k, 1) < Mid(v(j, 1), k, 1) Then
compstr2 = -1
Exit Function
End If
End If
Next
compstr2 = m
End Function

keizi

"Satish" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Hi kounoike
>
> There is a slight problem...
>
> Infact the folder names can also contain underscores, dots and all
> other valid characters that a folder name allows.
>
> With the idea you provided, it works well for (space), but is there any
> way I can handle it for underscores and other characters. The main
> requirement is that "\" must precede anything else while sorting. With
> that in mind, I tried replace "\" with """ (first invalid character in
> the default sort order) and space with ">" . Still, it leaves scope
> for characters 0 to 9 that can be part of the file name and may precede
> "\" in the sort.
>
> Anyone, any ideas?
>
> Thanks
> Satish
>
> Satish wrote:
>> Thanks a lot! I figured it out myself just after I posted the question.
>> Thanks for your reply though...I checked your code works!! :-)
>> Cheers!
>> Satish
>>
>> kounoike wrote:
>> > i'm not sure whether this work or not.
>> > first relace \ with < , and (space) with >.
>> > after sorting, relace back < with \, and > with (space)
>> > this is a test code with your data.
>> >
>> > Sub testsort()
>> > With Range("A1:A6")
>> > .Replace What:="\", Replacement:="<", LookAt:=xlPart
>> > .Replace What:=" ", Replacement:=">", LookAt:=xlPart
>> > .Sort Key1:=Range("A1"), Order1:=xlAscending
>> > .Replace What:="<", Replacement:="\", LookAt:=xlPart
>> > .Replace What:=">", Replacement:=" ", LookAt:=xlPart
>> > End With
>> > End Sub
>> >
>> > keizi
>> >
>> > "Satish" <(E-Mail Removed)> wrote in message
>> > news:(E-Mail Removed)...
>> > > Hello,
>> > >
>> > > I have some data on a spreadsheet. The data is a list of folders
>> > > that
>> > > I later showing using the Treeview control.
>> > >
>> > > The data is the direct output of a DOS command: dir <Folder
>> > > Name>/a:d
>> > > /b/s > file.txt
>> > >
>> > > I am reading in the data from the file.txt file and putting it on a
>> > > spreadsheet
>> > >
>> > > If I choose the Folder Name as "C:", that has 3 folders "Satish",
>> > > "Temp" and "Satish Work", and each folder contains one sub-folder as
>> > > shown below -
>> > > C:\Satish
>> > > C:\Satish Work
>> > > C:\Temp
>> > > C:\Satish\Audacity
>> > > C:\Satish Work\Excel
>> > > C:\Temp\Buttons
>> > >
>> > > Note: Each row is in one Cell, say column A.
>> > >
>> > > The above DOS command first lists the main folders and then
>> > > one-by-one
>> > > will list the sub-folders within each main folder. But, I want
>> > > something like -
>> > > C:\Satish
>> > > C:\Satish\Audacity
>> > > C:\Satish Work
>> > > C:\Satish Work\Excel
>> > > C:\Temp
>> > > C:\Temp\Buttons
>> > >
>> > > I want it to list all the sub-folders within a folder before moving
>> > > on
>> > > to the next folder. I sort of achieved it by sorting the data using:
>> > >
>> > > ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
>> > > Key1:=Worksheets(1).Range("A1")
>> > >
>> > > But the default sort of Excel lists the data like -
>> > > C:\Satish
>> > > C:\Satish Work
>> > > C:\Satish Work\Excel
>> > > C:\Satish\Audacity
>> > > C:\Temp
>> > > C:\Temp\Buttons
>> > >
>> > > Please note that C:\Satish\Audicity has appeared after C:\Satish
>> > > Work\Excel. This is because Excel sorts the data in the following
>> > > order -
>> > > 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _
>> > > `
>> > > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
>> > >
>> > > Space comes before "\". Hence I have this problem.
>> > >
>> > > Is there any way I can make Excel sort this data in the order I want
>> > > (as mentioned above)?
>> > > Please help me, because the treeview that I later use uses some logic
>> > > and it has to have data in the correct order.
>> > >
>> > > Thanks!
>> > > Satish
>> > >
>

Whoa! That certainly looks to be some complex logic in there...! I
will certainly try it out...thanks so much for the effort!!

-Satish

kounoike wrote:
> I don't know this is the way to go. but if you want "\" must precede
> anything else while sorting as you said, i would make my special sorting
> which can recognize "\" as the smallest of all other characters.
> using your former data, the code below seems to work in case of (space). so,
> i think this should work in case of underscores and other characters. but
> i'm not sure because i did'nt test this in other cases.
>
> assuming below are in "A1:A6"
> C:\Satish
> C:\Satish Work
> C:\Temp
> C:\Satish\Audacity
> C:\Satish Work\Excel
> C:\Temp\Buttons
>
> "sorttest" will produce the sorting result in "B1:B6".
> C:\Satish
> C:\Satish\Audacity
> C:\Satish Work
> C:\Satish Work\Excel
> C:\Temp
> C:\Temp\Buttons
>
> Sub sorttest()
> Dim a
> a = Range("a1:a6")
> qsort2md a, 1, 6
> Range("b1:b6") = a
> End Sub
>
> Sub qsort2md(v As Variant, ByVal left As Long, ByVal right As Long)
> 'translated from qsort, Brian W.Kernighan and Dennis M.Ritchie,
> 'The C Programming Language 2nd Japanese version", page 106
> Dim i As Long, last As Long
>
> If left >= right Then
> Exit Sub
> End If
> swapm2 v, left, (left + right) \ 2
> last = left
> i = left + 1
> Do While (i <= right)
> If compstr2(v, i, left) < 0 Then
> last = last + 1
> swapm2 v, last, i
> End If
> i = i + 1
> Loop
> swapm2 v, left, (last)
> qsort2md v, left, (last - 1)
> qsort2md v, (last + 1), right
> End Sub
>
> Sub swapm2(v As Variant, ByVal i As Long, ByVal j As Long)
> 'translated from qsort, Brian W.Kernighan and Dennis M.Ritchie,
> 'The C Programming Language 2nd Japanese version", page 106
> Dim tmp
> tmp = v(i, 1)
> v(i, 1) = v(j, 1)
> v(j, 1) = tmp
> End Sub
>
> Function compstr2(v As Variant, ByVal i As Long, ByVal j As Long) As Long
> Dim UL As Long, k As Long, m As Long
>
> If Len(v(i, 1)) > Len(v(j, 1)) Then
> UL = Len(v(j, 1))
> m = 1
> ElseIf Len(v(i, 1)) < Len(v(j, 1)) Then
> UL = Len(v(i, 1))
> m = -1
> Else
> UL = Len(v(i, 1))
> m = 0
> End If
>
> For k = LBound(v, 1) To UL
> If Mid(v(i, 1), k, 1) <> Mid(v(j, 1), k, 1) Then
> If Mid(v(i, 1), k, 1) = "\" Then
> compstr2 = -1
> Exit Function
> ElseIf Mid(v(j, 1), k, 1) = "\" Then
> compstr2 = 1
> Exit Function
> ElseIf Mid(v(i, 1), k, 1) > Mid(v(j, 1), k, 1) Then
> compstr2 = 1
> Exit Function
> ElseIf Mid(v(i, 1), k, 1) < Mid(v(j, 1), k, 1) Then
> compstr2 = -1
> Exit Function
> End If
> End If
> Next
> compstr2 = m
> End Function
>
> keizi
>
> "Satish" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Hi kounoike
> >
> > There is a slight problem...
> >
> > Infact the folder names can also contain underscores, dots and all
> > other valid characters that a folder name allows.
> >
> > With the idea you provided, it works well for (space), but is there any
> > way I can handle it for underscores and other characters. The main
> > requirement is that "\" must precede anything else while sorting. With
> > that in mind, I tried replace "\" with """ (first invalid character in
> > the default sort order) and space with ">" . Still, it leaves scope
> > for characters 0 to 9 that can be part of the file name and may precede
> > "\" in the sort.
> >
> > Anyone, any ideas?
> >
> > Thanks
> > Satish
> >
> > Satish wrote:
> >> Thanks a lot! I figured it out myself just after I posted the question.
> >> Thanks for your reply though...I checked your code works!! :-)
> >> Cheers!
> >> Satish
> >>
> >> kounoike wrote:
> >> > i'm not sure whether this work or not.
> >> > first relace \ with < , and (space) with >.
> >> > after sorting, relace back < with \, and > with (space)
> >> > this is a test code with your data.
> >> >
> >> > Sub testsort()
> >> > With Range("A1:A6")
> >> > .Replace What:="\", Replacement:="<", LookAt:=xlPart
> >> > .Replace What:=" ", Replacement:=">", LookAt:=xlPart
> >> > .Sort Key1:=Range("A1"), Order1:=xlAscending
> >> > .Replace What:="<", Replacement:="\", LookAt:=xlPart
> >> > .Replace What:=">", Replacement:=" ", LookAt:=xlPart
> >> > End With
> >> > End Sub
> >> >
> >> > keizi
> >> >
> >> > "Satish" <(E-Mail Removed)> wrote in message
> >> > news:(E-Mail Removed)...
> >> > > Hello,
> >> > >
> >> > > I have some data on a spreadsheet. The data is a list of folders
> >> > > that
> >> > > I later showing using the Treeview control.
> >> > >
> >> > > The data is the direct output of a DOS command: dir <Folder
> >> > > Name>/a:d
> >> > > /b/s > file.txt
> >> > >
> >> > > I am reading in the data from the file.txt file and putting it on a
> >> > > spreadsheet
> >> > >
> >> > > If I choose the Folder Name as "C:", that has 3 folders "Satish",
> >> > > "Temp" and "Satish Work", and each folder contains one sub-folder as
> >> > > shown below -
> >> > > C:\Satish
> >> > > C:\Satish Work
> >> > > C:\Temp
> >> > > C:\Satish\Audacity
> >> > > C:\Satish Work\Excel
> >> > > C:\Temp\Buttons
> >> > >
> >> > > Note: Each row is in one Cell, say column A.
> >> > >
> >> > > The above DOS command first lists the main folders and then
> >> > > one-by-one
> >> > > will list the sub-folders within each main folder. But, I want
> >> > > something like -
> >> > > C:\Satish
> >> > > C:\Satish\Audacity
> >> > > C:\Satish Work
> >> > > C:\Satish Work\Excel
> >> > > C:\Temp
> >> > > C:\Temp\Buttons
> >> > >
> >> > > I want it to list all the sub-folders within a folder before moving
> >> > > on
> >> > > to the next folder. I sort of achieved it by sorting the data using:
> >> > >
> >> > > ThisWorkbook.Worksheets(1).Range("A1:A6").Sort _
> >> > > Key1:=Worksheets(1).Range("A1")
> >> > >
> >> > > But the default sort of Excel lists the data like -
> >> > > C:\Satish
> >> > > C:\Satish Work
> >> > > C:\Satish Work\Excel
> >> > > C:\Satish\Audacity
> >> > > C:\Temp
> >> > > C:\Temp\Buttons
> >> > >
> >> > > Please note that C:\Satish\Audicity has appeared after C:\Satish
> >> > > Work\Excel. This is because Excel sorts the data in the following
> >> > > order -
> >> > > 0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _
> >> > > `
> >> > > { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
> >> > >
> >> > > Space comes before "\". Hence I have this problem.
> >> > >
> >> > > Is there any way I can make Excel sort this data in the order I want
> >> > > (as mentioned above)?
> >> > > Please help me, because the treeview that I later use uses some logic
> >> > > and it has to have data in the correct order.
> >> > >
> >> > > Thanks!
> >> > > Satish
> >> > >
> >