Dir function not returning any files.

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.

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

Select allOpen in new window

@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

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

Select allOpen in new window

Thanks,
Ben

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

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

@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

Select allOpen 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.

@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.

@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

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.

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.

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

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

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!  :-)