Transfer excel from one folder to other

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Transfer excel from one folder to other

Post by jimpatel1993 »

Hi
Thanks for looking at my post

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?

Code: Select all

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
Thanks a lot again

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

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?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

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.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

Try this. The declarations at the beginning should be at the top of the module (below Option Explicit if you have that):

Code: Select all

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
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Let me Try this
Thanks a lot again

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

I'm sorry, my fault, it was air code. Change the line

Code: Select all

    fso.MoveFile Source:=xSPath & "*.xls*", Destination:=xDPath
to

Code: Select all

    fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

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

Thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

Have you changed xSPath and xDPath to the real paths?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

You mean in the code?
Yes

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

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.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Does it néed to be any specific extension?
Thanks again

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

What if you set the source folder to "c:\users\Desktop\Move file\source" ?
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Still same
Sorry hans

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

I'm sorry, I can't offer further help. I have checked that the code works if the paths are correct...
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Hmmm
Let me Try again with different path.
Thanks Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Transfer excel from one folder to other

Post by HansV »

I used the exact code that I posted, I only changed the two constants

Code: Select all

Const xSPath = "C:\Users\Desktop\Move file\Source\"
Const xDPath = "C:\Users\Desktop\Move file\Destination\"
to paths on my computer:

Code: Select all

Const xSPath = "C:\Users\javog\Documents\Excel\Source\"
Const xDPath = "C:\Users\javog\Documents\Excel\Destination\"
Those paths don't exist on your computer, of course.
Best wishes,
Hans

jimpatel1993
2StarLounger
Posts: 153
Joined: 31 Jan 2021, 09:12

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

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

Code: Select all

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