FileDateTime(pathname)

Topic

New Reply

I use Iomega QuickSync II to constantly back up my documents and templates directories to my Zip Drive. This not only provides me with an off site backup, but also allows me to work on any file outside of the office.

To enhance the system, I have written a macro, OpenFromZip(), that runs on my office machine. It makes
it easy to update my hard drive with any file I edited or created elsewhere. OpenFromZip() simply presents a FileOpen dialog on the ZipDriveDocuments directory. When I choose a file, the macro copies it to the corresponding subdirectory of the Documents directory on the hard drive. If the corresponding subdirectory does not exist on the hard drive, it creates it.

Most of the time it works fine, but I have had a couple of problems caused by human error. A couple of times, I have inadvertently overwritten the newer copy on the hard drive with the older copy on the zip because I mistakenly believed that the zip version was newer.

The macro currently overwrites the hard drive copy by default. I want to enhance it by adding a confirmation that shows the corresponding modification dates.

I have tried to use the FileDateTime(pathname) function in

 
 If FileDateTime(ZipVersion) < FileDateTime(HDVersion) Then

  If MsgBox(HDVersion & " is newer than " & ZipVersion & _
   ".  Do you want to overwrite " & HDVersion & "(" & _
   HDVerDate & ") with " & ZipVersion & "(" & ZipVerDate _
   & "?", VBYesNoCancel) = VBYes Then 

 [proceed with the copy, etc.]

I can’t seem to get the variables HDVerDate and ZipVerDate to work. The documentation says that FileDateTime(pathname) returns a variant. Any suggestions?

Reply | Quote

Replies

Hi,
When you say you can’t get them to work, what exactly is happening? You haven’t posted the code that determines what ZipVerDate and HDVerDate are – can you post that too?

Reply | Quote

Sorry, I left that out. It is

Dim ZipVerDate
Dim HDVerDate

 ...

ZipVerDate = FileDateTime(ZipVersion)
HDVerDate = FileDateTime(HDVersion)

ZipVersion and HDVersion are strings comprised of the full path names of the respective files. The problem is that the HDVerDate and ZipVerDate vaiables remain empty throughout the macro’s execution, so the “If” test retuns “false” and the macro executes as if the test did not exist.

I assume that I don’t understand and use the data type for the FileDateTime(pathname) function properly.

Reply | Quote

I can’t see anything wrong with that code (I just tested it and it’s OK on my PC) so could you post the whole thing in case something is going wrong somewhere else?

Reply | Quote

*** Edited – Geoff W. Long lines split (They fo bad things to , particularly in flat mode) ***

Rory,

Here is the entire sub
[indent]

---

Sub OpenFromZip()
'
' DisplayFileOpen Macro
' An aide in Zip Sync file management.
' This macro allows you to open a document which
' only exists on the QuickSync disk and copies it
' to the corresponding folder on the hard drive
'
' This macro could have been written to run on any
' Office97 machine by using functions that return the
' pertinent directories on the machine in use, but
' that was unnecessary for my purposes and would have
' slowed the macro down.  Instead, I chose to hard
' code the directories into the macro.  Hence, if the
' directories change, the macro needs to be manually
' updated.
'
Dim ZipDir As String     'variable holds the Zip drive path
Dim SaveDir As String    'variable holds the hard drive path
Dim RelPath As String    'variable hold the relative path 
                         '(stripped of the drive letter)
Dim FName As String      'variable holds the file name
Dim ZipVersion As String 'variable holds the full path name 
                         'of the zip version
Dim HDVersion As String  'variable holds the full path name 
                         'of the HD version
Dim ZipVerDate           'variable holds the time/date stamp  
                         'of the zip version
Dim HDVerDate            'variable holds the time/date stamp 
                         'of the HD version

On Error GoTo CreateIt
Err.Clear
   StatusBar = "Select a file from the Zip drive."
'First, change the default directory to the documents 'directory on the zip drive
      ChangeFileOpenDirectory "J:Documents"
'Now, display the FileOpen dialog
      Application.Dialogs(wdDialogFileOpen).Show

'Find out which subdirectory, on the Zip drive, the opened 
'document is located and assign it to a variable (ZipDir)
   ZipDir = ActiveDocument.Path
   ZipVersion = ActiveDocument.FullName
   ZipVerDate = FileDateTime(ZipVersion)
