Macro to move files from one folder to another

Topic

New Reply

I have a macro to move files from C:Journal Templates to C:old JNLS

However when activating the macro, it comes up with a message”file already exists” and the following code is highlighted – FSO.moveFile Source:=FromPath & FileExt, Destination:=ToPath . All I want to do is to move the files in folder “C:Journal Templates” to”C:old JNLS

Your assistance in resolving this is most appreciated

Code:

Sub Move_JNL_Folder()

    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String

    FromPath = “C:Journal Templates”
    ToPath = “C:old JNLS”


    FileExt = “*.*” 

    If Right(FromPath, 1)  “” Then
    FromPath = FromPath & “”
    End If

    Set FSO = CreateObject(“scripting.filesystemobject”)

    If FSO.FolderExists(FromPath) = False Then
    MsgBox FromPath & ” doesn’t exist”
    Exit Sub
    End If

    If FSO.FolderExists(ToPath) = False Then
    MsgBox ToPath & ” doesn’t exist”
    Exit Sub
    End If

    FSO.moveFile Source:=FromPath & FileExt, Destination:=ToPath
    MsgBox “You can find the files from ” & FromPath & ” in ” & ToPath

    End Sub 

   

http://www.mrexcel.com/forum/excel-questions/720170-macro-delete-items-folder.html

Reply | Quote

Replies

I believe with FSO it is not possible to move a file if the file already exists in the destination. If the files may already exist (as you seem to be doing with your code) then you must first copy the files (setting the overwrite parameter on the copy) and then delete (the old files)

For further info on FSO methods see http://technet.microsoft.com/en-us/library/ee176983.aspx

Steve

Reply | Quote

Hi Howard

As Steve says, you can’t use FSO if the file already exists in the destination folder.
In addition, you can’t use FSO if any of the files are currently open.
In addition, you can’t use FSO if the file you are using (i.e containing the code) is one of the source files.

I have attached a workbook containing a moveFile routine.
This routine uses the very efficient vba Name function to move files.

If you imagine that all of the files you want to move are in your house, the VBA Name function makes moving house simple by just changing the number on your mailbox. Copy file and Kill methods require you to ‘load’ a big truck and ‘unload’ the truck at the new address. That can take a while.

My routine skips any files that are currently open, and also allows you to specify combinations of types of files to be moved by file extension (e.g. *.xls*, *.doc*, *.csv) etc.

zeddy

Reply | Quote

Hi Zeddy

It would be appreciated if you could amend your code to overwrite the files in the destination folder as as use the same file name each month to copy from the source folder to the destination folder

Regards

Howard

Reply | Quote

Hi Howard

In the attached file, I added a vba line to ‘Kill’ the file in the destination folder before the move.
The ‘on error resume next’ will ignore the Kill command if the file being moved isn’t in the destination folder.
I haven’t tested it, but I’m sure it will do the trick.
This version does not deal with any files that are currently open, but a further amendment could automatically close any such files if required. The only requirement would be whether the opened file should be saved or not saved before closing.

Please test and let me know if the attached version does what you require.

zeddy

Reply | Quote

Hi Zeddy

I have amended the folder names and have used you code several times. I would like he code amemded for one folder where all the files are to be moved, except a workbok which has the name “consolidation” in the name for eg “Group1 Consolidated Tax” It would be appreciated if you would amend your code accordingly

Howard

Reply | Quote

Hi Zeddy & Steve

Thanks for your explanations. Zeddy thanks for the code, it works perfectly

Regards

Howard

Reply | Quote

Hi Zeddy

It works perfectly-thanks very much

Regards

Howard

Reply | Quote

Hi Howard

That’s twice you said that.
Nothing’s perfect.
Mostly.

zeddy

Reply | Quote

Hi Zeddy

It worked perfectly the first time as I has no files in the destination folder. I have tested your macro several times and it works each time without any problem

Regards

Howard

Reply | Quote

Hi Howard

Now I follow. Thanks for the explanation.

zeddy

Reply | Quote

Hi Zeddy

A while back you assisted me to write code to move files from one folder to another. I now have a situation where I want to copy all xls files from folder “C:Pull” including subdirectories in Pull to “C:Summary Profit reports”

It would be appreciated if you would amend your code accordinly

