Transfer excel from one folder to other

jimpatel1993
2StarLounger
Posts: 153
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: 1499
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
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

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 »

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

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
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 )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also

jimpatel1993
2StarLounger
Posts: 153
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: 153
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: 153
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: 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 »

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
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 your reply Hans
I am getting error as named argument not found
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 »

On which line? Please help us by providing specific information.
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 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: 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 »

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
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 Hans
Thanks for your time.
I still getting same above mentioned line error.
Only code working for me is one below.
Is anything you can tweek this please?
Sorry for some reason other code does not work for me.
Thanks again
Doc.AElstein wrote:
13 Oct 2021, 11:59
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 )

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 have no further ideas.
Best wishes,
Hans

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

Re: Transfer excel from one folder to other

Post by jimpatel1993 »

No worries Hans
Much appreciated your time and effort.
Thanks a lot

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

Re: Transfer excel from one folder to other

Post by Doc.AElstein »

@Hans
Hi Hans
That OverWrite:=True does not work for me either , in .CopyFile or .MoveFile
( only tried Excel 2007 ( Early and Late Binding ) so far. I will check other Excel versions later perhaps )

_.__________________________________________________________________________

@ jimpatel1993
Try these. They may take longer than the other macros because they copy each file, one at a time.
For if there are already file exists in Destination Folder, then
_ Sub OverwriteExistingFiles() will effectively overwrite existing file. ( It will first delete existing file in Destination , and then move the new one.
or
_ Sub SkipExistingFiles() will not move a file if it already exists in Destination


Code: Select all

 Option Explicit ' http://www.eileenslounge.com/viewtopic.php?p=288885#p288885
Const xSPath = "C:\Users\JimmyRiddle\Desktop\Move file\Source\"
Const xDPath = "C:\Users\JimmyRiddle\Desktop\Move file\Destination\"
Dim Fso As Object
Sub OverwriteExistingFiles()
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, DuhFile As String
    For Each Sfl In Fld.SubFolders
     Call ProcessFolder(Sfl)
    Next Sfl
     Let DuhFile = Dir(Fld.Path & "\*.xls*", vbNormal) ' try to find a Excel file in the folder,  Fld      Dir  will return the string name of the first Excel file it finds
        Do While DuhFile <> ""
            If Fso.FileExists(xDPath & "\" & DuhFile) Then Kill xDPath & "\" & DuhFile ' Delete the existing file in Destination
         Fso.MoveFile Source:=Fld.Path & "\" & DuhFile, Destination:=xDPath
         Let DuhFile = Dir  ' unqualified  Dir  will look again for next file with previous search criteria
        Loop
 Fso.DeleteFolder Fld.Path, True
End Sub

Code: Select all

 Option Explicit ' http://www.eileenslounge.com/viewtopic.php?p=288885#p288885
Const xSPath = "C:\Users\JimmyRiddle\Desktop\Move file\Source\"
Const xDPath = "C:\Users\JimmyRiddle\Desktop\Move file\Destination\"
Dim Fso As Object
Sub SkipExistingFiles()
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, DuhFile As String
    For Each Sfl In Fld.SubFolders
     Call ProcessFolder(Sfl)
    Next Sfl
     Let DuhFile = Dir(Fld.Path & "\*.xls*", vbNormal) ' try to find a Excel file in the folder,  Fld      Dir  will return the string name of the first Excel file it finds
        Do While DuhFile <> ""
            If Fso.FileExists(xDPath & "\" & DuhFile) Then
            ' the file exists already in Destination so I will do nothing
            Else
             Fso.MoveFile Source:=Fld.Path & "\" & DuhFile, Destination:=xDPath
            End If
         Let DuhFile = Dir  ' unqualified  Dir  will look again for next file with previous search criteria
        Loop
 Fso.DeleteFolder Fld.Path, True
End Sub

Alan
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also