Comparing files in two folders before moving them 1

[VbPanicStation]

Hi all,
I'm new to programming - using VBA in access 2000. Hoping someone can help me.
What the problem is: I have got three folders - temporary, master & archive folders. All folders have got files in them. What I'd like to do is for the code to move all the files from the temporary folder into the master folder. Conditions: If a file (232-01) with a lower revision exists in the master folder (and a higher revision file 232-02 is in the temporary folder) for the code to move the lower revision file from the master into the archive before moving the higher revision file from the temporary into the master. If a higher revision exists in the master and a lower revision exists in the archive then the code to move the lower revision file straight into the archive and leave the hight revision drawing in the master folder. Don't know where to start - Please Help!!!

 

[PHV]

Take a look at the Dir function and the Name .. As instruction.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[fumei]

Also possibly take a look at the FileSystemObject.

First test out (maybe use the macro recorder) copying files from one folder to another; then deleting files. Of course do not use your REAL data files. Make some dummy folders and dummy files to play with.

Once you have a handle on how to move files around, you will need some logic to make decisions about those files.

Give it a shot and when you have some code that you have specific questions about, post back here. What you want to do is reasonable.

Gerry

 

[VbPanicStation]

Hi - thanks for the info about the Dir function. I have been looking on the net for VBA code I could modify to suit & have come accross the one below. It works fine except that I can not get it to display in a list box. Listing the contents of a folder in a list box will enable me to select files to move to another folder. Can anyone help - please?

Dim sFileDest As String
Dim MyDir   As String
Dim Counter As Long
Dim i As Integer
'Create a dynamic array variable, declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)
sFileDest = "C:\ScannedClientDocs\*.*"
'Loop through all the files in the directory by using Dir$ function
MyDir = Dir$(sFileDest, vbDirectory)
Do While sFileDest <> ""
    'DirectoryListArray(Counter) = sFileDest
    DirectoryListArray(Counter) = Mid(sFileDest, 1, 10)
sFileDest = Dir$
Counter = Counter + 1
    Loop
'Reset the size of the array without losing its values using Redim Preserve
ReDim Preserve DirectoryListArray(Counter - 1)
'To prove it worked I ran the following:
For Counter = 2 To UBound(DirectoryListArray)
    'Debug.Print writes the results to the Immediate window (press Ctrl + G to view it)'
    If DirectoryListArray(Counter) <> DirectoryListArray(Counter - 1) Then
    Debug.Print DirectoryListArray(Counter)
End If
Next Counter

 

[VbPanicStation]

I'm stuck - please help!

 

[PHV]

Either play with the AddItem method of the ListBox (need recent version of access) or build the RowSource as a semi-colon separated list less than 2048 characters.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VbPanicStation]

Thanks PHV - I am using VBA/ Access 2000. When I type List1. & the list of methods/ properties is displayed - .additem is displayed. Is this because Is this because the relevant reference has not selected (as in tools/ references on the defaul menu)? If so, what is the reference name?

 

[PHV]

Type List1.AddItem and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VbPanicStation]

Hi PHV - what I meant to say is that the .additem property is NOT displayed in my list methods/ properties. Not sure if there is somewhere where I need to select relevant references or not

 

[PHV]

So, dynamically build a RowSource string (semi-colons separated list).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VbPanicStation]

Hello again,
I wonder if you can help.
With your help - I have been able to compile code which does most of the things that I'd like it to do namely:

1. List all items of a specific directory in a list box
2. Enable user to multi-select items - using a mouse
3. Using a button click event, Move selected items (one by one) from one folder to another.
I would like the list box to refresh every time a file is moved such that the contents of the list box displayed are true. How can I do this?
I am using VBA access 2000 & have pasted the code below:
I have only got two controls on the form - a listbox & a button:

Option Compare Database
Option Explicit
Private Sub Command20_Click()
' identifies the items that have been selected in the listbox
'and moves them from one folder to another
Dim intCount As Integer 'counts the number of items in the listbox
Dim FileToMove As String
For intCount = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(intCount) = True Then
FileToMove = Me.List1.ItemData(intCount)
MsgBox FileToMove
Dim fso ' Code below moves files (one by one) from source folder to destination folder
Dim file As String, sfol As String, dfol As String

