XCOPY and CopyFolder problems

[303factory]

Hi

I need my code to copy a folder of files into another folder of files. Sometimes the file names may match, it doesnt matter if these files are not copied, or are copied and overwrite the orginal.

The CopyFolder method doesnt work because some files in the target directory are read only. This gives me 'permission denied' errors.
I looked around for an alternative solution and was told to try XCOPY with this code:

Dim oShell, xsource, xdestination
xsource = "C:\TempOutputFolder\User Files\*"
xdestination = "D:\myOtherFolder"
Set oShell = WScript.CreateObject("WScript.Shell")
oShell.Run "XCOPY.EXE xsource xdestination /R /Y"
Set oShell = Nothing

However with this code I'm getting the error 'Object required' on the 4th line, and cant find out why.

Can anyone tell me either why I'm getting this error, or perhaps suggest an alternative? I wouldnt mind using the MakeWritable function on the target folder so I can use CopyFolder, but that only seems to be available in .net

Any help would be much appreciated

303

 

[Mike375]

Try a batch file

rd /S /Q FromLetters
MkDir FromLetters
Copy \Letters \FromLetters

Pause

That first removes the folder an then remakes it.

Leave Pause there for you first try as this will leave the DOS screen open after it has run.

You can run a batch file from RunApp in a macro or with Shell. The advantage of Shell as I learnt the other day from Banana was the DOS screen can be hidden with vbHide

 

[303factory]

Try a batch file

rd /S /Q FromLetters
MkDir FromLetters
Copy \Letters \FromLetters

Pause

That first removes the folder an then remakes it.

Leave Pause there for you first try as this will leave the DOS screen open after it has run.

You can run a batch file from RunApp in a macro or with Shell. The advantage of Shell as I learnt the other day from Banana was the DOS screen can be hidden with vbHide

Can a batch file take variables when called? The name of the folder I'm copying will change every time the code is executed..

Dan

 

[Mike375]

I don't really know. Actually I reduced the number I use because of that very thing.

The other thing you could try is to see if code will remove a folder.

 

[Mike375]

I found this to remove a folder but "read only' files stopped it. But maybe some way around it.

Dim fso
Dim fol As String
fol = "c:\FromLetters"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(fol) Then
fso.DeleteFolder fol
Else
MsgBox fol & " does not exist or has already been deleted!" _
, vbExclamation, "Folder not Found"
End If

I just realised that files will do variables but they are % type stuff. Not to sure you can do this type thing which I suspect in principle is what you are talking about

