Transfer excel from one folder to other

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Oops
Sorry
There you go
Thanks a lot
You do not have the required permissions to view the files attached to this post.

User avatar
Doc.AElstein
BronzeLounger
Posts: 1427
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfer excel from one folder to other

Post by Doc.AElstein »

OK, so you have on your computer these folders

C:\Users\JimmyRiddle\Desktop\Move file
C:\Users\JimmyRiddle\Desktop\Move file\Destination
C:\Users\JimmyRiddle\Desktop\Move file\Source
C:\Users\JimmyRiddle\Desktop\Move file\Source\d
C:\Users\JimmyRiddle\Desktop\Move file\Source\d\New folder
C:\Users\JimmyRiddle\Desktop\Move file\Source\d\New folder\New folder
C:\Users\JimmyRiddle\Desktop\Move file\Source\df
C:\Users\JimmyRiddle\Desktop\Move file\Source\Kali


( I did a JimmyRiddle where you should be )

That all looks good


Try these, ( remember to change JimmyRiddle )

Code: Select all

Sub Test1()
Dim fso As Object
Dim xDesktop As String
Dim xSPath  As String
Dim xDPath As String
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
 Let xDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    If xDesktop = "" Then MsgBox prompt:="You don't have any folder with name Desktop": Exit Sub
' Let xSPath = xDesktop & "\Move file\Source\"
' Let xDPath = xDesktop & "\Move file\Destination\"
 Let xSPath = "C:\Users\JimmyRiddle\Desktop\Move file\Source\"       ' ---  CHANGE  JimmyRiddle
 Let xDPath = "C:\Users\JimmyRiddle\Desktop\Move file\Destination\"  '  ---  CHANGE  JimmyRiddle
    If Dir(xSPath, vbDirectory) = "" Then MsgBox prompt:="You don't have a path  " & xSPath: Exit Sub
    If Dir(xDPath, vbDirectory) = "" Then MsgBox prompt:="You don't have a path  " & xDPath: Exit Sub
    If Dir(xSPath & "*.xls*", vbNormal) = "" Then MsgBox prompt:="You don't have any Excel files at  " & xDesktop & "\Move file\Source\" & "": Exit Sub
 fso.MoveFile Source:=xSPath & "*.xls*", Destination:=xDPath
End Sub

Sub Test2()
Dim fso As Object
Dim xDesktop As String
Dim xSPath  As String
Dim xDPath As String
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
 Let xDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
    If xDesktop = "" Then MsgBox prompt:="You don't have any folder with name Desktop": Exit Sub
' Let xSPath = xDesktop & "\Move file\Source\"
' Let xDPath = xDesktop & "\Move file\Destination\"
 Let xSPath = "C:" & Application.PathSeparator & "Users" & Application.PathSeparator & "JimmyRiddle" & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Move file" & Application.PathSeparator & "Source" & Application.PathSeparator & "" ' ---  CHANGE  JimmyRiddle
 Let xDPath = "C:" & Application.PathSeparator & "Users" & Application.PathSeparator & "JimmyRiddle" & Application.PathSeparator & "Desktop" & Application.PathSeparator & "Move file" & Application.PathSeparator & "Destination" & Application.PathSeparator & ""  '  ---  CHANGE  JimmyRiddle
    If Dir(xSPath, vbDirectory) = "" Then MsgBox prompt:="You don't have a path  " & xSPath: Exit Sub
    If Dir(xDPath, vbDirectory) = "" Then MsgBox prompt:="You don't have a path  " & xDPath: Exit Sub
    If Dir(xSPath & "*.xls*", vbNormal) = "" Then MsgBox prompt:="You don't have any Excel files at  " & xDesktop & "\Move file\Source\" & "": Exit Sub
 fso.MoveFile Source:=xSPath & "*.xls*", Destination:=xDPath
End Sub
\ -_- / :heavy: :jollyroger:

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

Re: Transfer excel from one folder to other

Post by HansV »

We're back at one of my first replies.

Code: Select all

Const xSPath = "C:\Users\1234\Desktop\Move file\Source\"
Const xDPath = "C:\Users\1234\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
Change 1234 to your login name.
Last edited by HansV on 13 Oct 2021, 12:05, edited 1 time in total.
Reason: to correct mistake (thanks, Alan)
Regards,
Hans

User avatar
Doc.AElstein
BronzeLounger
Posts: 1427
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Re: Transfer excel from one folder to other

Post by Doc.AElstein »

and this would be the corresponding example version of Hans original macro that I did you

Code: Select all

Option Explicit
Const xSPath = "C:\Users\JimmyRiddle\Desktop\Move file\Source"
Const xDPath = "C:\Users\JimmyRiddle\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
    If Not Dir(fld.Path & "\*.xls*", vbNormal) = "" Then fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath
    For Each sfl In fld.SubFolders
     Call ProcessFolder(sfl)
    Next sfl
End Sub
( I did a JimmyRiddle again where you should be )
\ -_- / :heavy: :jollyroger:

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Wowww
This is amazing work guys.
I wish I ring you guys and say thank you for this amazing piece of work.
Unfortunately Hans your code I am still getting an error. But last version of Jimmy works perfectly fine in my system.
Thanks both of you for this effort.
Again don't know how to say thanks but this is best work guys.
Just one question how do I run this macro automatically at 6 pm every evening. Of course excel will be opened all the time.
Thanks again

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

I found it.

Thanks a lot guys for your brilliant work

Cheers
Jim

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Hi Guys
Just wanted to say this code works brilliant for last 2 days.
1. Just noticed if there is any duplicate file then I am getting pop up message as file already exist and its getting aborted.
Is there anything I can skip this and automatically run the code pls?
I wanted to leave this excel opened all the time and any fully automation will be much helpful

2. Secondly, of course excels are transfered perfectly, is there any option to delete folder completely after transfer?
Because I am left with so many empty folders after transfer
Thanks again

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

Re: Transfer excel from one folder to other

Post by HansV »

See if this works for you:

Code: Select all

Option Explicit

Const xSPath = "C:\Users\JimmyRiddle\Desktop\Move file\Source"
Const xDPath = "C:\Users\JimmyRiddle\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
    For Each sfl In fld.SubFolders
     Call ProcessFolder(sfl)
    Next sfl
    If Dir(fld.Path & "\*.xls*") <> "" Then
        fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath, OverWrite:=True
    End If
    fso.DeleteFolder fld.Path, True
End Sub
Regards,
Hans

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Thanks for your reply Hans
I am getting error as named argument not found
Thanks a lot again

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

Re: Transfer excel from one folder to other

Post by HansV »

On which line? Please help us by providing specific information.
Regards,
Hans

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

Thanks for your reply Hans
I am getting an error as
Named argument not found and in line
Fso. Movefile source :=fld. Path & "\*. Xls*", Destination :=xdpath, overwrite := True
Thanks a lot

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

Re: Transfer excel from one folder to other

Post by HansV »

Sorry, Overwrite is only for CopyFile, not for MoveFile.
Try

Code: Select all

Option Explicit

Const xSPath = "C:\Users\JimmyRiddle\Desktop\Move file\Source"
Const xDPath = "C:\Users\JimmyRiddle\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
    For Each sfl In fld.SubFolders
     Call ProcessFolder(sfl)
    Next sfl
    If Dir(fld.Path & "\*.xls*") <> "" Then
        fso.CopyFile Source:=fld.Path & "\*.xls*", Destination:=xDPath, OverWrite:=True
    End If
    fso.DeleteFolder fld.Path, True
End Sub
Regards,
Hans