Reply | Quote

Hi Howard

I’ve been away.

What do you want to happen if you have multiple copies of a particular filename zzzzzz.xls in several subdirectories of C:Pull ?????
For example, you could just overwrite the C:Summary Profit reportszzzzzz.xls with whatever the last one found

zeddy

Reply | Quote

Hi Zeddy

Hope you are keeping well. I tried to adapt some code which you provided to me several months ago. What I need to do is to move csv files from the folders name containing the name for eg C:Payroll Files BR1, C:Payroll Files Br2 etc to C:old payroll files

When actiovating the macro, it comes up with message, Error :76 Path not found. It would be apreciated if you could assist-see code below

Sub MoveFilesToAnotherFolder()

Dim objFSO As Object ‘FileSystemObject
Dim objFile As Object ‘File
Dim objFolder As Object ‘Folder
Const strFolder As String = “C:Payroll Files*.*”
Const strNewFolder As String = “C:Old Payroll Files”
Set objFSO = CreateObject(“Scripting.FileSystemObject”)
For Each objFolder In objFSO.GetFolder(strFolder & “”).SubFolders
If Right(objFolder.Name, 2) = “tb” Then
For Each objFile In objFolder.Files
If InStr(1, objFile.Type, “CSV”, vbTextCompare) Then
Name objFile.Path As strNewFolder & “” & objFile.Name
End If
Next objFile
End If
Next objFolder

End Sub

Reply | Quote

Hi Howard

..you were nearly there, but you can’t use
Const strFolder As String = “C:Payroll Files*.*”
..nice try though!

Try this instead:

Code:

Sub MoveFilesToAnotherFolder()

Dim objFSO As Object 		'FileSystemObject
Dim objFile As Object 		'File
Dim objFolder As Object 	'Folder
 
Const strNewFolder As String = "C:Old Payroll Files"

Set objFSO = CreateObject("Scripting.FileSystemObject")
 
For Each objFolder In objFSO.GetFolder("c:").SubFolders
If objFolder.Name Like "Payroll Files *" Then

For Each objFile In objFolder.Files
If InStr(1, objFile.Type, "CSV", vbTextCompare) Then
Name objFile.Path As strNewFolder & "" & objFile.Name
End If
Next objFile

End If
Next objFolder

End Sub 

Let me know if this does the trick

zeddy

Reply | Quote

Hi Zeddy

