Results 1 to 7 of 7
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Permission denied (Error 70) (Excel 2K and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    Any examples how to sort the output of the Application.FileSearch. If it is not sorted by default.

    I am getting a Run-Time error 70 (Permission) denied on file 1106 and I dumped the output into a worksheet to see what was the last file reached, 1105 and then see the path to it, to figure out where is that 1106's file.

    But without sorting I really can't tell because according to Window's Explorer, files 1105, and 1106 and 1107 have the same profile, so why is it failing on 1106, I have no idea, and I am sure I am not looking at the right file, because other CDs have 4000+ files it they were processed without a hitch.

    Now Error 70 means:

    <<< An attempt was made to write to a write-protected disk or to access a locked file. For example, this error will occur if an Open For Output statement is performed on a write-protected file. >>>

    The files are on a CD, and I know that I am accessing a read-only disk, and I am reading from it, but so are all the files before it and after it... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    So how do I get what the file 1106 is? I tried DIR(.FileFound(1106)) but it fails with the RTE-70?

    Thanks for any help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Permission denied (Error 70) (Excel 2K and >)

    In Excel 2002, the results of FileSearch are sorted ascending by file name by default. If you specify SearchSubFolders to be True, the sorting will be performed over all found files, not within folders. The Execute method of the FileSearch object has two properties that govern sorting:
    <UL><LI>SortBy, can be
    msoSortByFileName (default)
    msoSortByFileType
    msoSortByLastModified
    msoSortByNone
    msoSortBySize
    <LI>SortOrder, can be
    msoSortOrderAscending (default)
    msoSortOrderDescending[/list]HTH

  3. #3
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permission denied (Error 70) (Excel 2K and >)

    Hi Wassim

    I don't know what could be causing your run-time error 70,
    but I have a couple of answers / pointers to your query
    about sorting the output of Application.Filesearch

    first the file-listing sub

    <hr>
    <pre>
    Sub CreateFileList()
    '---------------------------------------------
    Dim fs As Object, i As Integer
    '---------------------------------------------
    Set fs = Application.FileSearch
    '---------------------------------------------
    With fs
    .LookIn = "Your folder name here"
    .Filename = "*.xl?" ' your file-type here
    If .Execute(SortBy:=msoSortByFileName, _
    SortOrder:=msoSortOrderAscending) > 0 Then
    For i = 1 To .FoundFiles.Count
    ActiveCell.Value = .FoundFiles(i)
    ActiveCell.Offset(1, 0).Select
    Next i
    MsgBox "There were " & .FoundFiles.Count & " file(s) found."
    Else
    MsgBox "No files were found !"
    End If
    End With
    End Sub
    </pre>


    <hr>


    In this example,
    - the 'msoSortByFileName' can be changed to whatever sort order you want
    - the For... Next loop enters each found filename in the activecell and goes down for the next one.


    The code may not look / feel very elegant, but it works !


    Second, a suggestion to use a different approach for file-listing -

    Download 2xExplorer by Nikos Bozinis from http://www.netez.com/2xExplorer.

    This is a FREE souped-up Explorer utility which is fantastic !
    One of the many features of this utility is the ability to copy selected filenames to the clipboard (with full path)

    - select the files you want to list in Excel (Ctrl+A for all files)
    - then press Alt+C to copy filenames to clipboard

    - then switch to Excel and Paste.
    - the selected filenames are pasted from the activecell downwards.

    Then you can
    - replace the folder path (if you don't need it)
    - sort by whatever order you want
    etc.. etc.. etc...


    HTH :0)

  4. #4
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permission denied (Error 70) (Excel 2K and >)

    Hi again, Wassim

    sorry, a small goof up <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

    <hr>
    - select the files you want to list in Excel (Ctrl+A for all files)
    <hr>
    should read as

    <hr>
    - select the files you want to list in 2xExplorer (Ctrl+A for all files)
    <hr>

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Permission denied (Error 70) (Excel 2K and >)

    Hi Hans

    OK Thanks for your reply, and after a couple of tweeks I figured some of the problems.

    1) Some files had an '=' as its first character in its name, so when Excel put that name in the cell, it wanted to evaluate it. I solved that by appending a<font color=blue> "</font color=blue><font color=red>'</font color=red><font color=blue>"</font color=blue> before any name.

    2) Some files, can be accessed directly from the CDs, but the FileSearch could not read the name, date and time, size of the files. This is still a pending issue.

    I was told that the CDs were burned using Nero CD Burner, but no version was given, and I did not see any issues on their web site. I have written some extra code to allow the code to continue and then flag the files by number for further analysis, or hand processing.

    Thanks for your help.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Permission denied (Error 70) (Excel 2K and >)

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> Khushnood

    This 2xExplorer was a nice thing.

    Please see my reply to Hans.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Lounger
    Join Date
    Jan 2002
    Location
    Mumbai, India
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Permission denied (Error 70) (Excel 2K and >)

    Hi Wassim,

    Ok, I understand the problem.
    One discovers new issues with software every day... !

    I sometimes have a problem with the semi-colon character in filenames while burning CDs.
    These are Excel files saved via macros during document processing.
    So I've switched to using comma or hyphen instead

    Glad you liked 2xExplorer :-)

    Ciao

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •