Sub cannot run (Error 5)
 

I'm trying to run the sub below (using Excel 97, Win XP),
but kept hitting:

Invalid procedure call or argument (Error 5)

Debug pointed at this line:
.FileName = "*.*"

The number of files in the folder: D:\Program Files\Erlang\CalcA
is less than 50

-------
Sub ListFiles02()
'Using FileSearch to list the files in a directory
Dim i As Integer
i = 1
With Application.FileSearch
.NewSearch
.LookIn = "D:\Program Files\Erlang\CalcA"
.FileName = "*.*"
.SearchSubFolders = True
'.SearchFolders = False
.Execute

For i = 1 To .FoundFiles.Count
Range("B" & i).Value = .FoundFiles(i)
Range("C" & i).Value = FileDateTime(.FoundFiles(i))
Range("D" & i).Value = FileLen(.FoundFiles(i))
Next
End With
End Sub
-------

Appreciate any insights. Thanks.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

as it is, the code works fine for me. Try looking in another drive - D is
often used for CD/DVD


"Max" wrote:

I'm trying to run the sub below (using Excel 97, Win XP),
but kept hitting:

Invalid procedure call or argument (Error 5)

Debug pointed at this line:
.FileName = "*.*"

The number of files in the folder: D:\Program Files\Erlang\CalcA
is less than 50

-------
Sub ListFiles02()
'Using FileSearch to list the files in a directory
Dim i As Integer
i = 1
With Application.FileSearch
.NewSearch
.LookIn = "D:\Program Files\Erlang\CalcA"
.FileName = "*.*"
.SearchSubFolders = True
'.SearchFolders = False
.Execute

For i = 1 To .FoundFiles.Count
Range("B" & i).Value = .FoundFiles(i)
Range("C" & i).Value = FileDateTime(.FoundFiles(i))
Range("D" & i).Value = FileLen(.FoundFiles(i))
Next
End With
End Sub
-------

Appreciate any insights. Thanks.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

Thanks, Patrick, but still no go here, I'm afraid. Tried putting in other
valid drives / paths, all attempts failed to run. Same error msg, same debug
line.

Even tried changing the debug line to say,:
..FileName = "*.bmp"
but it still failed to clear this line
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Patrick Molloy" wrote in message
...
as it is, the code works fine for me.
Try looking in another drive - D is
often used for CD/DVD

Sub cannot run (Error 5)
 

try

.FileName = "*"


"Max" wrote:

Thanks, Patrick, but still no go here, I'm afraid. Tried putting in other
valid drives / paths, all attempts failed to run. Same error msg, same debug
line.

Even tried changing the debug line to say,:
..FileName = "*.bmp"
but it still failed to clear this line
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Patrick Molloy" wrote in message
...
as it is, the code works fine for me.
Try looking in another drive - D is
often used for CD/DVD

Sub cannot run (Error 5)
 

Tried that, but it still hung at the same line
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Patrick Molloy" wrote in message
...
try

.FileName = "*"

Sub cannot run (Error 5)
 

"Max" wrote in message
...

your code works with no problem for me.
How about opening new workbook, and copy your posted code to the new
workbook, and then try it on the new workbook?

keizi

-------
Sub ListFiles02()
'Using FileSearch to list the files in a directory
Dim i As Integer
i = 1
With Application.FileSearch
.NewSearch
.LookIn = "D:\Program Files\Erlang\CalcA"
.FileName = "*.*"
.SearchSubFolders = True
'.SearchFolders = False
.Execute

For i = 1 To .FoundFiles.Count
Range("B" & i).Value = .FoundFiles(i)
Range("C" & i).Value = FileDateTime(.FoundFiles(i))
Range("D" & i).Value = FileLen(.FoundFiles(i))
Next
End With
End Sub

Sub cannot run (Error 5)
 

Thanks, Keizi. Tried as you suggested, but it still fails, same error, same
line.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"kounoike" wrote in message
...
"Max" wrote in message
...

your code works with no problem for me.
How about opening new workbook, and copy your posted code to the new
workbook, and then try it on the new workbook?

keizi

Sub cannot run (Error 5)
 