'Strip the zip drive letter from the full path and assign 
'this relative path to a variable (RelPath).  By ommitting 
'the third (length) parameter from the 
'Mid(string,start,length) function, the function returns the 
'remainder of the string,regardless of its length.
   RelPath = Mid(ZipDir, 4)
'Identify the corresponding directory on the hard drive by 
'appending the hard drive letter to the relative path 
'variable and assigning it to a new variable (SaveDir)
'IMPORTANT: While windows is not really case sensitive and 
'will open or save a file to the proper directory regardless 
'of the case in the path string, VisualBasic's string 
'comparison operators are case sensitive.  Therefore, even 
'though
'ActiveDocument.SaveAs "e:DocumentsArthur" will work as 
'expected, ActiveDocument.Path will not equal 
'"e:documentsarthur" since ActiveDocument.Path
'will always return the drive letter as a capital
   SaveDir = "E:DocumentsArthur" & RelPath
'Now, get just the file name without the path and assign it 
'to the (FName) variable
   FName = ActiveDocument.Name
'Put the hard drive full path name in a string   
   HDVersion = SaveDir & "" & FName
'if it exists get its date time stamp
   HDVerDate = FileDateTime(HDVersion)
'compare the dates/times of the two files   
   If ZipVerDate < HDVerDate Then
      If MsgBox(HDVersion & " is newer than " & ZipVersion _
          & ".  Do you want to overwrite " _
         & HDVersion & "(" & HDVerDate & ") with " & _
          ZipVersion & "(" & ZipVerDate & "?", _
         vbYesNoCancel) = vbYes Then GoTo TryAgain
      Else
         GoTo Done
   End If
   
         
TryAgain:

StatusBar = "Saving " & FName & " to " & SaveDir & "..."
'Finally, save the document to the corresponding directory 
'on the hard drive using SaveAs to save it to the 
'corresponding hard drive directory (SaveDir) with the same
'file name (FName).
   ActiveDocument.SaveAs HDVersion
GoTo TestArea

CreateIt:
   StatusBar = "Creating " & SaveDir & "..."
      ChDrive "E"
      ChDir "E:DocumentsArthur"
      MkDir RelPath
   GoTo TryAgain

TestArea:
'Test to see if the procedure was successful and, display a 
'confirmation or error.  If the procedure was successful, 
'the ActiveDocument should reside on the hard drive, in
'the corresponding directory.  If the ActiveDocument does 
'not, something went wrong.
'If you are having trouble with this test, see the comments 
'at lines 45-50 which discuss the
'case sensitive nature of the comparison string which 
'requires that SaveDir have acurate
'capitalization.
   If ActiveDocument.Path = SaveDir Then
       MsgBox FName & " has been saved to " & SaveDir, _
          vbInformation
   Else
       MsgBox "The copy to the hard drive failed.", _
          vbCritical
   End If
MsgBox FName & " has been saved to " & ActiveDocument.Path()
Done:
End Sub

---

[/indent]

Hope you can understand my spagetti

Reply | Quote

Hi,
I’m stumped – I pasted that into a module, changed the paths and it worked fine! Have you tried stepping through the code with the locals window open and viewing where it fails? (I’m assuming that the fact you have a line which has an underscore and then carries on on the same line rather than on the next line is simply down to a formatting glitch when you posted)

Reply | Quote

Yeah. The hardest part of posting the Sub was reformatting it for the lounge.

I’m going to try it on another machine.

Reply | Quote

Hi,

You may be better posting code as a .txt attachment. It keeps the formatting (unlike code without “pre” tags); it can be copied anxd pasted without mucking about with manual line breaks (unlike code with “pre” tags); and lines you’ve accidentally left too long don’t cause display problems (unlike code with pre tags).

Reply | Quote

Geoff,

Do you mean a text file attachment or pasteing a text file?

Reply | Quote

Sorry, I meant attaching a text file- as I’ve done in this post.

Pasting text into a message loses all leading spaces when the message is viewed.

Reply | Quote

** edited Geoff W. Split long lines ***

Finally got it to work. I changed the IF test to

   If ZipVerDate < HDVerDate Then
      If MsgBox(HDVersion & " is newer than " & ZipVersion _ 
         & ".  Do you want to overwrite " _
         & HDVersion & "(" & HDVerDate & ") with " & _ 
         ZipVersion & "(" & ZipVerDate & ")?", _
         vbYesNo) = vbNo Then
       GoTo Done
      End If
   End If

