Issue is, in the source folder have multiple sub folder's where excel file is located which is all in .XLS format. I wanted to transfer all .xls files from source folder (which will have multiple sub folder's ). Is there any generic code where it will search for all .xls file from source folder and transfer to destination just as all .xls files instead of folders?
Sub Button1_Click()
Dim xTFile As String
Dim xExtArr As Variant
Dim xExt As Variant
Dim xSPath As String
Dim xDPath As String
Dim xSFile As String
Dim xCount As Long
xSPath = "C:\Users\Desktop\Move file\Source\"
xDPath = "C:\Users\Desktop\Move file\Destination\"
xExtArr = Array("*.xlsx*", "*.xlsm")
For Each xExt In xExtArr
xTFile = Dir(xSPath & xExt)
Do While xTFile <> ""
xSFile = xSPath & xTFile
FileCopy xSFile, xDPath & xTFile
Kill xSFile
xTFile = Dir
xCount = xCount + 1
Loop
Next
End Sub
Do you want all .xls files to be copied to the destination folder (as opposed to subfolders of the destination folder)?
Could there be duplicate file names, for example MyBook.xls in Subfolder1 of the source folder and also MyBook.xls in Subfolder2 of the source folder?
If so, how should that be handled?
Hi Hans
Thanks for your reply.
There won't be any duplicate files in the subfolder. Everything will be named unique.
Yes you are right. I wanted to cut and transfer all. Xls files or any other excel related files from multiple subfolder located in specific top folder to destination folder as one file location please.
Const xSPath = "C:\Users\Desktop\Move file\Source\"
Const xDPath = "C:\Users\Desktop\Move file\Destination\"
Dim fso As Object
Sub Button1_Click()
Dim fld As Object
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
Set fld = fso.GetFolder(xSPath)
Call ProcessFolder(fld)
End Sub
Sub ProcessFolder(fld As Object)
Dim sfl As Object
fso.MoveFile Source:=xSPath & "*.xls*", Destination:=xDPath
For Each sfl In fld.SubFolders
Call ProcessFolder(sfl)
Next sfl
End Sub
Hi Hans
I am not able to transfer from subfolders unfortunately.
I am getting an error as path not found.
Basically what I am trying to do is I have different excels like. Xlsm,. Xlsx under source folder. Example
Source folder - under that folder 1, folder, folder 100, etc... Where excel files will be located. I wanted to transfer those excel files to destination folder with only excel files without subfolders
Sorry for poor explanation
Thanks a lot again
Sorry Hans
Still does not work unfortunately.
Am I doing anything wrong?
Const xSPath = "C:\Users\Desktop\Move file\Source\"
Const xDPath = "C:\Users\Desktop\Move file\Destination\"
Dim fso As Object
Sub Button1_Click()
Dim fld As Object
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
Set fld = fso.GetFolder(xSPath)
Call ProcessFolder(fld)
End Sub
Sub ProcessFolder(fld As Object)
Dim sfl As Object
fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath
For Each sfl In fld.SubFolders
Call ProcessFolder(sfl)
Next sfl
End Sub
Make absolutely sure that the paths are correct.
I have now tested the code and it worked exactly as intended. It moved all workbooks from the source folder and its subfolders to the destination folder.
Basically if I put source path location as one of the folder name it is transferring from that folder only and code works for that. But it's not taking other folders into account.
Example
"c:\users\Desktop\Move file\source\Folder1\"
This is transferring all excel from Folder1
But when I remove folder1 I am getting error as file not found.
Thanks again
Can you please share your code what you used in your system please?
Should be same as you provided before but I guess I am not using path code properly.
Thanks and sorry for more question.
Thanks
Thanks for sharing.
You mentioned in your previous post that
"Declaration should be at the beginning under option Explict if I have any"
Is that some thing I am going wrong?
I am using below code. Is that path declaration specified correctly in my code please?
Thanks again
Const xSPath = "C:\Users\Desktop\Move file\Source\"
Const xDPath = "C:\Users\Desktop\Move file\Destination\"
Dim fso As Object
Sub Button1_Click()
Dim fld As Object
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
Set fld = fso.GetFolder(xSPath)
Call ProcessFolder(fld)
End Sub
Sub ProcessFolder(fld As Object)
Dim sfl As Object
fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath
For Each sfl In fld.SubFolders
Call ProcessFolder(sfl)
Next sfl
End Sub
Thanks