Are you logged on as local administrator? Code 5 generally = "Access is
denied" Also try another machine.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Max" wrote:
| I'm trying to run the sub below (using Excel 97, Win XP),
| but kept hitting:
|
| Invalid procedure call or argument (Error 5)
|
| Debug pointed at this line:
| .FileName = "*.*"
|
| The number of files in the folder: D:\Program Files\Erlang\CalcA
| is less than 50
|
| -------
| Sub ListFiles02()
| 'Using FileSearch to list the files in a directory
| Dim i As Integer
| i = 1
| With Application.FileSearch
| .NewSearch
| .LookIn = "D:\Program Files\Erlang\CalcA"
| .FileName = "*.*"
| .SearchSubFolders = True
| '.SearchFolders = False
| .Execute
|
| For i = 1 To .FoundFiles.Count
| Range("B" & i).Value = .FoundFiles(i)
| Range("C" & i).Value = FileDateTime(.FoundFiles(i))
| Range("D" & i).Value = FileLen(.FoundFiles(i))
| Next
| End With
| End Sub
| -------
|
| Appreciate any insights. Thanks.
|
| --
| Rgds
| Max
| xl 97
| ---
| Singapore, GMT+8
| xdemechanik
| http://savefile.com/projects/236895
| --
|
|

Sub cannot run (Error 5)
 

I'm stumped??, just tested, the sub runs ok on my home PC: Excel 97, Win 98.
But it continues to fail in the other, earlier environ: Excel 97, Win XP
Is there anything else I can try to make it work over there ?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

As responsed further in the other branch ... just tested, the sub runs ok on
my home PC: Excel 97, Win 98. But it continues to fail in the other, earlier
environ: Excel 97, Win XP. Is there anything else I can try to make it work
over there ?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

Dave, thanks for insights. Just tested, the sub runs ok on my home PC: Excel
97, Win 98. But it continues to fail in the other, earlier environ: Excel
97, Win XP

Are you logged on as local administrator?
How do I check this in Win XP ?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dave Patrick" wrote in message
...
Are you logged on as local administrator? Code 5 generally = "Access is
denied" Also try another machine.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Sub cannot run (Error 5)
 

Start|Run and type

control userpasswords2

then properties for your user account.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Max" wrote:
| Dave, thanks for insights. Just tested, the sub runs ok on my home PC:
Excel
| 97, Win 98. But it continues to fail in the other, earlier environ: Excel
| 97, Win XP
|
| Are you logged on as local administrator?
| How do I check this in Win XP ?
| --
| Rgds
| Max
| xl 97
| ---
| Singapore, GMT+8
| xdemechanik
| http://savefile.com/projects/236895
| --

Sub cannot run (Error 5)
 

Thanks, I'll have to try this at the office m/c tomorrow.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dave Patrick" wrote in message
...
Start|Run and type

control userpasswords2

then properties for your user account.

Sub cannot run (Error 5)
 

You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Max" wrote:
| Thanks, I'll have to try this at the office m/c tomorrow.
| --
| Rgds
| Max
| xl 97
| ---
| Singapore, GMT+8
| xdemechanik
| http://savefile.com/projects/236895

Sub cannot run (Error 5)
 

