Modify the code to bring and collect all excel files from a specific partition

[Hany ali]

Hello my Masters
I Want your Helping in Modify this Code ,to Collect and merge all Excel Files from Certain Partition to Certain Folder , but I don't Know The Reason in This code does not work as well
Because I Want All Excel Files brought in this Folder Name "Test100"
thanks alot

Sub copy_specific_files_in_folder()
Dim FSO As Object
Dim sourcePath As String
Dim destinationPath As String
Dim fileExtn As String
sourcePath = "D:\EXC.Dep"
destinationPath = "C:\Users\Hany.Ali\Downloads\Test100\"
fileExtn = " * .xl * "
If Right(sourcePath, 1) <> " \ " Then
sourcePath = sourcePath & " \ "
End If
Set FSO = CreateObject("scripting.filesystemobject")
If FSO.FolderExists(sourcePath) = False Then
MsgBox destinationPath & " does not exist"
Exit Sub
End If
If FSO.FolderExists(destinationPath) = False Then
MsgBox sourcePath & " does not exist"
Exit Sub
End If
FSO.CopyFile Source:=sourcePath & fileExtn, Destination:=destinationPath
MsgBox "Your files have been copied from " & sourcePath & " to " & destinationPath
End Sub

 

[jolivanes]

fileExtn = " * .xl * "
If Right(sourcePath, 1) <> " \ " Then
sourcePath = sourcePath & " \ "

Any reason why you have spaces in here?
I have not gone through your code because I don't do scripting filesystemobjects but the spaces stood out.
Try it by deleting the spaces that should not be there.

 

[Hany ali]

thanks but not this Reason

 

[Chihiro]

Looks like that's pretty much same code as Ron's code in link below "Sub Copy_Certain_Files_In_Folder()"
https://www.rondebruin.nl/win/s3/win026.htm

Copy his code and change FromPath and ToPath as needed.

Unless you have folder permission setting issue, code will work.

If this isn't the solution that you are looking for. You'll need to give us more detail of what you want to accomplish and what exactly the code fails to do.

 

[Hany ali]

thanks alot ,but this one "Sub Copy_Certain_Files_In_Folder()" ,the same result by mistake it didn't brought any Files
but this one work as well with me ,Creat Separate NEW Folder

Sub Move_Certain_Files_To_New_Folder()
'This example move all Excel files from FromPath to ToPath.
'Note: It will create the folder ToPath for you with a date-time stamp
    Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String
    Dim FNames As String

    FromPath = "C:\Users\Ron\Data"  '<< Change
    ToPath = "C:\Users\Ron\" & Format(Now, "yyyy-mm-dd h-mm-ss") _
           & " Excel Files" & "\"    '<< Change only the destination folder

    FileExt = "*.xl*"   '<< Change
    'You can use *.* for all files or *.doc for word files

    If Right(FromPath, 1) <> "\" Then
        FromPath = FromPath & "\"
    End If

    FNames = Dir(FromPath & FileExt)
    If Len(FNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    FSO.CreateFolder (ToPath)

    FSO.MoveFile Source:=FromPath & FileExt, Destination:=ToPath
    MsgBox "You can find the files from " & FromPath & " in " & ToPath

End Sub

 

[Chihiro]

Create new folder worked? If I understood your post right... you probably didn't have destination folder and/or have appropriate permission. So, files didn't move over using the code. But newly created folder has no folder security set and files got moved...

 

[Hany ali]

I had exactly wanted to create a new folder through me in advance and transfer all relevant excel files from a specific Partition to this folder