The original was looking for vbYes and was convoluted with an Else. Changing it to vbNo made it cleaner and fixed the problem. Thanks for the chance to think this one through.

BTW, if anyone else uses QuickSyncII and can use this routine, I’d be glad to share it.

Reply | Quote

Rory,

Everything is working now but I still have one problem which was introduced when the date/time comparison started working.

The routine chokes on the line that tries to get the date/time stamp from the HDVersion if the file does not exist on the hard drive. I couldn’t find a VBA test for whether the file exists in order to avoid an error (in JS it’s as easy as

if (HDVersion) {stuff to execute if it exists}

).

I tried

If Err = 53 Then GoTo LabelThatSkipsTheComparison

and

 On Err = 53 GoTo . . .

,
but neither one trapped the Error 53. The only thing that worked was

On Error GoTo LabelThatSkipsTheComparison

at the beginning of the routine. This works, but it is not satisfactory because it treats all errors the same way.

Any suggestions? (BTW Error 53 is “File not found”.)

Reply | Quote

To find out if a file exists, use the FileSystemObject’s FileExists method. You’ll need to set a reference to the Microsoft Scripting Runtime.(Tools References…)

Dim oFSO as FileSystemObject
Set oFSO = New FileSystemObject

If oFSO.FileExists(Fullpath & filename) = True _
Then
‘ File Exists!
Else
‘ File doesn’t exist.
End If

Set oFSO = Nothing

I can highly recommend VB & VBA In A Nutshell by Paul Lomax published by O’Reilly. It’s an excellent reference to the VBA language.

Mike

Reply | Quote

How is this method better than the simpler “DIR”?

I’ve used the FileScripting method in the past, but not recently.

Reply | Quote

The scripting method, recommended by VBNerd works fine. Thanks. For my education, though, how would the DIR method work?

Reply | Quote

Hi,
Something like:
If Dir(fullpath & filename) = “” then
‘file does not exist
else
‘file exists
end if
should work.
Just a thought – glad it’s working now!

Reply | Quote

hah ha! I fooled myself! I dug up my boolFileExists and found that *I* use the Filelen function!

The first example below using FileLen will work as posted. the other two probably need supporting code that isn’t posted yet.

Public Function boolFileExists(strFile As String) As Boolean
' Procedure :   boolFileExists
' Description:  This code determines if a file exists.
' Copyright: Chris Greaves Inc.
' Inputs:       A possibly empty string representing a _
' possible legal file path and name.
' Returns:      A boolean, TRUE if the file is available to the caller.
' Assumes:      Nothing
' Side Effects: None.
' Tested:       By the calls shown below.
    boolFileExists = False
    On Error Resume Next
    boolFileExists = (FileLen(strFile) = FileLen(strFile))
NoFile:
'Sub TESTboolFileExists()
'    MsgBox boolFileExists("c:autoexec.bat") ' Generally you will find this.
'    MsgBox boolFileExists("autoexec.bat") ' Only if your default directory is C:
'    MsgBox boolFileExists("c:ausoexec.bat") ' Mis-spelled the name
'    MsgBox boolFileExists("") ' Empty string
'    MsgBox boolFileExists("*&^%$#@!") ' Illegal file name constructed here.
'    MsgBox boolFileExists(32) ' Used a file handle? ooops!
'End Sub
End Function

Here’s a use of DIR to build a list/string of drive letters:

Public Function boolValidate_Drive(ByVal strDrive As String)
' Procedure :   boolValidate_Drive
' Description:  Return the set of live drives from a set of possibles.
' Copyright:    Chris Greaves Inc.
' Inputs:       A string of letters. Usually a-z.
' Returns:      Only letters which correspond to an active_
'  drive available to this system.
' Assumes:      Nothing
' Side Effects: None.
' Tested:       By the calls shown below.
    boolValidate_Drive = False
    If Len(strDrive) = 2 Then
        If boolAlphaOnly(Left(strDrive, 1)) Then
            If Mid(strDrive, 2, 1) = ":" Then
                On Error GoTo BAD2
                Dir (strDrive)
                On Error GoTo 0
                boolValidate_Drive = True
                Exit Function
BAD2:
                Resume Exit2
Exit2:
                Exit Function
            End If
        End If
    End If
End Function

Here’s a poorly-documented function to load directories to a listbox

