code to move files created between two dates to folder B?

[gil]

Hi All, . I am seeking vba code to MOVE files created between date1 and date2 from folderA to folderB?



? Possibly something like:




Dim date1 As String

date1 = Selection.Text

' navigation code to new selection

Dim date2 As String

date2 = Selection.Text

Dim folderA As String

' identify folderA

Dim folderB As String

' identify folderB



' ??? Object.MoveFile Source, Destination


WordBasic.CopyFileA folderA ?? FileName:= ??, Directory:=folderB



Cheers & tia

Gil

 

[gil]

or something like:

Dim strDefFilePath As String, strFileToCopy As String
Dim strSourcePath As String, strSourceFile As String
Dim objFSO As Object

strSourcePath = "c:\Patients\AMR\"
strDefFilePath = "c:\Patients\"
strFileToCopy = "TorrTod.doc"
strSourceFile = strSourcePath & strFileToCopy


strSourcePath = "" ' identify folderA
strDefFilePath = "" ' identify folderB
strFileToCopy = "CartSamu.doc" ' but how to identify by date or time?
strSourceFile = strSourcePath & strFileToCopy

' ... but it needs to move over up to 10 jpg files


Gil


Hi All, . I am seeking vba code to MOVE files created between date1 and date2 from folderA to folderB?



? Possibly something like:




Dim date1 As String

date1 = Selection.Text

' navigation code to new selection

Dim date2 As String

date2 = Selection.Text

Dim folderA As String

' identify folderA

Dim folderB As String

' identify folderB



' ??? Object.MoveFile Source, Destination


WordBasic.CopyFileA folderA ?? FileName:= ??, Directory:=folderB



Cheers & tia

Gil

 

[Russ]

Gil,
This will work very quickly in WinWord docs to extract document properties.
http://word.mvps.org/FAQs/MacrosVBA/DSOFile.htm

One of the document properties is for creation date.
Tweak to code found in the .dot template to do what you want.

 

[Russ]

Gil,
I also found this code snippet with Google and modified it to return
Creation Date of a file. (Note: .dll will not work in MacWord)
http://officeone.mvps.org/vba.html
I haven't tested in WinWord because I'm using MacWord 2004 at home.

You may have the function return a value as a string or date.

Function GetDateCreated(ByVal FileName As String) As Date
'Function GetDateCreated(ByVal FileName As String) As String
Dim DSO As Object
Dim DPs As Object

Set DSO = CreateObject("DSOleFile.PropertyReader")
Set DPs = DSO.GetDocumentProperties(FileName)
GetDateCreated = DPs.DateCreated
Set DSO = Nothing
End Function

If the function returns as a date you can use the datediff() function to
compare two dates.

 

[Russ]

Gil,
There are other ways to move files using other scripting languages, too.
In a dos batch file, you could use xxcopy ( a variation of xcopy ).
http://www.xxcopy.com/xxcopy17.htm

You could even call a dos batch file from VBA.
Helmut Weber mentioned this:
http://vb.mvps.org/samples/project.asp?id=Shell32
Or this xShell code works in Word97, too:

Put this in Declarations section at the top of your VBA code module so that
all subroutines can take advantage of the 'wait for shell' code.

Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

'Add this code as one of the regular subroutines.

Public Function ShellX( _
ByVal PathName As String, _
Optional ByVal WindowStyle As Integer = vbMinimizedFocus, _
Optional ByVal Events As Boolean = True _
) As Long

'Declarations:
Const STILL_ACTIVE = &H103&
Const PROCESS_QUERY_INFORMATION = &H400&
Dim ProcId As Long
Dim ProcHnd As Long

'Get process-handle:
ProcId = Shell(PathName, WindowStyle)
ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

'wait for process end:
Do
If Events Then DoEvents
GetExitCodeProcess ProcHnd, ShellX
Loop While ShellX = STILL_ACTIVE

'clean up:
CloseHandle ProcHnd
End Function

'And call it like this:

Dim x As Long
Dim strDosBatchFullPath As String
strDosBatchFullPath = C:\...myDosBatchFile.bat
System.Cursor = wdCursorWait
x = ShellX(Chr(34) & strDosBatchFullPath & Chr(34))

 

[Russ]

Oops,
Correction in line below

