Hi,
I have this project I am doing for my boss but I am not sure where to get started. I have a bunch of .pdf files and my boss wants to see a msgbox popup in from an access forum which I know how to do and ask the user to select how many days. Then use this number to show all the pdf files which were modified within that amount of days. It seems easy but not sure the property in regards to date modified.
Any help would be great Thanks A lot

Hello,

You can use the FileScripting object in the Mircosoft Scripting Runtime library (set a reference in your project) to identify .pdf files in a folder, as well as the LastModified date of those files. The following finds the .pdf files in a folder modified since a "number of days" parameter entered in an inputbox, loads the names of the files that meet the criteria into a string array, then loads a ListBox control from the array. Be sure the ListBox's Row Source Type property is set to "Value List".

Private Sub cmdGetFileList_Click()

    ' Dimension FileScripting objects
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim oFile As Scripting.File

    Dim intDaysOld As Integer
    Dim datModifies As Date
    Dim arrFiles() As String
    Dim intIndex As Integer
    Dim intCount As Integer

    intDaysOld = CInt(InputBox("Enter number of days:", "Files modified since number of days entered"))

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("c:\Test")
    Set oFiles = oFolder.Files

    intIndex = 0

    For Each oFile In oFiles
        datModified = Format(oFile.DateLastModified, "mm/dd/yyyy")

        ' Is the file a .pdf file?
        If oFile.Type = "Adobe Acrobat 7.0 Document" Then
            ' Is LastModified date >= criteria date?
            If CDate(datModified) >= Date - intDaysOld Then
                ' Load file name into dynamic string array
                ReDim Preserve arrFiles(intIndex)
                arrFiles(intIndex) = oFile.Name
                Debug.Print arrFiles(intIndex)
                intIndex = intIndex + 1
            End If
        End If
    Next oFile

    'Populate the list box with file names.
    For intCount = 0 To UBound(arrFiles)
        Me.lstFiles.AddItem arrFiles(intCount)
    Next

    Set oFSO = Nothing
    Set ofFolder = Nothing
    Set oFile = Nothing

End Sub

HTH,

Bob

Dim datModifies As Date should be Dim datModified As Date

Thanks a lot I'll give that a try

Is there any way of doing it without using the FileScripting object because I am trying to use this in Access 97 and I get an error which states
Compile error:
User-defined type not defined
Pointing at Dim oFSO As Scripting.FileSystemObject

Accutally thats not the problem, the problem is displayin the data because access does not allow the usage of .additem so is there a way to copy all the files which are pdf within that time frame and store them maybe in one of my tables.

Actually, Access does not allow .AddItem in Access 97. It is a programming enhancement in Access 2002, where .AddItem and .RemoveItem act as wrapper functions around value lists.

You can use a similar technique in Access 97 by building your own value list string, and assigning it to the ListBox's RowSource property (the RowSourceType property must be set to Value List).

The major drawback of this approch is that Access 97's RowSource is limited to strings containing a maximum of 2,048 characters (A2K2 increased the maximum to 32,768 characters).

If you need more than 2,048 characters, create a Recordset object (using ADO or DAO) based on a table in your database, add the retrieved file names to the table, and use the table as the ListBox's Rowsource Property, with RowSourceType set to Table/Query. (If you need help with the code to do this post again).

Here's how to use the Value List string:

Private Sub cmdGetFileList_Click()

    ' Dimension FileScripting objects
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim oFile As Scripting.File

    Dim intDaysOld As Integer
    Dim datModified As String
    Dim strRowSource As String

    intDaysOld = CInt(InputBox("Enter number of days:", "Files modified since number of days entered"))

    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("c:\Test")
    Set oFiles = oFolder.Files

    intIndex = 0

    For Each oFile In oFiles
        datModified = Format(oFile.DateLastModified, "mm/dd/yyyy")

        ' Is the file a .pdf file?
        If oFile.Type = "Text Document" Then
            ' Is LastModified date >= criteria date?
            If CDate(datModified) >= Date - intDaysOld Then
                ' Add file name to ListBox's RowSource string
                strRowSource = strRowSource & oFile.Name & ";"
                Debug.Print strRowSource
            End If
        End If
    Next oFile

    ' Remove trailing ";" from Value List string
    strRowSource = Left(strRowSource, Len(strRowSource) - 1)

    ' Set RowSource property
    Me.lstFiles.RowSource = strRowSource

    Set oFSO = Nothing
    Set ofFolder = Nothing
    Set oFile = Nothing

