Dir function not returning any files.

Hi Experts,

I had the following code working perfectly fine on my pc.

   sDir = "E:\AppDev\FTP\Caspio"

    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)

Open in new window


However when placing it into the server it always returning an empty string.

Folder above contains many files (on that server).
LVL 6
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dr. KlahnPrincipal Software EngineerCommented:
This may be an access privilege issue, i.e. insufficient privilege to look at the drive or folder in question.  PC workstations tend to be run by one user with admin rights, where servers tend to apportion and enforce access rights more carefully.

Before attempting to look at the folder check whether read access rights to that folder exist, and if not, dump an error message indicating the nature of the issue.
Fabrice LambertConsultingCommented:
I do not suggest using the Dir function as it have issues.
The FileSystem Library offer better and more reliable way to loop trough files or directories.

Sample code:
Const path As string = "E:\AppDev\FTP\Caspio"

Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")

Dim folder As Object
Set folder = fso.getFolder(path)

Dim file As Object
For Each file in folder.files
    '// do Something with the file object here.
Next

Open in new window

bfuchsAuthor Commented:
@Dr. Klahn,
I'm logged in as admin.
Have full rights to create/delete files there.
btw, running dir command in dos command prompt works fine there.
Thanks,
Ben
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

bfuchsAuthor Commented:
Hi Fabrice,

You already mentioned that to me several times, however i had a hard time converting this code into your version.

Perhaps you can give me a hand?

   Dim StrFile As String, strTable As String, sFileStr As String
   Dim sDir As String
   Dim l As Long, s As String, i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "H:\FTP\test\"
    'StrFile = Dir(sDir & "*PatChanges*")
    StrFile = Dir(sDir & "*" & sFileStr & "*")
    Do While Len(StrFile) > 0 And Not IsFileOpen(sDir & StrFile)
''        Do While IsFileOpen(sDir & StrFile)
''            ' do nothing
''        Loop
        If InStr(1, StrFile, "Full") = 0 And InStr(1, StrFile, "Part") = 0 Then
            i = CountOfRecords(sDir, StrFile)
            If i > 1 Then
                'Debug.Print StrFile & " - " & CountOfRecords(sDir, StrFile)
                If InStr(1, StrFile, "PatChanges") > 0 Then
                    strTable = "Patients"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "SchChanges") Then
                    strTable = "Schedule"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "CGChanges") Then
                    strTable = "Caregivers"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                ElseIf InStr(1, StrFile, "PatMedProfileChangesV2") Then
                    strTable = "Patients_Medications"
                    l = ImportDataToCaspio(strTable, sDir & StrFile, i)
                End If
                If l > 0 Then
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Success') "
                Else
                    s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & StrFile & "'," & l & ",'Failure') "
                End If
                db.Execute s
            End If
        End If
        On Error Resume Next
        Kill sDir & StrFile
        StrFile = Dir
        On Error GoTo 0
    Loop
End Sub

Open in new window


