| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 = "*" |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | -- | | |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | -- |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | -- | | |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 -- |
|
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 | -- | | |
|
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
| Befuddled with For Next Loop ------ Run - Time Error '13' Type Mismatch Error | Excel Programming | |||
| Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Linear Mode