End Sub

HTH,

Bob

Thanks for all the information its all very helpful, I am learning quite a bit doing it. If you don't mind posting the code if lets say I have a table with a hyperlink which links to a pdf file and the table also has a field called ID. Thanks a lot

Martin

Will get some code posted as soon as I get a chance but wanted to correct my typo in the last post:

' Is the file a .pdf file?
        If oFile.Type = "Text Document" Then

should read

' Is the file a .pdf file?
        If oFile.Type = "Adobe Acrobat 7.0 Document" Then

as in my original post.

- Bob

This is about as far as I can get tonight. The code below reads names of .pdf files from a folder into an ADO recordset, builds a hyperlink address to the files, then inserts the hyperlink address into a table. The table has two fields: FileID (Autonumber), FileHyperlink (hyperllink address).

Everything seems to work, except that when I click the hyperlink in the table Adobe Reader opens, but then closes immediately without displaying the file. Not sure why. I know that hyperlink fields introduce some security issues and perhaps thats the problem. However, I'm attempting top open a .pdf file on my local system and it won't work right. Any idea why? Here's the code:

Option Compare Database

Public Sub InsertPDFFiles()
    On Error GoTo ErrorHandler

    ' Data access variables
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim strCnn As String
    Dim strSQL As String

    ' Microsoft Scripting Runtime library variables (Scrrun.dll.)
    Dim oFSO As Scripting.FileSystemObject
    Dim oFolder As Scripting.Folder
    Dim oFile As Scripting.File

    Dim intDaysOld As Integer
    Dim datModified As String
    Dim strRowSource As String
    Dim intID As Integer
    Dim strFileHyperLink As String
    Dim blnRecordAdded As Boolean

    ' Open a connection
    Set cnn = New ADODB.Connection
    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=c:\PDFFiles.mdb;"
    cnn.Open strCnn

    ' Open tblPDFFiles recordset with a cursor that allows updates
    Set rst = New ADODB.Recordset
    strSQL = "tblPDFFiles"
    rst.Open strSQL, cnn, adOpenKeyset, adLockOptimistic, adCmdTable

    ' Instantiate scripting objects
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder("c:\Test")
    Set oFiles = oFolder.Files

    ' Get number of days.
    intDaysOld = CInt(InputBox("Enter number of days:", "Files modified since number of days entered"))

    intIndex = 0

    ' Insert file names and hyperlink address into table
    For Each oFile In oFiles
        datModified = Format(oFile.DateLastModified, "mm/dd/yyyy")

        ' Is the file a .pdf file?
        If oFile.Type = "Adobe Acrobat 7.0 Document" Then
            ' Is LastModified date >= criteria date?
            If CDate(datModified) >= Date - intDaysOld Then
                strFileHyperLink = oFile.Name & "#" & oFile.Path & "#"
                Debug.Print strFileHyperLink
            End If

            rst.AddNew
            rst!FileHyperlink = strFileHyperLink
            rst.Update
            blnRecordAdded = True

        End If
    Next oFile

    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Set oFSO = Nothing
    Set ofFolder = Nothing
    Set oFile = Nothing
    Exit Sub

ErrorHandler:
   ' clean up
    If Not rst Is Nothing Then
        If rst.State = adStateOpen Then rst.Close
    End If
    Set rst = Nothing

    If Not cnn Is Nothing Then
        If cnn.State = adStateOpen Then cnn.Close
    End If
    Set cnn = Nothing

    Set oFSO = Nothing
    Set ofFolder = Nothing
    Set oFile = Nothing

    If Err <> 0 Then
        MsgBox Err.Source & "-->" & Err.Description, , "Error"
    End If
End Sub




- Bob