PDA

View Full Version : [SOLVED] excel vba permission denied run time error 70



voyagerphoen
01-03-2019, 12:36 AM
This is a repost of excel vba permission denied as i reported to delete that post.I found this code in tutorial in youtube .This code should create folder named vbscripting in documents folder and copy all excel files from documents into it but it pops up error message run time error 70 permission denied.It works fine when i change address to D: drive or any other ,But it seems to not working in documents folder.I have checked the permission of that folder and even files in it but no. I have also tried to run excel in compatibility mode, please help.


Dim newfolderpath As StringDim fso As Scripting.FileSystemObject

Sub usingthescriptingruntimelibrary3()
Dim oldfolderpath As String
newfolderpath = Environ("userprofile") & "\Documents\vbscripting"
oldfolderpath = Environ("Userprofile") & "\Documents"
Set fso = New Scripting.FileSystemObject
If fso.FolderExists(oldfolderpath) Then
If Not fso.FolderExists(newfolderpath) Then
fso.createfolder newfolderpath
End If
Call copyexcelfiles(oldfolderpath)
End If
Set fso = Nothing
End Sub

Sub copyexcelfiles(StartFolderPath As String)
Dim oldfolder As Scripting.Folder
Dim subfol As Scripting.Folder
Dim fil As Scripting.File
Set oldfolder = fso.GetFolder(StartFolderPath)
For Each fil In oldfolder.Files
If Left(fso.GetExtensionName(fil.Path), 2) = "xl" Then
fil.Copy newfolderpath & "\" & fil.Name
End If
Next fil
For Each subfol In oldfolder.SubFolders
Call copyexcelfiles(subfol.Path)
Next subfol
End Sub

Logit
01-03-2019, 11:05 AM
.
Not certain why your macro is not functioning as desired. Sorry.

The following works here :



Option Explicit


'''
'strPathSource: The full path of the file to copy
'strPathOutput: The full to copy the file to
Private Sub Copy_File(ByVal strPathSource _
As String, ByVal strPathOutput As String)
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
'copy file
Call objFSO.CopyFile(strPathSource, strPathOutput)
End Sub


Sub Example1()
On Error GoTo err:
Call Copy_File(Environ("userprofile") & "\Documents\*.xls*", _
Environ("userprofile") & "\Documents\vbscripting")
Exit Sub
err: MsgBox "No Excel files.", vbInformation, "Error"
End Sub



NOTE: This macro does not auto-create the folder "vbscripting" if it does not already exist. You can borrow theportion of code from your macro to accomplish same.

voyagerphoen
01-07-2019, 01:47 AM
But in my case it creates folder "vbscripting" and it also copies files to that folder but with error message(Run time error 70)

Logit
01-07-2019, 09:06 AM
.
Perhaps one or more of your files in the DOCUMENT folder is protected ?

Dave
01-07-2019, 11:47 AM
FYI Logit.. I've already posted a working solution to this same post that was never acknowledged and then removed from this forum at the request of voyagerphone. Dave

Logit
01-07-2019, 01:00 PM
.
Thanks Dave.