File Copy Failing if Path Doesn't Exist

Hello,

I have the following code which copies files from a list of paths in column A and puts them in a path which is in column B. The issue I am having is if the path doesn't exist in column B where the copies are to be placed the macro bombs out. Is there something simple I can add to my code which works when the folders exists as it is to create the needed folders if they don't exist?

Please Login or Register  to view this content.

an example of column A and Column B below. However it won't end up on the C drive. It will end up on a network drive "M".

c:\Users\anthony.martin\Desktop\New folder\200 Lafayette - Office CAPEX 2017.09.pdf C:\Users\anthony.martin\Desktop\New folder\Test\2017\Q3\0-Properties\200 Lafayette-48010\200 Lafayette - Office CAPEX 2017.09.pdf
c:\Users\anthony.martin\Desktop\New folder\200 Lafayette - Retail CAPEX 2017.09.pdf C:\Users\anthony.martin\Desktop\New folder\Test\2017\Q3\0-Properties\200 Lafayette-48010\200 Lafayette - Retail CAPEX 2017.09.pdf

Change this

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

will this change create the folders in the path if they are missing so that it copies the file? I don't want it to skip files. I want it to create the missing folders to place the copy of the pdf into the string of folders.

It will not. My apologies for not reading your post completely and misunderstanding the request. Hang tight.

no need for an apology. I appreciate your help.

I am getting a byreft argument type mismatch compile error on this line at the (FileDestination) in the If section shown below.

If makeDir(getDirName(FileDestination)) Then FileCopy FileSource, FileDestination

Change the top of your Sub to this and let me know if it continues.

Please Login or Register  to view this content.

Thank you. Now I am getting a compile error for user-defined type not defined in the function section in the Dim section below.

Public Function getDirName(sFilePath As String) As String

' Get the directory

Dim filesystem As New FileSystemObject

Within Excel you need to set a reference to the VB script run-time library. The relevant file is usually located at \Windows\System32\scrrun.dll or \Windows\SysWOW64\scrrun.dll

• To reference this file, load the Visual Basic Editor (ALT+F11)
• Select Tools > References from the drop-down menu
• A listbox of available references will be displayed
• Tick the check-box next to 'Microsoft Scripting Runtime'
• The full name and path of the scrrun.dll file will be displayed below the listbox
• Click on the OK button.

Note: This is a setting for your installation of Excel. If other people want to use your macro, they will need to follow these steps to set the reference as well.

Thank you. Now it is saying pathexists subs or functions is not defined under the make directory function.

Public Function makeDir(sDirPath As String) As Boolean

' Function to recursively make a directory if it doesn't exist

Dim vDirPart As Variant
Dim sRecurDir As String

If pathExists(sDirPath) Then

Thanks for all of your help with this. I really appreciate it.

Originally Posted by amartin575

Thank you. Now it is saying pathexists subs or functions is not defined under the make directory function.

Public Function makeDir(sDirPath As String) As Boolean

' Function to recursively make a directory if it doesn't exist

Dim vDirPart As Variant
Dim sRecurDir As String

If pathExists(sDirPath) Then

Yeah in my "Try this" post, I missed a function when I copy-pasted. I added it via edit but it seems you were quicker than me. It is in the above code block (for later people) but here it is again, isolated for easy copy.

Please Login or Register  to view this content.

you are the bomb smpita! worked like a champ and finished within seconds!

Thanks again!

Hello smpita, So the code that I thought worked partially works. It only moves the file if the path doesn't exist. It then creates the folders and puts a copy of the file in that location. The code doesn't put a copy to the location if the specified path already exists. Is there an ElseIf that I can place in this code to get it to work for paths that already exist?

Please Login or Register  to view this content.

nevermind I think I figured it out by adding an Else to it.

The actual issue is here

Please Login or Register  to view this content.

If you change it to this, it should fix the issue.

Please Login or Register  to view this content.

When the directory existed, it would exit from the function without setting the boolean, defaulting the boolean to a false in the following IF conditional.

Please Login or Register  to view this content.

This change sets the boolean to True when the directory exists and allows the IF conditional to pass, subsequently executing the FileCopy command.