Call Shell("explorer.exe c:\" & Format([Forms]![MasterForm]![Text1582]), vbNormalFocus)

 

[Mike375]

Check this

http://www.access-programmers.co.uk/forums/showthread.php?t=31829

This one removed a folder with read only files, they were Word.docs

Public Type SHFILEOPSTRUCT
hWnd As Long
wFunc As Long
pFrom As String
pTo As String
fFlags As Integer
fAborted As Boolean
hNameMaps As Long
sProgress As String
End Type
Public Const FO_DELETE = &H3
Public Const FOF_ALLOWUNDO = &H40
Public Const FOF_SILENT = &H4
Public Const FOF_NOCONFIRMATION = &H10
Public Declare Function SHFileOperation Lib "shell32.dll" Alias "SHFileOperationA" (lpFileOp As SHFILEOPSTRUCT) As Long
Public Function DeleteDir(strDirectory As String)
Dim SHFileOp As SHFILEOPSTRUCT
With SHFileOp
'Delete the file
.wFunc = FO_DELETE
'Select the file
.pFrom = strDirectory
'Allow 'move to recycle bn' with no warning/confirmation messages and no progress meter
.fFlags = FOF_ALLOWUNDO Or FOF_SILENT Or FOF_NOCONFIRMATION
End With
'perform file operation
SHFileOperation SHFileOp
End Function

 

[303factory]

I found this to remove a folder but "read only' files stopped it. But maybe some way around it.

Dim fso
Dim fol As String
fol = "c:\FromLetters"
Set fso = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists(fol) Then
fso.DeleteFolder fol
Else
MsgBox fol & " does not exist or has already been deleted!" _
, vbExclamation, "Folder not Found"
End If

I just realised that files will do variables but they are % type stuff. Not to sure you can do this type thing which I suspect in principle is what you are talking about

Call Shell("explorer.exe c:\" & Format([Forms]![MasterForm]![Text1582]), vbNormalFocus)

One problem that I maybe didnt make clear enough in my original post. I cant actually delete the target folder, I need to keep the original files in the target directory, and add any new files from the source directory. There may be some duplicates that can either not be copied or can overwrite the target. But there will definately be two sets of files, of which I need to keep both.

It's looking like I'll need to loop through every file in the directory and do a fileExist check before I copy... I'd rather avoid this though!

 

[Mike375]

When I searched about and got the above I saw some stuff on removing the "read only" so you might have to search up on that.

Alternatively, work out how to copy with the VBA but have it bypass the read only files. A batch file does that.

 

[Mike375]

I never have this problem because all my folder copying produces a sub folder with date/time stamp, in other words, back up system. However, in case I do have it one day I tried to solve the problem for my self.

Firstly, if a variable in the folder name did not have to be allowed for the a straight copy with a bathc file is just one line since they just by pass the read only file.

This is clumsy but one made would be simple to run.

SetAttr "c:\FromLetters\0AMPMike.doc", vbNormal This fixes the problem for a specified file and the following runs but obviously that is not mush use. So to AllenBrowne in the attached DB

Dim FSO As Object
Dim FromPath As String
Dim ToPath As String
FromPath = "C:\Letters"
ToPath = "C:\FromLetters"



If Right(FromPath, 1) = "\" Then
FromPath = Left(FromPath, Len(FromPath) - 1)
End If
If Right(ToPath, 1) = "\" Then
ToPath = Left(ToPath, Len(ToPath) - 1)
End If
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FolderExists(FromPath) = False Then
MsgBox FromPath & " doesn't exist"
Exit Sub
End If


FSO.CopyFolder Source:=FromPath, Destination:=ToPath

The attached is made from Allenbrownes site. There is a call on the button the form. That list all the file name in the table Files. Should be OK to select the folder as variable. If a tabular form was made on the table (Table needs to be deleted before each run) then it should be no problem to apply something like

SetAttr "c:\Letters\" & Format([Forms]![MasterForm]![FileName]), ", vbNormal

and then run the above copy code.

The AllenBrowne code is almost instant so I think the whole process would only add a couple of seconds to the copy procedure.

 

[Mike375]

I just made a rough working edition to automate setting the Attr away from read only and all works OK.

Macro1 will take you there.

In the attached everyrhing (including for AllenBrownes function) have been based on copying \Letters to \FromLetters and serring the attr to vbNormal is also based on the folder \FromLetters. So all that needs to be done is to make those for a variable as has been done for thefile names.

I tested it by setting attr to Read Only on several files (Word.docs) and also changed the content of the Word.docs to see if the copy overwrote them and it did.

This way will do me because I can use the output of Allen Browne's function to a table

 

[Mike375]

The attached now has the folders as variables. Open Formq and go from there. Some of the field in Query2 are not used as they were Left() and Right() that I was trying as getting the variables for folder and files names.

One type of file that did stop it running was where I had saved a website. Perhaps because there was no extension. In fact I did not even know I had a few saved until I was trying different folders

 

[Mike375]

PS.

On Form1 the label/Textbox on the left for Copy From is in fact the folder being copied To and the right one is the folder being copied from. I did the one on the left first as it removes the read only attributes in the folder being copied to

 

[303factory]

Awesome thanks for such a thorough set of responses I should have everything I need to get it working as I want to now.
Thanks again!

 

[Mike375]

Happy to have been of some assistance.

Just remember with that last edition the two textboxes for entering folder names are reversed.