I thought that you may have neen away. Did you have a bit of a holiday? The xls files that are copied from C:Pull + subdirectories (including C:workfilez_ACCNTS (forgot mention this in earlier post) are to be copied to C:Summary Profit reports. Existing Xls files in C:Summary Profit reports can be overwritten when the files are copied accross

Howard

Reply | Quote

Hi Howard

I had a nice trip to New York.
Thanks for asking.

Now, I have attached a file which should do as you want.
Let me know if this works for you.

zeddy

Reply | Quote

Hi Zeddy

Glad you had a nice trip. My next holiday, I definately want to visit NY.

Thanks for the help. Kindly amend your code to copy the files and NOT to move these

Howard

Reply | Quote

Hi Howard

OOops!

Previous version will move files from subfolders.
This attached file will copy files, as requested.

Let me know if this works for you.

zeddy

Reply | Quote

Hi Zeddy

Hope u had a good trip. When u have a chance please look at my post # 18

Howard

Reply | Quote

Hi Howard

Yes, the trip was spectacular! Can’t wait to go back again!

In the last file posted, there were two folders, and files in both folders (and sub-folders) were copied into a single specified folder.
So are you now asking for one folder where files are to be moved, and leave the other folder where files are to be copied???

zeddy

Reply | Quote

Hi Zeddy

Thanks for the help. Code works perfectly

Howard

Reply | Quote

Hi Zeddy

Sorry should have been clearer. I have another folder called “Tax Schedules” . I would like to move all the workbooks in this folder (these are Excel Files), except the workbook/s “Consolidation” in the workbook name for eg Group1 Consolidated Tax” . I need these moved to “Old Tax Schedules”

Howard

Reply | Quote

Hi Howard

Try the attached file. I haven’t tested it fully because I would need to create sample files etc etc.
Please test it and let me know if it works for you.

zeddy

Reply | Quote

Hi Zeddy

It works perfectly. Thanks very much for all the assistance

Howard

Reply | Quote

Hi Zeddy

Thanks for the help. None of the CSV files have moved from the folders for eg C:Payroll Files BR1 etc to C:Old Payroll Files

Please test and advise

Thanks

Howard

Reply | Quote

Hi Howard

..I should’ve tested it first, but that means creating test files, test folders and then testing.

Which I have now done.
So try this version:

Code:

Sub MoveFilesToAnotherFolder()

Dim objFSO As Object        'FileSystemObject
Dim objFile As Object       'File
Dim objFolder As Object     'Folder
 
Const strNewFolder As String = "C:Old Payroll Files"  '<<--change destination folder as required

Set objFSO = CreateObject("Scripting.FileSystemObject")
 
For Each objFolder In objFSO.GetFolder("C:").SubFolders  '<<--change start folder as required
zName = objFolder.Name
If zName Like "Payroll Files *" Then

For Each objFile In objFolder.Files
zFile = objFile.Name
If zFile Like "*.csv" Then
Name objFile.Path As strNewFolder & "" & objFile.Name
End If
Next objFile

End If
Next objFolder

End Sub

Let me know if this does the trick

zeddy

Reply | Quote

Hi Zeddy

Sorry for not getting back to you, but have been traveling out of town.

When activating the Macro, nothing happens.

I think it may have to do with this line of code

Code:

 For Each objFolder In objFSO.GetFolder("C:").SubFolders  '<<--change start folder as required 

I'm not sure what I need to change

Your assistance in this regard is most appreciated

Reply | Quote

Hi Howard

I don’t think you need to change that line of code, unless you use an external drive other than C:
..the macro looks for folders like..
C:Payroll Files BR1
C:Payroll Files BR2
C:Payroll Files xxxxxxx

Please check that this is the kind of folder name you are using.
(Or let me know the exact name of the folder you are using)

zeddy

Reply | Quote

How about this:

‘
‘*** delete the old output file if it exists
‘
If fs.fileexists(output_name) Then fs.deletefile (output_name) ‘ delete the output file

Reply | Quote

Hi Alan

Thanks for your input. It would be appreciated if you would expand on this

Regards

Howard

Reply | Quote

Hi Zeddy

For some unknown reason the CSV files in the folder C:payroll files BR1 etc is not recognized. I copied another CSV file into the folder and it was copied into the folder “Old Payroll Files. I have attached a sample csv file in one of the Payroll files folder for you to look at. I’m sure that you will be able to find the solution. I saved the file as an excel file as well as when I open the zip file it seems to be corrupted

Regards

Howard

Reply | Quote

Hi Howard

I can open the zip file without any problems. No corruption in the csv file for me.

Now, regarding “the CSV files in the folder C:payroll files BR1 etc is not recognized“.
This is because the existing code I provided is case sensitive.
This means that C:payroll files BR1 is not the same as C:Payroll Files BR1 and CSV is not the same as csv

You have several choices here:
1. You can make sure the folder names are like C:Payroll Files xxxx and make sure the csv files are like xxx.csv and NOT like xxx.CSV
2. Amend the code so that it ignores the UPPER or lower case and works for both

To amend the code, change the line..
If zName Like “Payroll Files *” Then
to..
If UCase(zName) Like “PAYROLL FILES *” Then

..and change the line..
If zFile Like “*.csv” Then
to..
If UCase(zFile) Like “*.CSV” Then

I have attached a file with these changes.

Try that and let me know if that fixes it.

zeddy

Reply | Quote

Hi Zeddy

I need one small change. Where there is an existing file in C:Old Payroll Files, It must be overwritten

Kindly amend your code to accommodate this

Thanks

Howard

Reply | Quote

Hi Howard

“Where there is an existing file in C:Old Payroll Files, It must be overwritten”
This is what alan sh was alluding to in his post#28

Instead of overwriting an existing file, I delete it using the Kill statement, then move the file as before.

Ok, here’s the updated file.

kindly try this version.

zeddy

Reply | Quote

Hi Zeddy

It works perfectly now-thanks for all the help

Regards

Howard

Reply | Quote

Hi Zeddy

Thanks for your input, much appreciated

Howard

Reply | Quote