Ok, these were my user properties listed:
Debugger Users; Tivoli_Admin_Privileges; Administrators; Users
(I don't really know how to intrepret the properties <g)

The sub still stubbornly refuses to run ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

Check that you can manually traverse the folders in question with your
logon. Try some other directories possibly with SearchFolders = False

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Max" wrote:
| Ok, these were my user properties listed:
| Debugger Users; Tivoli_Admin_Privileges; Administrators; Users
| (I don't really know how to intrepret the properties <g)
|
| The sub still stubbornly refuses to run ..
| --
| Rgds
| Max
| xl 97
| ---
| Singapore, GMT+8
| xdemechanik
| http://savefile.com/projects/236895
| --
|
|

Sub cannot run (Error 5)
 

"Dave Patrick" wrote:
Check that you can manually traverse the folders
in question with your logon.

Confirmed, no problem. I can see the folder contents.

Try some other directories
possibly with SearchFolders = False

Tried other directories, and with the suggested setting
but it still hangs at the same line:
..FileName = "*.*"

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

Max,
People always seem to get unexpected behaviour with .Filesearch, across
versions of Windows and Office.
Dir( ) produces relaible results.

NickHK

"Max" wrote in message
...
I'm trying to run the sub below (using Excel 97, Win XP),
but kept hitting:

Invalid procedure call or argument (Error 5)

Debug pointed at this line:
.FileName = "*.*"

The number of files in the folder: D:\Program Files\Erlang\CalcA
is less than 50

-------
Sub ListFiles02()
'Using FileSearch to list the files in a directory
Dim i As Integer
i = 1
With Application.FileSearch
.NewSearch
.LookIn = "D:\Program Files\Erlang\CalcA"
.FileName = "*.*"
.SearchSubFolders = True
'.SearchFolders = False
.Execute

For i = 1 To .FoundFiles.Count
Range("B" & i).Value = .FoundFiles(i)
Range("C" & i).Value = FileDateTime(.FoundFiles(i))
Range("D" & i).Value = FileLen(.FoundFiles(i))
Next
End With
End Sub
-------

Appreciate any insights. Thanks.

--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

Thanks, Nick
Dir( ) produces reliable results.
How could the sub be amended to make use of the above ?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"NickHK" wrote in message
...
Max,
People always seem to get unexpected behaviour with .Filesearch, across
versions of Windows and Office.
Dir( ) produces reliable results.

NickHK

Sub cannot run (Error 5)
 

Max,
VBA Help has examples.

NickHK

"Max" wrote in message
...
Thanks, Nick
Dir( ) produces reliable results.
How could the sub be amended to make use of the above ?
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"NickHK" wrote in message
...
Max,
People always seem to get unexpected behaviour with .Filesearch, across
versions of Windows and Office.
Dir( ) produces reliable results.

NickHK

Sub cannot run (Error 5)
 

I had a look there, but I think its out of my depth to produce something
meaningful (I'm still a vba newbie). Do you have some sample code at hand
which could illustrate the same functionality as that of the Sub
ListFiles02() [I plucked the sub from one of the posts here]. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"NickHK" wrote in message
...
Max,
VBA Help has examples.

NickHK

Sub cannot run (Error 5)
 

Max,

Private Sub CommandButton1_Click()
Dim FileCount As Long

'FileCount = ListFilesWith_DIR(ActiveSheet.Range("B1"), "D:\Program
Files\Erlang\CalcA", "*.*")
FileCount = ListFilesWith_DIR(ActiveSheet.Range("B1"), "C:", "*.*")

MsgBox FileCount & " files found.", vbOKOnly, "Results"

End Sub

Private Function ListFilesWith_DIR(argStartRange As Range, _
ByVal argFolderPath As String, _
Optional argFilter As String = "*.*") _
As Long

Dim FileName As String
Dim i As Integer

If Right(argFolderPath, 1) < "\" Then argFolderPath = argFolderPath & "\"

FileName = Dir(argFolderPath & argFilter, vbNormal)

Do While FileName < ""
With argStartRange
.Offset(i, 0).Value = FileName
.Offset(i, 1).Value = FileDateTime(argFolderPath & FileName)
.Offset(i, 2).Value = FileLen(argFolderPath & FileName)
End With

i = i + 1
FileName = Dir()
Loop

'Return the number of files found
ListFilesWith_DIR = i

End Function

NickHK




"Max" wrote in message
...
I had a look there, but I think its out of my depth to produce something
meaningful (I'm still a vba newbie). Do you have some sample code at hand
which could illustrate the same functionality as that of the Sub
ListFiles02() [I plucked the sub from one of the posts here]. Thanks.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"NickHK" wrote in message
...
Max,
VBA Help has examples.

NickHK

Sub cannot run (Error 5)
 

Wonderful, Nick. Thanks !
Your alternative runs great.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Sub cannot run (Error 5)
 

Glad to hear it's sorted.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Max" wrote:
| Wonderful, Nick. Thanks !
| Your alternative runs great.
| --
| Rgds
| Max
| xl 97
| ---
| Singapore, GMT+8
| xdemechanik
| http://savefile.com/projects/236895
| --
|
|

Sub cannot run (Error 5)
 

My appreciation goes out to all who responded, Dave !
Cheers
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Dave Patrick" wrote in message
...
Glad to hear it's sorted.