Results 1 to 4 of 4

Thread: Access 2007

  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Portsmouth, UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access 2007

    I have some code I need to adapt to parse a directory listing of both docs and docx's to an access form. I am triggering this by a command button and then viewing the data in a form, 1 field uses the filename and path to hyperlink to the document.

    The vba currently only returns the filename, whereas I need filename, fullpath, file created and modified dates.

    here is the code I am using:
    Code:
    Dim fso
    Dim ObjOutFile
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    Set ObjOutFile = fso.CreateTextFile("OutputFiles.csv")
    
    ObjOutFile.WriteLine ("Type,File Name,File Path")
    
    GetFiles ("K:\Central\HRServices\JDDB\")
    
    ObjOutFile.Close
    
    WScript.Echo ("Completed")
    
    Function GetFiles(FolderName)
        On Error Resume Next
    
        Dim ObjFolder
        Dim ObjSubFolders
        Dim ObjSubFolder
        Dim ObjFiles
        Dim ObjFile
    
        Set ObjFolder = fso.GetFolder(FolderName)
        Set ObjFiles = ObjFolder.Files
    
        For Each ObjFile In ObjFiles
        ObjOutFile.WriteLine ("File," & ObjFile.Name & "," & ObjFile.Path)
        Next
    
        Set ObjSubFolders = ObjFolder.SubFolders
    
        For Each ObjFolder In ObjSubFolders
    
            ObjOutFile.WriteLine ("Folder," & ObjFolder.Name & "," & ObjFolder.Path)
    
    
            GetFiles (ObjFolder.Path)
        Next
    
    End Function
    Can anyone suggest a solution?

    Thanks
    Last edited by RetiredGeek; 2013-12-15 at 14:27. Reason: Added code tags
    Nemesisuk
    Windows 7 Home premium 32 bit
    Nvidia GeForce 9800 GTX

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    33
    Thanks
    4
    Thanked 3 Times in 3 Posts
    I threw this together, give it a shot. I put the data in an array not knowing what you want to do with it.
    Module1:
    Code:
    Option Compare Database
    Option Explicit
    Public myarray(200) As String
    Public inta As Integer
    Form1:
    Option Compare Database
    Option Explicit
    Private Sub Command7_Click()
    Dim mya As Integer
    mya = 0
    Call getdata("c:\temp")
    Do
        MsgBox myarray(mya)     ' Display the data in the array
        mya = mya + 1           ' Bump to next entry
        If mya = inta Then
            Exit Do
        End If
    Loop
    End Sub
    Function getdata(Strmydir As String)
    Dim strdir As String
    Dim Strext As Variant
    Dim createdate As Variant
    Dim moddate As Variant
    Dim fso As Object
    Dim fsn As Object
    inta = 0
    strdir = Dir(Strmydir & "\*.*")
    Do While strdir <> ""
    Strext = Split(strdir, ".")  ' Strext(1) is extension
    If UCase(Strext(1)) = "DOC" Or UCase(Strext(1)) = "DOCX" Then
        Set fso = CreateObject("Scripting.FileSystemObject")
        Set fsn = fso.GetFile(Strmydir & "\" & strdir)
        createdate = fsn.DateCreated
        moddate = fsn.datelastmodified
        Set fso = Nothing
        myarray(inta) = strdir & "," & Strmydir & "," & createdate & "," & moddate
        inta = inta + 1
    End If
    strdir = Dir
    Loop
    End Function
    Last edited by RetiredGeek; 2013-12-15 at 14:28. Reason: Added code tags

  4. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Portsmouth, UK
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Orangehut

    I need to store it in the table behind the form if possible to allow user to search and open the linked doc.
    Nemesisuk
    Windows 7 Home premium 32 bit
    Nvidia GeForce 9800 GTX

  5. #4
    2 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    162
    Thanks
    0
    Thanked 24 Times in 24 Posts
    I have created two working examples to do what you need.

    See:

    Document Links

    and

    Document Links 2
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Tags for this Thread

Posting Permissions

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