Gil,
There are other ways to move files using other scripting languages, too.
In a dos batch file, you could use xxcopy ( a variation of xcopy ).
http://www.xxcopy.com/xxcopy17.htm

You could even call a dos batch file from VBA.
Helmut Weber mentioned this:
http://vb.mvps.org/samples/project.asp?id=Shell32
Or this xShell code works in Word97, too:

Put this in Declarations section at the top of your VBA code module so that
all subroutines can take advantage of the 'wait for shell' code.

Private Declare Function CloseHandle Lib "kernel32" ( _
ByVal hObject As Long) As Long
Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
ByVal hProcess As Long, lpExitCode As Long) As Long
Private Declare Function OpenProcess Lib "kernel32" ( _
ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

'Add this code as one of the regular subroutines.

Public Function ShellX( _
ByVal PathName As String, _
Optional ByVal WindowStyle As Integer = vbMinimizedFocus, _
Optional ByVal Events As Boolean = True _
) As Long

'Declarations:
Const STILL_ACTIVE = &H103&
Const PROCESS_QUERY_INFORMATION = &H400&
Dim ProcId As Long
Dim ProcHnd As Long

'Get process-handle:
ProcId = Shell(PathName, WindowStyle)
ProcHnd = OpenProcess(PROCESS_QUERY_INFORMATION, True, ProcId)

'wait for process end:
Do
If Events Then DoEvents
GetExitCodeProcess ProcHnd, ShellX
Loop While ShellX = STILL_ACTIVE

'clean up:
CloseHandle ProcHnd
End Function

'And call it like this:

Dim x As Long
Dim strDosBatchFullPath As String
strDosBatchFullPath = C:\...myDosBatchFile.bat

Forgot the Quote marks:
strDosBatchFullPath = "C:\...myDosBatchFile.bat"

 

[gil]

Thank you Russ, ... looks like I may need to play with it a while. I'll check out the items you've referred to.
Cheers,
Gil

 

[Ed]

Hi Gil (& Russ).

In addition to Russ's suggestions ...

When you need to work with files and folders there's a very useful
FileSystemObject that you can use. It's very easy to use in VBA.

I think that the "guts" of what you want to do can be done by something like
this code snippet:

-------------------------------------------------------------------
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(SourceFolderPath)

For Each oFile In oFolder.Files
FileModifiedDate = Format(oFile.DateLastModified, "yyyymmdd")
If FileModifiedDate >= StartDate And FileModifiedDate <= EndDate Then
DestFilePath = oFSO.BuildPath(DestFolderPath, oFile.Name)
oFile.Move DestFilePath
End If
Next
-------------------------------------------------------------------

As you can see, there's not a lot to it. I include below the full code for a
UserForm that has two text boxes which hold the start and end dates and a
command button which initiates the move.

In my case I am using the last modified date of the files
(oFile.DateLastModified) but I could also use the creation date (e.g.
oFile.DateCreated).

Also, in my case I convert the dates to yyyymmdd format to make the
comparison easy but you could probably adapt it to use DateDiff as Russ
suggested.

Most of the rest of the code is validation and stuff.

To get the most benefit from the FileSystemObject, create a reference to
"Microsoft Scripting Runtime" in VBA's Tools>References dialog. That way you
get IntelliSense for it (and also VBA can do some checking for you).

You might have to fix some code lines if they get broken by the
word-wrapping of the newsgroup editor.

The code is:

-------------------------------------------------------------------
Private Sub cmdMove_Click()
Dim oFSO As Scripting.FileSystemObject
Dim oFolder As Folder
Dim oFile As File
Dim StartDate As String
Dim EndDate As String
Dim FileModifiedDate As String
Dim DestFilePath As String
Dim Msg As String
Const SourceFolderPath As String = "C:\Source"
Const DestFolderPath As String = "C:\Dest"
Const AppTitle As String = "Move Files"

On Error GoTo ErrHandler

If Not DateOK(txtStartDate.Text) Then
MsgBox "Invalid start date", vbExclamation, AppTitle
Exit Sub
End If

If Not DateOK(txtEndDate.Text) Then
MsgBox "Invalid end date", vbExclamation, AppTitle
Exit Sub
End If

StartDate = Right(txtStartDate.Text, 4) & Left(txtStartDate.Text, 2) & _
Mid(txtStartDate.Text, 4, 2)
EndDate = Right(txtEndDate.Text, 4) & Left(txtEndDate.Text, 2) & _
Mid(txtEndDate.Text, 4, 2)

If StartDate > EndDate Then
MsgBox "The start date is later than the end date", vbExclamation, _
AppTitle
Exit Sub
End If

Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(SourceFolderPath)

For Each oFile In oFolder.Files
FileModifiedDate = Format(oFile.DateLastModified, "yyyymmdd")
If FileModifiedDate >= StartDate And FileModifiedDate <= EndDate Then
DestFilePath = oFSO.BuildPath(DestFolderPath, oFile.Name)
oFile.Move DestFilePath
End If
Next

ExitPoint:
'Put any cleanup code here
Exit Sub

ErrHandler:
Msg = "An error has occurred. Please contact JLawson." & vbCrLf & vbCrLf _
& "Error " & Err.Number & " - " & Err.Description

MsgBox Msg, vbCritical, AppTitle

Resume ExitPoint
End Sub

Private Function DateOK(DateToCheck As String) As Boolean
'basic checks - could be beefed up
If (Not IsDate(DateToCheck)) _
Or Len(DateToCheck) <> 10 _
Or Mid(DateToCheck, 3, 1) <> "-" _
Or Mid(DateToCheck, 6, 1) <> "-" Then
DateOK = False
Exit Function
Else
DateOK = True
End If
End Function

Private Sub UserForm_Initialize()
'txtStartDate.Text = "05-06-2007"
'txtEndDate.Text = "12-10-2007"
End Sub

 

[Russ]

Thanks Ed,
Your method is definitely more elegant.

I wasn't aware of FSO since unfortunately I am currently stuck using MacWord
2004 at home and Word97 at work. (Neither of which can avail themselves' of
that reference, as far as I know.)

 

[Ed]

Hi Russ (& Gil),

The FSO is part of the Microsoft Scripting Runtime library (SCRRUN.dll). I
don't know the details of which versions of what software include that dll.
For me, it's always just been there on any machine where I've wanted to use
it.

There are some newsgroup posts which argue that you should not use the FSO.
One of the reasons listed is that you can't guarantee that it will be
available on any machine on which you might want your software to run (either
because SCRRUN.dll has never been installed, or because it has been disbled
or removed for security reasons), and that seems reasonable.

I was tacitly making two assumptions in my reply; one was that on the
relvant machine, SCRRUN.dll is available, and the second was that the
software being developed was not for general distribution.

I find the FSO pretty useful, but I have only used it in projects which are
for my own use or for use by my colleagues (who all have the dll). I've
avoided using it in projects which might be distributed more widely.

I did a quick test here and was able to use the FSO with Word 97.

So, depending on the circumstances, use of the FSO might be a good idea, or
it might not.

On another tack, I mentioned in my reply that it's a good idea to add a
reference to the Microsoft Scripting runtime in the VBA project. In fact, to
run the code as I've written it, the reference is more than a good idea; it's
essential. Because I've declared variables as type FileSystemObject, Folder
etc., the reference is required for VBA to know what they are.

Regards.

Ed

 

[Russ]

Ed,
Thanks for the research information. I am going to try it on the Word97
machine at work.

..dll files don't work in Mac machines per se, but I will soon be using a new
Mac at home with the Intel chip and hope to run MS Windows OS in a virtual
machine mode in a Window on my Mac OS desktop. It should be nearly as fast
as a 'normal' PC. It will have 2 quad core 64 bit chips inside. :-o

I think that same library is required for using the routines here:
http://www.j.nurick.dial.pipex.com/Code/index.htm
that do regular expressions.
I did want to try that, too.

 

[Russ]

Ed,
Thanks for the research information. I am going to try it on the Word97
machine at work.

..dll files don't work in Mac machines per se, but I will soon be using a new
Mac at home with the Intel chip and hope to run MS Windows OS in a virtual
machine mode in a Window on my Mac OS desktop. It should be nearly as fast
as a 'normal' PC. It will have 2 quad core 64 bit chips inside. :-o

I think that same library is required for using the routines here:
http://www.j.nurick.dial.pipex.com/Code/index.htm
that do regular expressions.
I did want to try that, too.

 

[Ed]

Hi Russ,

You're welcome

Have fun with your (soon) new Mac.

2 quad core 64 bit chips inside.

~

Regards.

Ed