Private Sub LoadList(frmMe As UserForm, lb As ListBox,_
ByVal strPath As String, ByVal strExtent As String, boolRecurse As Boolean)
' Given a valid path and file Extent, _
load the ListBox with all immediate child directories
    If Right(strPath, 1) = Application.PathSeparator Then
    Else
        strPath = strPath & Application.PathSeparator
    End If
    Me.Caption = "Searching for Icons in " & strPath
    WordBasic.PrintStatusBar "Searching for Icons in " & strPath
    frmMe.Caption = "Searching for Icons in " & strPath
    Dim strDirs As String
    
    strExtent = UCase(strExtent)
    
    Dim strFile As String
    
    strFile = Dir(strPath & "*.*", 16)
    While strFile  ""
        On Error GoTo Failed1
    Me.Caption = "Searching for Icons in " & strPath
    WordBasic.PrintStatusBar "Searching for Icons in " & strPath
    frmMe.Caption = "Searching for Icons in " & strPath
        If boolRecurse And (GetAttr(strPath & strFile) _
And vbDirectory) = vbDirectory Then
            If strFile  "." And strFile  ".." Then
                strDirs = strDirs & strPath & strFile & ","
            Else
            End If
        Else
        End If
Failed1:
    On Error GoTo 0
    strFile = Dir
    Wend
    
    strFile = Dir(strPath & "*." & strExtent, 16)
    While strFile  ""
        On Error GoTo Failed2
        If boolRecurse And (GetAttr(strPath & strFile)_
 And vbDirectory) = vbDirectory Then
            If strFile  "." And strFile  ".." Then
                strDirs = strDirs & strPath & strFile & ","
            Else
            End If
        Else
            If (GetAttr(strPath & strFile) And vbDirectory)  vbDirectory Then
                    lb.AddItem strPath & strFile
            Else
            End If
        End If
Failed2:
    On Error GoTo 0
    strFile = Dir
    Wend
    
    While Len(strDirs) > 0
        Dim strThisDir As String
        strThisDir = strSplitStringAt(strDirs, Right(strDirs, 1), True)
        strDirs = strSplitStringAt(strDirs, Right(strDirs, 1), False)
        Call LoadList(frmMe, lb, strThisDir, strExtent, boolRecurse)
    Wend

End Sub

Reply | Quote

Chris,

By itself, not much different.

But there’s a whole heap of other methods with filescripting which can be useful (eg, BuildPath, CopyFile, CreateFolder).

Lok in VBA help under “FileSystemObject”

Reply | Quote

Just in case you haven’t sorted this yet, I’d go with Chris on this one – the Dir function is probably the simplest method of checking if a file exists.

Reply | Quote

I’d be willing to bet that in future versions of the VBA language, things like the DIR$ function will be dropped for the more OO FileSystemObject. Just looking at the code, I’d say that the FileExists method is clearer than Dir.

There’s lots of redundant functions/statements in the VBA language that could use some cleaning up. There must be three or four ways to convert something to a string. Strangely enough, I’ve heard that VB.NET still carries many of these redundancies. Oh, for those of you who think that they don’t need to pay attention to VB.NET, think again. Office.NET will use the same language as VB.NET. Office XP won’t, but Office.NET(the version after XP) will.

BTW, the FileSystemObject also has a DateLastModified property which appears to do the same as the FileDateTime function.

Well, I’ve babbled on enough.

Mike “Someday I’ll create a sig and a neat pic.”

Reply | Quote

I had problems with this sort of stuff about five months back. I needed date/timestamps from files as part of an installation routine.

I waded through the busines sof PKZip (not Zip drives) using a form of UTC but got bogged down in effecting the conversion in Word97SR2/VBA.

I probably have the old code floatinga round, if you get really stuck. They are probably lotsa functions with TESTcode that let me single-step through stuff to see just waht wa shappening.

In the end I elected to test just the file name and size, and skip the date/time comparisons.

This won’t be much help except to say that the FileDateTime stuff is either broken, or just darn difficult to get working or to understand.

Ultimately I’d like to reintroduce the timestamp code, so I’d entertain the idea of a fresh start on it ….

(later)

I backup from two HDs to a removable HD each morning using the DOS XCOPY command and PKZip to collect the essential system files (registry, INI files etc). The Xcopy command does a good job of identifying which files are changed since the latest copy. I could post the BATch file.

Reply | Quote