Copyfile permission denied

sormick -  
 sormick -
Hello,

I created a VBA code that allows me to copy JPG files to an external hard drive.
It only copies the files based on the creation date that I selected.
I also have a VBA code that detects the letter, for example F:\, of the external hard drive because sometimes this letter changes.

However, every time I run my VBA code, there is often an error (70) permission denied that prevents me from copying the files.
Maybe it's because the destination folder on the external hard drive is read-only?
I have already manually disabled the read-only attribute of this folder, but it reactivates itself:

I am giving you my complete VBA code because you never know if this Error (70) is due to a missing or poorly written piece of code.

Thank you in advance for your help

Sheets("cp87").Select
Sheets("macros").Range("a203").Value = ""
Dim appShell As Object
Dim FileName As Variant
Dim FilePath As Variant
Dim oFolder As Object
Dim oFolderItem As Object
Dim testdate As Variant

FolderPath = "D:\Users\Public\IMPORT\TAGSIMPORTAFAIRE\"
FileName = "*.jpg"

EnterDate:
testdate = InputBox("enter the date of renaming the tags." & vbCrLf & "Example: 27/11/2014", "enter the date", (Date))
If testdate = "" Then Exit Sub
If Not IsDate(testdate) Then
MsgBox "The entered date is not valid." & vbCrLf _
& "Please re-enter the date."
GoTo EnterDate
End If
testdate = CDate(testdate)

Set appShell = CreateObject("Shell.Application")
Set oFolder = appShell.Namespace(FolderPath)

For Each oFolderItem In oFolder.Items
If oFolderItem.Name Like FileName Then

If DateValue(oFolder.GetDetailsOf(oFolderItem, 3)) = testdate Then

If Not oFolderItem.Name Like "*TAGSAFAIRE*" Then
a = a + 1
Dim destination As String
Dim objDrive As Object
With CreateObject("Scripting.FileSystemObject")
For Each objDrive In .Drives
If objDrive.IsReady Then
If objDrive.VolumeName = "VERBATIM HD" Then
destination = objDrive.Path & "\SAUVEGARDE IMPORT\"
Sheets("macros").Range("a203").Value = "YES"
Exit For
End If
End If
Next
End With
If Sheets("macros").Range("a203").Value = "" Then
MsgBox "The external backup drive is not connected via USB!", vbCritical
Exit Sub
End If

Dim xlobj As Object
Set xlobj = CreateObject("Scripting.FileSystemObject")
xlobj.CopyFile FolderPath & oFolderItem.Name, destination & oFolderItem.Name, True

Set xlobj = Nothing

End If

End If
End If

Next oFolderItem

If a = "" Then
MsgBox "There are no tags for the selected date: " & testdate & vbCrLf _
& "Please choose another date", vbCritical
Exit Sub
Else
MsgBox "There were: " & a & " tags renamed on: " & testdate & vbCrLf & _
"without duplicates and without tags that were not renamed."
End If

Dim appShell2 As Object, oFolder2 As Object, oFolderItem2 As Object

FolderPath = "D:\Users\Public\IMPORT\TAGSIMPORTAFAIRE\"
Set appShell2 = CreateObject("Shell.Application")
Set oFolder2 = appShell2.Namespace(FolderPath)

Dim rng As Range, cellul As Range
Set rng = Sheets("archivage").Range("z2:z65000")
For Each cellul In rng
If cellul.Value <> "" Then
On Error GoTo suit
b = b + 1
Dim xlobj2 As Object
Set xlobj2 = CreateObject("Scripting.FileSystemObject")
SetAttr FolderPath & cellul.Value, vbNormal
xlobj2.CopyFile FolderPath & cellul.Value, destination & cellul.Value, True

Set xlobj2 = Nothing
End If
suit::

Next cellul

If b = "" Then
Else
MsgBox "There were: " & b & " tags that were corrected: " & vbCrLf & _
"and which were saved on the external drive."
End If

Sheets("archivage").Range("z2:z65000") = ""
Exit Sub

2 answers

sormick
 
Hello!!

Please help me!!! I'm struggling with this error problem!!
Thank you!!
0
PlacageGranby Posted messages 402 Status Member 26
 
Hello,

I searched for "VBA error 70" on Google, and according to Microsoft,
https://support.microsoft.com/fr-fr/help/147394

CAUSE
This issue occurs if the source file you want to copy is open when you try to run the macro.


Another KB that may be unrelated
https://support.microsoft.com/fr-fr/help/180384
The runtime error "70" is typically the result of a security or permissions issue. The following list outlines possible causes of runtime error 70, but it is neither exhaustive nor definitive.

I looked a bit to see if there are any macros to check a file's status.
I only found code to check if an Excel document is open or not, but nothing for other types of files.

I also came across this site: http://www.info-3000.com/vbvba/fichiers/
Lots of code examples on file copying and different methods. You never know.

Unfortunately, I'm not an Excel whiz who can spot problems at a glance. Especially since your issue might be more related to the environment than the code. So, unless I can reproduce the problem on my computer, it's hard to help you.
0
sormick
 
Hello,

I finally found what was wrong with my code:

This code:
Dim objDrive As Object
With CreateObject("Scripting.FileSystemObject")
For Each objDrive In .Drives
If objDrive.IsReady Then
If objDrive.VolumeName = "VERBATIM HD" Then
destination = objDrive.Path & "\SAUVEGARDE IMPORT\"

was giving me the error mentioned above.

I replaced it with this one which seems to work well:
Private Declare Function GetVolumeInformation Lib "Kernel32" Alias "GetVolumeInformationA" (ByVal lpRootPathName As String, ByVal lpVolumeNameBuffer As String, ByVal nVolumeNameSize As Long, lpVolumeSerialNumber As Long, lpMaximumComponentLength As Long, lpFileSystemFlags As Long, ByVal lpFileSystemNameBuffer As String, ByVal nFileSystemNameSize As Long) As Long
Function GetVolumeName(ByVal cDrive As String) As String
' http://www.codyx.org/snippet_recuperer-nom-attribue-lecteur-disque-cle-etc_863.aspx
' cDrive = CHAR (letter) from A to Z
Dim sBuffer As String
Dim iEnd As Integer

sBuffer = Space$(255)
GetVolumeInformation cDrive & ":\", sBuffer, Len(sBuffer), 0&, 0&, 0&, vbNullString, 0&
iEnd = InStr(1, sBuffer, vbNullChar)
If iEnd Then GetVolumeName = Left$(sBuffer, iEnd - 1)
End Function
Function letterDrive(nomLecteur As String) As String
Dim l As Long
For l = 1 To 26
If GetVolumeName(Chr(64 + l)) = nomLecteur Then
letterDrive = Chr(l + 64)
Exit For
End If
Next l
End Function

Dim r As String, nomLecteur As String
nomLecteur = "VERBATIM HD"
r = letterDrive(nomLecteur)
If r <> "" Then
Dim destination As String
destination = r & ":\SAUVEGARDE IMPORT\"
Else
MsgBox ("Drive '" & nomLecteur & "' not found."), vbCritical
Exit Sub
End If
0