Results 1 to 2 of 2
Thread: VBA to Compare Files
2009-11-21, 14:51 #1
- 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
On Error Resume Next
Set rst = Nothing
Set dbs = Nothing
msgbox "Error " & Err & ": " & Err.Description, vbExclamation
Select Case Err
Case 53 ' File not found
Case 76 ' Path not found
Case Else ' Other errors
Resume ExitHandlerBest Regards,
2009-11-21, 15:26 #2
- Join Date
- Mar 2002
- Thanked 28 Times in 28 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.