Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2007

    Unanswered: Directory Listing

    Hello All,

    Im currently putting together a database which manages Scanned Files. Heres how it currently plans to work;

    1) File Gets Scanned onto system (Done)

    2) A Macro/Function looks in Scans directory and picks up any new files and puts them into a table - tblNewScans (Need Help)

    3) User looks a form which shows all new scans and decides where to put them e.g different tables. (Done)

    What would be the best way for me to achieve this?

    Ive currently got this code;

    Public Function aryFilesInDir(ByVal strPath As String) As Variant
    ' ----------------------------------------------------------------------
    ' Function to return an array of filenames from a passed directory
    '      array is 1 based
    ' ----------------------------------------------------------------------
    ' declare locals
    Dim aryFiles() As String
    Dim sfile As String
    Dim intCount As Integer
    intCount = 0
    ' prep pattern from passed path
    strPathPattern = strPath & "\*.*"
    ' run the dir cmd using the pattern
    sfile = Dir(strPathPattern)
    If Len(sfile) > 0 Then
        ' note the initial file returned
        If sfile <> "." And sfile <> ". ." Then
            intCount = intCount + 1
            ReDim aryFiles(intCount)
            aryFiles(intCount) = sfile
        End If
        ' Do the remaining files
        Do While sfile <> ""
            sfile = Dir()
            If sfile <> "." And sfile <> ". ." Then
                intCount = intCount + 1
                ReDim Preserve aryFiles(intCount)
                aryFiles(intCount) = sfile
            End If
    End If
    aryFilesInDir = aryFiles()
    End Function
    But im not sure how to use it? It creates an array but im not sure how to output it to a table.

    Thanks in advance for your help.

    James T

  2. #2
    Join Date
    Jan 2007
    California, USA
    Rather than creating an array, just Append the file name to the table, rather than adding it to the array. Using the Query Design Grid, create an Append query to append "" to your table (tblNewScans). Then, using the SQL created from this query as an example, write the SQL in your VBA code where you are now adding the found scan to the array. Once you have the SQL written in code, you can execute it with this line:
    CurrentDB.Execute txtSQL
    "txtSQL" is a sample variable name to hold your newly written SQL code to do the Append Query. Be sure that the DAO reference is set for this database. In VBA use Tools/References to do this.
    Hope this helps.

  3. #3
    Join Date
    Jan 2007
    Thanks for your reply.

    I seem to be getting a bit lost - sorry.

    So basically i need to rewrite my original code? Something like this?

    Function GetAllFilesInDir(ByVal strDirPath As String) As Variant
        ' Loop through the directory specified in strDirPath and save each
        ' file name in an array, then return that array to the calling
        ' procedure.
        ' Return False if strDirPath is not a valid directory.
        Dim strTempName As String
        Dim varFiles() As Variant
        Dim lngFileCount As Long
        Dim txtSQL As String
        txtSQL = "INSERT INTO tblFiles (TempFileName) VALUES (" & strTempName & ")"
        ' Make sure that strDirPath ends with a "\" character.
    If Right$(strDirPath, 1) <> "\" Then
        strDirPath = strDirPath & "\"
        End If
        If GetAttr(strDirPath) = vbDirectory Then
            strTempName = Dir(strDirPath, vbDirectory)
            If (GetAttr(strDirPath & strTempName) _
            And vbDirectory) <> vbDirectory Then
            'Do SQL
            CurrentDb.Execute txtSQL
        End If
            strTempName = Dir()
    End If
    End Function
    For the Insert statement, i keep getting errors about arguments, parameters and syntax incorrect? Any ideas?

    Im not too sure what you meant about the Append query?


Posting Permissions

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