file = FileToMove ' change to match the file name
sfol = "C:\folderone\" ' change to match the source folder path
dfol = "C:\foldertwo\" ' change to match the destination folder path
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
If Not fso.FileExists(sfol & file) And Not fso.FileExists(dfol & file) Then
MsgBox file & " does not exist in the source directory!", vbExclamation, "File Missing in source file"
ElseIf fso.FileExists(sfol & file) And fso.FileExists(dfol & file) Then
MsgBox file & " already exists in the destination folder", vbExclamation, "File Duplication"
ElseIf Not fso.FileExists(dfol & file) Then
fso.MoveFile (sfol & file), dfol
MsgBox file & " has now been moved!", vbExclamation, "Requested action complete"

Else
MsgBox file & " has already been moved!", vbExclamation, "File Exists in destination folder"
End If
If Err.Number = 53 Then MsgBox "File not found"
End If
Next

If Me.List1.Selected(intCount) = False Then
MsgBox "Please use the list box to highlight the files that you would like to move"
Exit Sub
End If
End Sub 

Private Sub Form_Load()
'on loading the form the function is called which displays
'the contents of a folder in the listbox
Call DisplayContentsOfDirectory
End Sub


Function DisplayContentsOfDirectory()
'Displays contents of folder into a list box
Dim sFileDest As String
Dim MyDir   As String
Dim Counter As Long
Dim i As Integer
Dim tempstring As String

'Create a dynamic array variable, declare its initial size
Dim DirectoryListArray() As String
ReDim DirectoryListArray(1000)
sFileDest = "C:\folderone\*.*"
'Loop through all the files in the directory by using Dir$ function
MyDir = Dir$(sFileDest, vbDirectory)
Do While sFileDest <> ""
    'DirectoryListArray(Counter) = sFileDest
    DirectoryListArray(Counter) = Mid(sFileDest, 1, 30)
sFileDest = Dir$
Counter = Counter + 1
    Loop
'Reset the size of the array without losing its values using Redim Preserve
ReDim Preserve DirectoryListArray(Counter - 1)
'To prove it worked I ran the following:
For Counter = 2 To UBound(DirectoryListArray)
    'Debug.Print writes the results to the Immediate window (press Ctrl + G to view it)'
    If DirectoryListArray(Counter) <> DirectoryListArray(Counter - 1) Then
    'Debug.Print DirectoryListArray(Counter)
    'MsgBox DirectoryListArray(Counter)
    tempstring = tempstring & DirectoryListArray(Counter) & ";"
End If
Next Counter
    '[Forms]![form1]!List1.RowSource = ""  'This would clear rowsource
    [Forms]![form1]!List1.RowSource = tempstring

End Function

 

[VbPanicStation]

Help please!

 

[fumei]

1. I am not sure you want to have all those message boxes. Why not use labels and update the lablel.caption with the text of your messages? That way, the user does not have to be always clicking the OK button on the message boxes. For CRITICAL information - maybe use a message box, but you could just as easily put the message text into a label, and increase the font size and bold it for emphasis. Generally speaking, if you do not need to use a message box....don't. On UserForms, labels can display messages, and faster.

2. You have the function to display the contents of the folder. Run it again when ever you move a file. It should do your update. Oh, but perhaps clear the listbox contents first, just to make sure that you have a fresh list every time.

Gerry

 

[VbPanicStation]

Hi Fumei,
Thanks for your notes. I agree with what you have said about the message boxes & was going to remove them once the code was complete - left them in there only for 'testing' purposes - I wanted to make sure that the code was doing what I wanted it to.
Good idea about the labels - I did not think about that at all.
I have got the code calling the function DisplayContentsOfDirectory every time a file is moved and it works OK. The only problem is that if users move files into or out of the folders using windows explorer - the listbox does not update. How can I overcome this problem? I have tried using the timer - to call the function DisplayContentsOfDirectory every second but this dis selects any files that I may have selected/ was in the process of moving. HELP PLEASE!!

 

[TaintedFlames]

You should organise yourself a little more. I went over you're code, and it's hard to see where you are going. I get the main idea of course.

My suggestion is break your problem down. Make a bunch of sub's that do something different. For exemple, make a sub, that lists a dir and stores it in an array. After make a sub, that add a string to a list. So on and so forth. Also you might want to slow down and write an algorithm.

I noticed that you haven't written any code to copy the files. When you get there type "FileSystemObject" in the help.

Also, you should probably use the "MouseDown" event to refresh your list.

Hope you get it and hope this helps.

Jay

 

[fumei]

Jay, I just found this thread again. Have to say, that is such good advice, and well phrased.

vbPanicStation? Do you have this working for you?

Gerry