Transfer excel from one folder to other
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
C:\Users\Desktop\ is probably not a valid path. If you want to refer to your desktop, it should be C:\Users\<yourusername>\Desktop\ where <yourusername> is your Windows login name.
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 12615
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: Transfer excel from one folder to other
Are you SURE that you have a folder at
C:\Users\Desktop\
This would be very unusual.
My desktop folder is at
C:\Users\Stuart\Desktop\
C:\Users\Desktop\
This would be very unusual.
My desktop folder is at
C:\Users\Stuart\Desktop\
StuartR
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
My desktop is at this place
C:\Users\Elston\Desktop
I ran this macro, and it confirmed it.
@jimpatel1993
Run macro Sub GetChaDesktopPath() , and tell us what it says to you
_._____________________________________________________________________________________________
Ref: https://excel.tips.net/T008233_Finding_ ... sktop.html
C:\Users\Elston\Desktop
I ran this macro, and it confirmed it.
Code: Select all
Sub GetChaDesktopPath()
Dim oWSHShell As Object
Set oWSHShell = CreateObject("WScript.Shell")
MsgBox prompt:=oWSHShell.SpecialFolders("Desktop"): Debug.Print oWSHShell.SpecialFolders("Desktop") ' On my computer I get C:\Users\Elston\Desktop
Set oWSHShell = Nothing
End Sub
@jimpatel1993
Run macro Sub GetChaDesktopPath() , and tell us what it says to you
_._____________________________________________________________________________________________
Ref: https://excel.tips.net/T008233_Finding_ ... sktop.html
Last edited by Doc.AElstein on 11 Oct 2021, 16:15, edited 2 times in total.
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
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
I can get Hans macros to work, like this......
This is what I start with:
Then I run this Sub Button1_Click()
After the macro is finished I see this
_.____________________________________________________________________-
Note: macro will error if not at least one file in every source folder and Sub folders
This small mod will stop that error
If Not Dir(fld.Path & "\*.xls*", vbNormal) = "" Then fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath
This is what I start with:
Then I run this Sub Button1_Click()
Code: Select all
Const xSPath = "C:\Users\Elston\Desktop\Move file\Source"
Const xDPath = "C:\Users\Elston\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
_.____________________________________________________________________-
Note: macro will error if not at least one file in every source folder and Sub folders
This small mod will stop that error
If Not Dir(fld.Path & "\*.xls*", vbNormal) = "" Then fso.MoveFile Source:=fld.Path & "\*.xls*", Destination:=xDPath
Code: Select all
Option Explicit
Const xSPath = "C:\Users\Elston\Desktop\Move file\Source"
Const xDPath = "C:\Users\Elston\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
Wow
Fantastic guys
Thanks everyone for your input.
I love this forum due to this.
Again much appreciated guys
Fantastic guys
Thanks everyone for your input.
I love this forum due to this.
Again much appreciated guys
-
- Administrator
- Posts: 78536
- 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
OK
I found the reason why code work partially for me.
I have source folder inside that I have multiple subfolders and inside that I have so many folders to get in to excel. Guess that's why I keep getting an error.
Is there any thing I can do?
Is there any way to search just excel files in the source folder and transfer those to destination?
Thanks again
I found the reason why code work partially for me.
I have source folder inside that I have multiple subfolders and inside that I have so many folders to get in to excel. Guess that's why I keep getting an error.
Is there any thing I can do?
Is there any way to search just excel files in the source folder and transfer those to destination?
Thanks again
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
Try this:
Code: Select all
Sub Button1_Click()
Dim fso As Object
Dim xDesktop As String
Dim xSPath As String
Dim xDPath As String
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
xDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop")
xSPath = xDesktop & "\Move file\Source\"
xDPath = xDesktop & "\Move file\Destination\"
fso.MoveFile Source:=xSPath & "*.xls*", Destination:=xDPath
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.
Unfortunately I am getting run time error 76
Thanks a lot Hans
Unfortunately I am getting run time error 76
Thanks a lot Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
What does this do
_.________________________________________________________________________________________________________________
Note also that folder names are case sensitive, so , for example,
Move File
and
Move file
are not the same folder name
Alan
Code: Select all
' https://eileenslounge.com/viewtopic.php?p=288662#p288662
Sub Button1_Click()
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\"
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
Note also that folder names are case sensitive, so , for example,
Move File
and
Move file
are not the same folder name
Alan
Last edited by Doc.AElstein on 12 Oct 2021, 15:52, edited 3 times in total.
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: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
That means the path was not found. You'll have to change xSPath and xDPath to the actual paths on your computer.
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
Thanks a lot for your kind help guys.
Really appreciate your effort in helping people like me.
Unfortunately I still get error and this time bad file name.
I have attached code below.
Anything I am doing wrong?
Thanks again
Really appreciate your effort in helping people like me.
Unfortunately I still get error and this time bad file name.
I have attached code below.
Anything I am doing wrong?
Thanks again
Code: Select all
Sub Sheet1_Button_click()
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 & "C:\Users\Jim\Desktop\Move file\Source\"
Let xDPath = xDesktop & "C:\Users\Jim\Desktop\Move file\Destination\"
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
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
No, no, no, this won't work. If your Windows login name is Jim, change
to
Code: Select all
Let xSPath = xDesktop & "C:\Users\Jim\Desktop\Move file\Source\"
Let xDPath = xDesktop & "C:\Users\Jim\Desktop\Move file\Destination\"
Code: Select all
Let xSPath = "C:\Users\Jim\Desktop\Move file\Source\"
Let xDPath = "C:\Users\Jim\Desktop\Move file\Destination\"
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
Sorry for confusion Hans
My windows login is not that
I have unique number which I don't want to disclose.
That's why I mentioned as Jim.
Actually I just copied the windows link and pasted in my actual code. I am pretty sure Windows login path is correct as I copied it.
Thanks
My windows login is not that
I have unique number which I don't want to disclose.
That's why I mentioned as Jim.
Actually I just copied the windows link and pasted in my actual code. I am pretty sure Windows login path is correct as I copied it.
Thanks
-
- Administrator
- Posts: 78536
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Transfer excel from one folder to other
Replace Jim with your login name. So for example if it is 12345, use
or use
but don't mix the two.
Code: Select all
Let xSPath = "C:\Users\12345\Desktop\Move file\Source\"
Let xDPath = "C:\Users\12345\Desktop\Move file\Destination\"
Code: Select all
Let xSPath = xDesktop & "\Move file\Source\"
Let xDPath = xDesktop & "\Move file\Destination\"
Best wishes,
Hans
Hans
-
- 2StarLounger
- Posts: 153
- Joined: 31 Jan 2021, 09:12
Re: Transfer excel from one folder to other
Yes i am using right path :)
I am not using jim instead i am using my login number. So i wont suspect in the path.
When i use right path like C:\Users\1234\Desktop\Move file\Source iam getting an error as
bad file name or number.
When I use path like “\Move file\Source\” only excel files inside that source folder is transferring and rest of the files not transferring.
Note:
My source folder have multiple subfolders example,
Source folder -> Folder 1 -> Folder 1a -> Folder 1b -> folder 1c-> Excel file located
Folder 2 -> Folder 2a -> Excel file located
Folder 3 -> Folder 3a -> Folder 3b -> Excel file 1 located
Folder 3aa -> Folder 3bb -> Folder 3cc-> Excel file 2 located
Folder 3bb -> Excel file 3 located
Folder 4 -> Folder 4a -> … -> … -> Excel file located
Folder 4b -> Excel file located
Etc
Sorry again.
I know this is driving you guys crazy.
Much appreciated guys
I am not using jim instead i am using my login number. So i wont suspect in the path.
When i use right path like C:\Users\1234\Desktop\Move file\Source iam getting an error as
bad file name or number.
When I use path like “\Move file\Source\” only excel files inside that source folder is transferring and rest of the files not transferring.
Note:
My source folder have multiple subfolders example,
Source folder -> Folder 1 -> Folder 1a -> Folder 1b -> folder 1c-> Excel file located
Folder 2 -> Folder 2a -> Excel file located
Folder 3 -> Folder 3a -> Folder 3b -> Excel file 1 located
Folder 3aa -> Folder 3bb -> Folder 3cc-> Excel file 2 located
Folder 3bb -> Excel file 3 located
Folder 4 -> Folder 4a -> … -> … -> Excel file located
Folder 4b -> Excel file located
Etc
Sorry again.
I know this is driving you guys crazy.
Much appreciated guys
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
It should be C:\Users\1234\Desktop\Move file\Source\jimpatel1993 wrote: ↑13 Oct 2021, 08:48When i use right path like C:\Users\1234\Desktop\Move file\Source iam getting an error as
( but not with 1234 - change 1234 to your user name ( or whatever number you are using) )
_.-_______________________________________________________________________________
I have uploaded a workbook. It has a macro for you to run.
Sub FindFolderMovefile()
(The macro may take a few minutes to run). It will tell us the paths you have
When I run that macro, Sub FindFolderMovefile() , I see this
Code: Select all
Option Explicit
Dim fld As Object
Dim fso As Object
Sub FindFolderMovefile()
Set fso = CreateObject(Class:="Scripting.FileSystemObject")
Set fld = fso.GetFolder("C:\Users\")
Call ProcessFolderFindMovefile(fld)
End Sub
Sub ProcessFolderFindMovefile(fld As Object)
If InStr(1, fld, "AppData", vbBinaryCompare) <> 0 Then Exit Sub
If InStr(1, fld, "Move file", vbBinaryCompare) <> 0 Then Let Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = fld
' If InStr(1, fld, "Source", vbBinaryCompare) <> 0 Then Let Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = fld: Exit Sub
' If InStr(1, fld, "Destination", vbBinaryCompare) <> 0 Then Let Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Value = fld: Exit Sub
Dim sfl As Object
On Error GoTo Bed
For Each sfl In fld.SubFolders
Call ProcessFolderFindMovefile(sfl)
Next sfl
Bed:
On Error GoTo -1
On Error GoTo 0
End Sub
Move file
on your computer ( or maybe the folder is protected somehow, but I am not sure about that )
Alan
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 13 Oct 2021, 10:16, edited 2 times in total.
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
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: Transfer excel from one folder to other
Yes correct. That is what you asked forjimpatel1993 wrote: ↑13 Oct 2021, 08:48When I use path like “\Move file\Source\” only excel files inside that source folder is transferring and rest of the files not transferring.
_._______________________________________________________________________jimpatel1993 wrote: ↑12 Oct 2021, 07:15Is there any way to search just excel files in the source folder and transfer those to destination?
In English language...
"only excel files inside that source" = "just excel files in the source"
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
Thanks once again
I have verified the path and its correct. I have attached for your reference. I have just changed my user name to 1234.
Still no luck.
I have verified the path and its correct. I have attached for your reference. I have just changed my user name to 1234.
Still no luck.
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
I think you have uploaded the wrong file? There is only my paths shown?
We need to see the results it gives you
We need to see the results it gives you
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