Sorting with DIR function
Sorting with DIR function
am 29.12.2007 13:29:22 von anthony
What sort order does the DIR function use? I can't see any options
within the function to force it to sort in a particular order. Does
the sort order depend on the Windows Explorer setting for that folder?
Re: Sorting with DIR function
am 29.12.2007 13:37:22 von Baz
From Access 2003 help for the Dir function:
"Because file names are retrieved in no particular order, you may want to
store returned file names in an array, and then sort the array."
"anthony" wrote in message
news:1e63cf1b-8c67-43ff-8705-dd652c15bfc5@v4g2000hsf.googleg roups.com...
> What sort order does the DIR function use? I can't see any options
> within the function to force it to sort in a particular order. Does
> the sort order depend on the Windows Explorer setting for that folder?
Re: Sorting with DIR function
am 29.12.2007 14:18:11 von anthony
Ah, that's hiden away in the Access 2007 help under "Tip", which is
why I missed it. Many thanks
Actually, I'm going to need help with this because I really don't know
what an array is. I'm thinking in terms of putting the filenames into
a temporary table, sorting that and then processing them. Is that much
the same thing?
Re: Sorting with DIR function
am 29.12.2007 14:34:37 von Baz
Not really, no. A table will be in the Access file (i.e. it will be on the
disk) whereas an array exists only in memory. In theory, then, it should be
much quicker to create and sort an array, but you would have to "roll your
own" sort by writing a pile of code to do it (although a bit of Googling
would probably reveal loads of array sorts that people have already written
in VB). With your table idea, all you need to do is to query the table with
an appropriate ORDER BY clause.
Another in-memory option is a Collection object. Unlike an array, these
allow you to insert new items in between existing items, so with a little
bit of ingenuity you could effectively sort your collection while you were
creating it, which would obviate the need to implement a complex sort
algorithm.
"anthony" wrote in message
news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
> Ah, that's hiden away in the Access 2007 help under "Tip", which is
> why I missed it. Many thanks
>
> Actually, I'm going to need help with this because I really don't know
> what an array is. I'm thinking in terms of putting the filenames into
> a temporary table, sorting that and then processing them. Is that much
> the same thing?
>
Re: Sorting with DIR function
am 29.12.2007 14:54:22 von anthony
Your help is much appreciated. Many thanks
Re: Sorting with DIR function
am 29.12.2007 15:08:16 von Stuart McCall
"anthony" wrote in message
news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
> Ah, that's hiden away in the Access 2007 help under "Tip", which is
> why I missed it. Many thanks
>
> Actually, I'm going to need help with this because I really don't know
> what an array is. I'm thinking in terms of putting the filenames into
> a temporary table, sorting that and then processing them. Is that much
> the same thing?
If you'd like to explore array creation and sorting, try this:
http://www.groupacg.com/files/sorts01.zip
It's written in Access 97 but will convert without change to a later version
(not tested under Access 2007 yet).
Re: Sorting with DIR function
am 29.12.2007 17:21:20 von lyle
On Dec 29, 9:08 am, "Stuart McCall" wrote:
> "anthony" wrote in message
>
> news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
>
> > Ah, that's hiden away in the Access 2007 help under "Tip", which is
> > why I missed it. Many thanks
>
> > Actually, I'm going to need help with this because I really don't know
> > what an array is. I'm thinking in terms of putting the filenames into
> > a temporary table, sorting that and then processing them. Is that much
> > the same thing?
>
> If you'd like to explore array creation and sorting, try this:
>
> http://www.groupacg.com/files/sorts01.zip
>
> It's written in Access 97 but will convert without change to a later version
> (not tested under Access 2007 yet).
Sub APostFromYearsAgo()
Dim s As String
Dim a() As String
s = s & "Some File.ext"
s = s & ";"
s = s & "1 File.ext"
s = s & ";"
s = s & "A File.ext"
a = Split(s, ";")
WizHook.SortStringArray a
s = Join(a, ";")
Debug.Print s
'1 File.ext;A File.ext;Some File.ext
End Sub
Re: Sorting with DIR function
am 29.12.2007 17:40:45 von Stuart McCall
> Sub APostFromYearsAgo()
> Dim s As String
> Dim a() As String
> s = s & "Some File.ext"
> s = s & ";"
> s = s & "1 File.ext"
> s = s & ";"
> s = s & "A File.ext"
> a = Split(s, ";")
> WizHook.SortStringArray a
> s = Join(a, ";")
> Debug.Print s
> '1 File.ext;A File.ext;Some File.ext
> End Sub
Interesting. I've not run across this before. It doesn't load a wizard
library, either. Thanks for that.
Re: Sorting with DIR function
am 30.12.2007 16:25:55 von anthony
TVM Stuart and Lyle
Re: Sorting with DIR function
am 30.12.2007 17:59:15 von Tom van Stiphout
On Sat, 29 Dec 2007 08:21:20 -0800 (PST), lyle
wrote:
Still works in A2007.
Code window > F2 to open the Object Browser > RightClick in the
Members section and select Show Hidden Members > Search for WizHook.
-Tom.
>On Dec 29, 9:08 am, "Stuart McCall" wrote:
>> "anthony" wrote in message
>>
>> news:0a1b946a-ccac-4630-ae45-61455b06642f@n20g2000hsh.google groups.com...
>>
>> > Ah, that's hiden away in the Access 2007 help under "Tip", which is
>> > why I missed it. Many thanks
>>
>> > Actually, I'm going to need help with this because I really don't know
>> > what an array is. I'm thinking in terms of putting the filenames into
>> > a temporary table, sorting that and then processing them. Is that much
>> > the same thing?
>>
>> If you'd like to explore array creation and sorting, try this:
>>
>> http://www.groupacg.com/files/sorts01.zip
>>
>> It's written in Access 97 but will convert without change to a later version
>> (not tested under Access 2007 yet).
>
>Sub APostFromYearsAgo()
> Dim s As String
> Dim a() As String
> s = s & "Some File.ext"
> s = s & ";"
> s = s & "1 File.ext"
> s = s & ";"
> s = s & "A File.ext"
> a = Split(s, ";")
> WizHook.SortStringArray a
> s = Join(a, ";")
> Debug.Print s
> '1 File.ext;A File.ext;Some File.ext
>End Sub
Re: Sorting with DIR function
am 30.12.2007 18:19:32 von lyle
On Dec 30, 11:59 am, Tom van Stiphout wrote:
> Still works in A2007.
> Code window > F2 to open the Object Browser > RightClick in the
> Members section and select Show Hidden Members > Search for WizHook.
Wizhook has lotsa stuff that might be useful, such as a bold-line
enabled message box (long gone from normal VBA calls, it seems).
WizHook.WizMsgBox ".Key = 51488399 @Not so much lately!@", _
"Used to Need", vbOK, 0, ""
Re: Sorting with DIR function
am 30.12.2007 19:03:26 von Tom van Stiphout
On Sun, 30 Dec 2007 09:19:32 -0800 (PST), lyle
wrote:
Curiously this one doesn't seem to be working anymore in A2007. No
error message, nothing.
Later found out that the ".Key=51488399" should be set on the WizHook
object, like this:
Public Sub blahblah()
With WizHook
..Key = 51488399
..WizMsgBox "Bold Line @ @ Not So Bold Line", _
"The Caption", vbYesNo, 23, "Path To Some Help File"
End With
End Sub
-Tom.
>WizHook.WizMsgBox ".Key = 51488399 @Not so much lately!@", _
> "Used to Need", vbOK, 0, ""
Re: Sorting with DIR function
am 30.12.2007 20:17:46 von lyle
On Dec 30, 1:03 pm, Tom van Stiphout wrote:
> On Sun, 30 Dec 2007 09:19:32 -0800 (PST), lyle
>
> wrote:
>
> Curiously this one doesn't seem to be working anymore in A2007. No
> error message, nothing.
>
> Later found out that the ".Key=51488399" should be set on the WizHook
> object, like this:
> Public Sub blahblah()
> With WizHook
> .Key = 51488399
> .WizMsgBox "Bold Line @ @ Not So Bold Line", _
> "The Caption", vbYesNo, 23, "Path To Some Help File"
> End With
> End Sub
>
> -Tom.
>
> >WizHook.WizMsgBox ".Key = 51488399 @Not so much lately!@", _
> > "Used to Need", vbOK, 0, ""
WizHook.Key = 51488399
Seems to enable Wizhook globally for the duration of the application
instance. This confused me and I thought that WizHook.Key = 51488399
was no longer required at all.
Strangely!? Wizhook stays enabled even after an runtime error.