The following block of code works absolutly fine when in a workbook by
itself, however, when it is part of the module it is intened for it creates
an error on the execute line!

"runtime error 5"
"Invalid procedure call"

I don't know why this is happening and would like an explination or a
pointer to why this maybe happening

If I remove everything in the line beyond Ececute it works fine.

When the error is occuring hovering the mouse over msoSortBy....... gives a
value of Empty. This should not be the case as the code works fine in
another workbook!



With Application.FileSearch
.LookIn = "C:\shop\hist\data"
.SearchSubFolders = False
.FileName = "reple*.xls"
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
temp = .FoundFiles(.FoundFiles.Count)
End With

Your code worked ok for me in xl2002 and win98.

I'm not sure why msoSortByFileName would return Empty. Did you use that as a
variable in your program. (take a peek to check.)

Try changing msoSortByFileName to 1 (one) to see if that "fixes" it until you
find out why it's empty.



Stuart wrote:

The following block of code works absolutly fine when in a workbook by
itself, however, when it is part of the module it is intened for it creates
an error on the execute line!

"runtime error 5"
"Invalid procedure call"

I don't know why this is happening and would like an explination or a
pointer to why this maybe happening

If I remove everything in the line beyond Ececute it works fine.

When the error is occuring hovering the mouse over msoSortBy....... gives a
value of Empty. This should not be the case as the code works fine in
another workbook!

With Application.FileSearch
.LookIn = "C:\shop\hist\data"
.SearchSubFolders = False
.FileName = "reple*.xls"
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
temp = .FoundFiles(.FoundFiles.Count)
End With

--

Dave Peterson

No it hasn't been used as a variable nor has it even been used elsewhere!

Not quite sure what you meant by changing "msoSortByFileName to 1 (one)" I
tried running the line msoSortByFileName=1 immediately before the problem
line, however it made no difference, I still get the same runTime error!



setting it to 1 makes no differance
"Dave Peterson" wrote in message
...
Your code worked ok for me in xl2002 and win98.

I'm not sure why msoSortByFileName would return Empty. Did you use that
as a
variable in your program. (take a peek to check.)

Try changing msoSortByFileName to 1 (one) to see if that "fixes" it until
you
find out why it's empty.



Stuart wrote:

The following block of code works absolutly fine when in a workbook by
itself, however, when it is part of the module it is intened for it
creates
an error on the execute line!

"runtime error 5"
"Invalid procedure call"

I don't know why this is happening and would like an explination or a
pointer to why this maybe happening

If I remove everything in the line beyond Ececute it works fine.

When the error is occuring hovering the mouse over msoSortBy.......
gives a
value of Empty. This should not be the case as the code works fine in
another workbook!

With Application.FileSearch
.LookIn = "C:\shop\hist\data"
.SearchSubFolders = False
.FileName = "reple*.xls"
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
temp = .FoundFiles(.FoundFiles.Count)
End With

--

Dave Peterson

..Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
becomes
..Execute SortBy:=1, SortOrder:=msoSortOrderAscending

If you hit ctrl-g to see the immediate window, you can type:

?msoSortByFileName
and hit enter
to see what that constant is defined as.

Try it with msoSortOrderAscending

But that doesn't help why you're having the problem to begin with.

If you look at Tools|References, do you have anything marked MISSING.

If you look at tools|References in the workbook that works, do you have anything
that's "extra". If you do, try checking that reference in the problem workbook.

Stuart wrote:

No it hasn't been used as a variable nor has it even been used elsewhere!

Not quite sure what you meant by changing "msoSortByFileName to 1 (one)" I
tried running the line msoSortByFileName=1 immediately before the problem
line, however it made no difference, I still get the same runTime error!

setting it to 1 makes no differance
"Dave Peterson" wrote in message
...
Your code worked ok for me in xl2002 and win98.

I'm not sure why msoSortByFileName would return Empty. Did you use that
as a
variable in your program. (take a peek to check.)

Try changing msoSortByFileName to 1 (one) to see if that "fixes" it until
you
find out why it's empty.



Stuart wrote:

The following block of code works absolutly fine when in a workbook by
itself, however, when it is part of the module it is intened for it
creates
an error on the execute line!

"runtime error 5"
"Invalid procedure call"

I don't know why this is happening and would like an explination or a
pointer to why this maybe happening

If I remove everything in the line beyond Ececute it works fine.

