Transfer excel from one folder to other
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
Oops
Sorry
There you go
Thanks a lot
Sorry
There you go
Thanks a lot
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
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 )
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
You can find me at DocAElstein also
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
We're back at one of my first replies.
Change 1234 to your login name.
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
Last edited by HansV on 13 Oct 2021, 12:05, edited 1 time in total.
Reason: to correct mistake (thanks, Alan)
Reason: to correct mistake (thanks, Alan)
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
and this would be the corresponding example version of Hans original macro that I did you
( I did a JimmyRiddle again where you should be )
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 am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
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
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
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
I found it.
Thanks a lot guys for your brilliant work
Cheers
Jim
Thanks a lot guys for your brilliant work
Cheers
Jim
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
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
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
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
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
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
Thanks for your reply Hans
I am getting error as named argument not found
Thanks a lot again
I am getting error as named argument not found
Thanks a lot again
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
On which line? Please help us by providing specific information.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
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
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
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
Sorry, Overwrite is only for CopyFile, not for MoveFile.
Try
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
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
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
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:59and this would be the corresponding example version of Hans original macro that I did you
( I did a JimmyRiddle again where you should be )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
-
- Administrator
- Posts: 78465
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
No worries Hans
Much appreciated your time and effort.
Thanks a lot
Much appreciated your time and effort.
Thanks a lot
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
@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
Alan
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
You can find me at DocAElstein also