My operating system is Microsoft 365 for enterprise (my workplace laptop). My knowledge with regards to VBA/programming is relatively rudimentary, so it would be great if the solution can be easily comprehended. I would be grateful if anyone proposes a solution to my mishap. Prior to running the macro I activated the Microsoft Scripting Runtime reference.
It is a very simplistic macro, column A (from row 2 downwards) contains incumbent pdf names, column B (from row 2 downwards) comprises the new pdf names I aim to set. New_name = Worksheets("Sheet1").Cells(i, 2).Value If f.Name = Worksheets("Sheet1").Cells(i, 1).Value Then Set fo = fso.GetFolder(Worksheets("Sheet1").Cells(2, 5).Value) Last_row = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
'Microsoft Scripting Runtime reference should be enabled If Len(Dir(Left(dFolder, Len(dFolder) - 1), vbDirectory)) = 0 Then MkDir dFolder 'if the path does not exist, create itįCount = fCount + FSO_XCopy(sFolder & objSubFolder.Name & "/", dFolder & objSubFolder.I am struggling to run a macro I encountered on the web and I can't run it due to the following error: "Run-time error 70 - 'permission denied'" Set FSO = CreateObject("Scripting.FileSystemObject") 'opens sys file scripts MsgBox "Source folder not found for function FSO_XCopy!" If Len(Dir(Left(sFolder, Len(dFolder) - 1), vbDirectory)) = 0 Then If Right(dFolder, 1) "\" Then dFolder = dFolder & "\" 'fix folder id If Right(sFolder, 1) "\" Then sFolder = sFolder & "\" 'fix folder id '* CopiedFileCount = FSO_XCopy("C:\SourceFolderName\","C:\DestinationFolderName\")įunction FSO_XCopy(sFolder As String, dFolder As String, Optional MoveFlag As Boolean) As Integer '* RETURNS integer, count of files copied '* is the destination folder path ie: ("C:\DestinationFolderName\") - MUST END WITH "\" '* is the source folder path ie: ("C:\SourceFolderName\") - MUST END WITH "\" This will slow my coding speed by 500 and cost me many hundreds of keystrokes a day.
'* Optional MoveFlag (true/false) if 'True' will delete source data after a copy is complete I am an Access Developer who uses personal custom code to automate the production of VBA Code (in MS Access 2003). '* Recursively copies files from source folder and subfolders to destination folder and subfolders To include all the subfolders it recursively calls itself. This version copies (or moves) files and subfolders - it does not adjust attributes. This fix is still a little slower than normal but there are fewer thunk delays using vba.
Calling a shell to dos works ok but has a few drawbacks, 1st, unless you have a function to wait till the shelled application is complete (search for Shell-N-Wait) before continuing, you have to hold any following commands with a message box and wait till the shelled application is complete before clicking to continue.Ī more recent problem I found is that our office is moving to Windows 7 64bit while still using Office 32bit, the calls I make using shell-n-wait are using kernal32 functions to wail till the shelled process is complete, then thunking the 64 os into a 32bit function it got very very slow to do what is normally a very fast function. I know this is an old thread but I suspect there are others that still need a better xcopy that includes subfolders, I had not been able to find one on the net so I ended up writting one. Set FSO_Fldr = FSO.GetFolder(SourceFolder) Public Function DeleteAllFilesInFolder(SourceFolder) ' Don’t forghet the slash or you got a "Run-time error '70' Permission denied" Open the file for Input or change the write-protection attribute of the file. This error has the following causes and solutions: You tried to open a write-protected file for sequential Output or Append. If Dir(DestinationFolder, vbDirectory) = "" Then Permission denied (Error 70) :-An attempt was made to write to a write-protected disk or to access a locked file. ' Tools/References menu item and check 'Microsoft Scripting Runtime'ĭim strSource As String, strDestination As String, strFileName As String ' You need to have the SCRRUN.DLL library loaded. Public Function CopyAllFilesInFolder(SourceFolder, DestinationFolder) Thanks to everybody for the help!Ĭall CopyAllFilesInFolder("C:\Source", "C:\Destination") I know, I'm late but here my working Access VBA function code.