Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Mar 2005
    Leicestershire, United Kingdom
    Thanked 0 Times in 0 Posts

    As my development on my moving files across and ftp'ing them to Rightmove continues, I am now looking to achieve the following:

    When I am looking to look at a field within a query, to then copy it across to another folder, but renaming it at the same time. I would only like to copy across the file it has changed since the last vba run. So for example, I would have the original photographs sepcific to a field of a record, which copies across to a Common folder; \\Server\Database\Files\

    This is the space for the duplicated renamed files.

    When the vba next runs, only if the file has changed or does not exists, it will copy it across to the common folder.

    The vba I have to convert and rename the images is:

    ' Change the constants as needed
    ' Name of the table
    Const strTable = "qryRightmoveFeedExport"
    ' Name of ID field
    Const strIDName = "PropertyID"
    ' Prefix of field names
    Const strPrefix = "Media_document_"
    ' Lowest number for field
    Const intMin = 0
    ' Highest number for field
    Const intMax = 0
    ' Path of destination folder - MUST end in backslash
    Const strFolder = "\\Server\Database\Rightmove\Files\"

    ' Kill strFolder & "*.*"

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strID As String
    Dim strField As String
    Dim strOld As String
    Dim strNew As String
    Dim i As Integer
    Dim intPosDot As String
    Dim strExt As String

    On Error GoTo ErrHandler
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset(strTable, dbOpenDynaset, dbSeeChanges)
    Do While Not rst.EOF
    strID = rst.Fields(strIDName)
    SysCmd acSysCmdSetStatus, "Processing record ID " & strID
    For i = intMin To intMax
    strField = strPrefix & Format(i, "00")
    If Not IsNull(rst.Fields(strField)) Then
    strOld = rst.Fields(strField)
    intPosDot = InStrRev(strOld, ".")
    strExt = Mid(strOld, intPosDot)
    strofficeid = DLookup("Branch_ID", "qryRightmoveFeedExport", "PropertyId= " & strID)
    strNew = strFolder & strofficeid & "_" & strID & "_DOC_" & Format(i, "00") & strExt
    FileCopy strOld, strNew

    End If
    Next i

    On Error Resume Next
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    msgbox "Error " & Err & ": " & Err.Description, vbExclamation
    Select Case Err
    Case 53 ' File not found
    Resume Next
    Case 76 ' Path not found
    Resume Next
    Case Else ' Other errors
    Resume ExitHandler
    End Select
    Resume ExitHandler
    Best Regards,


  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts
    You could create an object of type Scripting.FileSystemObject and use its GetFile method to get an object of type Scripting.File. This object has a DateLastModified property. You could do this for strOld and strNew.

    See An Introduction to the Scripting Runtime Object Library.

Posting Permissions

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