Thanks,
Ben
Fabrice LambertConsultingCommented:
   Dim StrFile As String
   Dim strTable As String
   Dim sFileStr As String
   Dim sDir As String
   Dim l As Long
   Dim s As String
   Dim i As Long
   Dim db As Database
   Set db = CurrentDb
   sDir = "H:\FTP\test\"

    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")

    Dim folder As Object
    Set folder = fso.GetFolder(sDir)

    Dim file As Object
    For Each file In folder.Files
        If Not (isfileopen(file.Path & "\" & file.Name)) Then
            If (InStr(l, file.Name, "Full") = 0 And InStr(l, file.Name, "Part") = 0) Then
                i = CountOfRecords(file.Path)
                If i > 1 Then
                    If InStr(1, file.Name, "PatChanges") > 0 Then
                        strTable = "Patients"
                        l = ImportDataToCaspio(strTable, file.Path & "\" & file.Name, i)
                    ElseIf InStr(1, file.Name, "SchChanges") Then
                        strTable = "Schedule"
                        l = ImportDataToCaspio(strTable, file.Path & "\" & file.Name, i)
                    ElseIf InStr(1, file.Name, "CGChanges") Then
                        strTable = "Caregivers"
                        l = ImportDataToCaspio(strTable, file.Path & "\" & file.Name, i)
                    ElseIf InStr(1, file.Name, "PatMedProfileChangesV2") Then
                        strTable = "Patients_Medications"
                        l = ImportDataToCaspio(strTable, file.Path & "\" & file.Name, i)
                    End If
                    If l > 0 Then
                        s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & file.Name & "'," & l & ",'Success') "
                    Else
                        s = "Insert into API_CallHistory(TableName,FileName, RecordsSubmitted,Results) Values ('" & strTable & "','" & file.Name & "'," & l & ",'Failure') "
                    End If
                    db.Execute s
                End If
            End If
        End If
    Next
End Sub

Open in new window

bfuchsAuthor Commented:
Hi Fabrice,

Thanks very much for converting it.
I see some things works differently from what expected, for example file.path does return the path including file name, so I'm adjusting the code accordingly.
Perhaps you can figure out why am I getting the attached error.
See immediate window for values of variables.

PS. no missing references.

Thanks,
Ben
Untitled.png
Untitled1.png
Mark EdwardsChief Technology OfficerCommented:
While the file system library gives you a whole bunch of things you can do with a file system, here's how to properly use the Dir function for anyone who wants to know:

First, let's setup some parameters:
Dim strFolderPath as string
Dim strFileName as string
strFolderPath="C:\Windows\"

Notice that the folder path MUST have EVERY back-slash character.  Leaving the last back-slash off (i.e. "C:\Windows") will cause
strFileName = Dir("C:\Windows")
to return an empty string, and the attempt to retrieve the next file name with
strFileName = Dir()
will throw an error.

If you are looking for a specific file name, or file names that match a certain wildcard format, such as
strFolderPath = "C:\Windows\*.accdb")
that looks for all files with the ".accdb" extension in the folder "C:\Windows\", then
strFileName = Dir(strFolderPath)
will return the first file name that matches the wildcard filter, and
strFileName = Dir()
will return the next file name matching the filter.  You can keep calling
strFileName = Dir()
 until all file names matching the filter have been returned (Dir() returns an empty string: i.e. Dir() = ""), or you find what you are looking for.

Remember, if you leave off the last back-slash, or the folder path doesn't exists, then Dir(strFolderPath) will return an empty string (which is the major cause of confusion when using Dir) and Dir() will throw an error.  Follow these rules and the Dir function works just fine.  Obviously it is required to make sure your folder path exists before using Dir.  It can be done with Dir (in a whacky-kind of way), but I'll let you figure out how to do that.... (hint:  "Dir() throws an error")

The little code function below can be used to demo these points.

Private Sub DirFunction()
    Dim strSearchFolder As String
    Dim strSearchFiles As String
    Dim strFile As String

    'Find all files in a folder:
    MsgBox "Find all files in a folder...."
    strSearchFolder = <your folder path here>
    strFile = Dir(strSearchFolder)
    Do
        'show retrieved file name:
        MsgBox strFile
        'get the next file name:
        strFile = Dir()
    Loop Until strFile = ""

    'Find all files matching wildcard file name:
    MsgBox "Find all files matching wildcard file name <your file name wildcard mask here>...."
    strSearchFiles = <your folder path here><your file name  or file name mask here>
    strFile = Dir(strSearchFiles)
    Do
        'show retrieved file name:
        MsgBox strFile
        'get the next file name:
        strFile = Dir()
    Loop Until strFile = ""
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Thanks to all participants!

@Mark,
You have spotted the problem, while changing the path from the original to the server I omitted the last backslash...

@Fabrice,
I'm still hoping you will help me successful convert it to the file system, would gladly go for it (and accept your answer as well).

Thanks,
Ben
Fabrice LambertConsultingCommented:
@Mark Edward:
The main flaw of the Dir function, is that it store internally the search criteria for later use.
In consequences, cascading calls with different criterias can give unexpected results (since the new criteria overwrite the previous one).

This is illustrated by the following code:
Private Function FileExist(byval path as String) As Boolean
    FileExist = Len(Dir(path)) > 0
End Function

Public Sub CheckCorrespondingFiles()
    Const SourceFolderPath As String = "c:\users\userID"
    Const TargetFolderPath As String = "c:\users\Public"

    Dim fileName As String
    fileName = Dir(SourceFolderPath & "\*.*")
    While (Len(fileName) > 0)
            '// Ops, FileExist give a new criteria to the Dir function
            '// the loop will at best work only once
            '// regardless of the amount of files in the source folder
        If (FileExist(TargetFolderPath & "\" & fileName)) Then
            Debug.Print fileName " exist in " & TargetFolderPath
        End If
        fileName = Dir
    Wend

Open in new window

Here, you can update the faulty function if needed, but what if you use a *nice* Library (such as an ACCDE or whataver with unreachable source code) with such flaw ?
How many time will you spend identifying a problem that isn't on your side ? Guess too much.
Mark EdwardsChief Technology OfficerCommented:
@Fabrice:  Thanks for pointing that out.  Again, it's all about what a developer is trying to do and how well they understand how the functions they use work.  Dir is a simple little function that is best used for simple processes where the file system library would be overkill or it's additional functionality is not needed.

A developer can screw up anything if they don't know or don't pay attention to what they are doing, even use of the file system library.  I prefer to understand how something works and how to use it properly instead of fearing potential coding screw-ups as a reason to avoid anything.  It helps me spot issues like the one the author had.  You can see the missing final backslash in his initial post.
bfuchsAuthor Commented:
@Fabrice,
Actually I figured out what was causing the error in your code,.
When re-writing the function you had an l instead of a 1 in the instr function.
Now it seems like working fine.

Just one question (for both).
it seems like it loops in order of file name and then date created.
how can I change that should work according to date/time created?

Thanks,
Ben
NorieAnalyst Assistant Commented:
Ben

You can't alter the order in which Dir operates.

If you need to process files in a specific order you'll probably need to look at using some other method.
Fabrice LambertConsultingCommented:
Keep in mind that the file system isn't a database, so it does not support any sorting,  search or filtering functionalities.

What you see in the files browser is "a view" of the file system, the view use the file system services to retrieve informations about files / folders, and the view offer filtering, search or filtering functionalities.
Bill PrewIT / Software Engineering ConsultantCommented:
Just one question (for both).
it seems like it loops in order of file name and then date created.
how can I change that should work according to date/time created?

You've had plenty of great help on this question so I haven't been posting, but did want to comment on this question.  Neither the built in DIR() function or the FileSystem object approach can fetch in any sorted order, as mentioned by other experts.

There are really two or three common approaches if you really want a sorted order, at least in my humble opinion:

  1. A fairly basic one would be to replace using the Dir() function approach with a call out to a command line to execute the DIR command to get a list of files.  The DIR command has options on the /O switch that provide some basic sorting by key fields of the output.  Your code would execute this command, and then process the output sort of like it was a text file reading line by line, but it would be in a sorted order.
  2. You could use either of the methods already mentioned earlier in this questions posts to fetch the list of files and place them into some sort of data structure.  Then write or use a function to sort the collection in the order you desire, before finally displaying.
  3. If you are working with a database you may be able to create a temporary table from the file data, and then do a SELECT with ORDER BY to put the results into the order you want, and then display the result of the SELECT.


»bp
bfuchsAuthor Commented:
Thank you experts,
As I see its not that simple to manipulate the sort order in the way the current code is written.
Perhaps would open a new thread focusing on that.
Thanks,
Ben
Mark EdwardsChief Technology OfficerCommented:
Simply put, the Dir() and File System methods will get the data, but they are not custom customizable to suit your own needs.  That's where you have to come in with your own programming, such as dumping the output of the Dir()and File System operations into an array or table where you can perform your own custom processing.

No software designer can anticipate every option that a user developer may want, so they get you half-way there, then the custom stuff is up to you.

By the way, this is a good example of why "codeless programming" is not as great an idea as all those "codeless programming" developers who develop that stuff to sell would like you to think.  There is always something a developer wants to do that the "codeless programming" designers can't anticipate, which leads to disappointment and frustration for the developer, and, eventually to development environments like Access that can do both, or a hybrid if you want!  :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
System Programming

From novice to tech pro — start learning today.