Transfer excel from one folder to other

[HansV]

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.

[StuartR]

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\

[Doc.AElstein]

My desktop is at this place
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

[Doc.AElstein]

I can get Hans macros to work, like this......

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

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

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

[jimpatel1993]

Wow
Fantastic guys
Thanks everyone for your input.
I love this forum due to this.
Again much appreciated guys

[HansV]

So what was the problem?

[jimpatel1993]

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

[HansV]

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

[jimpatel1993]

Thanks for your reply.
Unfortunately I am getting run time error 76

Thanks a lot Hans

[Doc.AElstein]

What does this do

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

[HansV]

That means the path was not found. You'll have to change xSPath and xDPath to the actual paths on your computer.

[jimpatel1993]

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

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

[HansV]

No, no, no, this won't work. If your Windows login name is Jim, change

Code: Select all

 Let xSPath = xDesktop & "C:\Users\Jim\Desktop\Move file\Source\"
 Let xDPath = xDesktop & "C:\Users\Jim\Desktop\Move file\Destination\"

to

Code: Select all

 Let xSPath = "C:\Users\Jim\Desktop\Move file\Source\"
 Let xDPath = "C:\Users\Jim\Desktop\Move file\Destination\"

[jimpatel1993]

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

[HansV]

Replace Jim with your login name. So for example if it is 12345, use

Code: Select all

 Let xSPath = "C:\Users\12345\Desktop\Move file\Source\"
 Let xDPath = "C:\Users\12345\Desktop\Move file\Destination\"

or use

Code: Select all

 Let xSPath = xDesktop & "\Move file\Source\"
 Let xDPath = xDesktop & "\Move file\Destination\"

but don't mix the two.

[jimpatel1993]

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

[Doc.AElstein]

When i use right path like C:\Users\1234\Desktop\Move file\Source iam getting an error as

It should be C:\Users\1234\Desktop\Move file\Source\

( 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

If the macro gives you no results then that probably means that you do not have a folder with the name
Move file
on your computer ( or maybe the folder is protected somehow, but I am not sure about that )




Alan

[Doc.AElstein]

When I use path like “\Move file\Source\” only excel files inside that source folder is transferring and rest of the files not transferring.

Yes correct. That is what you asked for

Is 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"

[jimpatel1993]

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.

[Doc.AElstein]

I think you have uploaded the wrong file? There is only my paths shown?
We need to see the results it gives you