When the error is occuring hovering the mouse over msoSortBy.......
gives a
value of Empty. This should not be the case as the code works fine in
another workbook!

With Application.FileSearch
.LookIn = "C:\shop\hist\data"
.SearchSubFolders = False
.FileName = "reple*.xls"
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
temp = .FoundFiles(.FoundFiles.Count)
End With

--

Dave Peterson

--

Dave Peterson

"Dave Peterson" wrote in message
...
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
becomes
.Execute SortBy:=1, SortOrder:=msoSortOrderAscending

If you hit ctrl-g to see the immediate window, you can type:

?msoSortByFileName
and hit enter
to see what that constant is defined as.

Try it with msoSortOrderAscending

But that doesn't help why you're having the problem to begin with.

If you look at Tools|References, do you have anything marked MISSING.

If you look at tools|References in the workbook that works, do you have
anything
that's "extra". If you do, try checking that reference in the problem
workbook.

Stuart wrote:

No it hasn't been used as a variable nor has it even been used
elsewhere!

Not quite sure what you meant by changing "msoSortByFileName to 1 (one)"
I
tried running the line msoSortByFileName=1 immediately before the
problem
line, however it made no difference, I still get the same runTime error!

setting it to 1 makes no differance
"Dave Peterson" wrote in message
...
Your code worked ok for me in xl2002 and win98.

I'm not sure why msoSortByFileName would return Empty. Did you use
that
as a
variable in your program. (take a peek to check.)

Try changing msoSortByFileName to 1 (one) to see if that "fixes" it
until
you
find out why it's empty.



Stuart wrote:

The following block of code works absolutly fine when in a workbook
by
itself, however, when it is part of the module it is intened for it
creates
an error on the execute line!

"runtime error 5"
"Invalid procedure call"

I don't know why this is happening and would like an explination or
a
pointer to why this maybe happening

If I remove everything in the line beyond Ececute it works fine.

When the error is occuring hovering the mouse over msoSortBy.......
gives a
value of Empty. This should not be the case as the code works fine
in
another workbook!

With Application.FileSearch
.LookIn = "C:\shop\hist\data"
.SearchSubFolders = False
.FileName = "reple*.xls"
.Execute SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending
temp = .FoundFiles(.FoundFiles.Count)
End With

--

Dave Peterson

--

Dave Peterson

Many thanks Dave for your help, "Microsoft Office 8.0 Object Libary" was not
ticked. Why it was not ticked in this particular workbook I have no idea, as
it is selected in every other workbook.

again, many thanks

Stuart


"Dave Peterson" wrote in message
...
.Execute SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending
becomes
.Execute SortBy:=1, SortOrder:=msoSortOrderAscending

If you hit ctrl-g to see the immediate window, you can type:

?msoSortByFileName
and hit enter
to see what that constant is defined as.

Try it with msoSortOrderAscending

But that doesn't help why you're having the problem to begin with.

If you look at Tools|References, do you have anything marked MISSING.

If you look at tools|References in the workbook that works, do you have
anything
that's "extra". If you do, try checking that reference in the problem
workbook.

Stuart wrote:

No it hasn't been used as a variable nor has it even been used
elsewhere!

Not quite sure what you meant by changing "msoSortByFileName to 1 (one)"
I
tried running the line msoSortByFileName=1 immediately before the
problem
line, however it made no difference, I still get the same runTime error!

setting it to 1 makes no differance
"Dave Peterson" wrote in message
...
Your code worked ok for me in xl2002 and win98.

I'm not sure why msoSortByFileName would return Empty. Did you use
that
as a
variable in your program. (take a peek to check.)

Try changing msoSortByFileName to 1 (one) to see if that "fixes" it
until
you
find out why it's empty.



Stuart wrote:

The following block of code works absolutly fine when in a workbook
by
itself, however, when it is part of the module it is intened for it
creates
an error on the execute line!

"runtime error 5"
"Invalid procedure call"

I don't know why this is happening and would like an explination or
a
pointer to why this maybe happening

If I remove everything in the line beyond Ececute it works fine.

When the error is occuring hovering the mouse over msoSortBy.......
gives a
value of Empty. This should not be the case as the code works fine
in
another workbook!

With Application.FileSearch
.LookIn = "C:\shop\hist\data"
.SearchSubFolders = False
.FileName = "reple*.xls"
.Execute SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending
temp = .FoundFiles(.FoundFiles.Count)
End With

--

Dave Peterson

--

Dave Peterson