selecting a folder and copying all files into another folder

[Holley]

Hello everyone! Hope someone may be able to assist with a project I am working on. I would like to be able to run a macro that will allow me to select a folder and have all the files (they will be .csv) copied into a another folder. The only static address will be the "to" folder. The "from" folder will change each time it is needed.

For example, I would like to move all files in H:\holley\Store\09-05-24\ to H:\holley\Store\Run. It seems like it should be simple, but I'm getting stuck on seleting the initial folder. Any suggestions would be most appreciated! Thank you in advance!

 

[Trevor G]

Can you clarify the following. Your thread title is copy files and your second paragraph states move! Which one do you want to do?

This should move from one folder to another (not tested written on my tablet)

Sub MoveAllFiles()
Dim MyFile As String
MyFile = Dir("H:\holley\Store\09-05-24\*.csv")
Do Until MyFile = ""
Name "H:\holley\Store\09-05-24\" & MyFile As "H:\holley\Store\Run\" & MyFile
MyFile = Dir
Loop
End Sub

 

[Holley]

Can you clarify the following. Your thread title is copy files and your second paragraph states move! Which one do you want to do?

This should move from one folder to another (not tested written on my tablet)

Sub MoveAllFiles()
Dim MyFile As String
MyFile = Dir("H:\holley\Store\09-05-24\*.csv")
Do Until MyFile = ""
Name "H:\holley\Store\09-05-24\" & MyFile As "H:\holley\Store\Run\" & MyFile
MyFile = Dir
Loop
End Sub

Sorry, I want to copy them. The folder containing the original files will change each time, I'm hoping to be able to select the folder since it will not be constant. The best outcome would be if I could select the subfolder under H:\holley\Store (each date has its own folder).

 

[Trevor G]

Hi Holley the following will copy all CSV files from folder to another. I haven't added in the Show Dialog box. This can be revisited tomorrow if no one else adds in this option but it will get you going.

Sub CopyFiles()
'Copy CSV files from One folder to another
    Dim FSO As Object
    Dim FromFolder As String
    Dim ToFolder As String
    Dim FileExt As String

    FromFolder = "C:\Users\Trev\Documents\CSV Files"  'Change to your folder for now
    ToFolder = "C:\Users\Trev\Documents\Copy To Folder"    'Change to H:\holley\Store\Run

    FileExt = "*.csv"  'Only CSV Files
    ' If needed you can use *.* for all files or *.xls* for Excel workbooks as an example

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

    Set FSO = CreateObject("scripting.filesystemobject")

    If FSO.FolderExists(FromFolder) = False Then
        MsgBox FromFolder & " This folder doesn't exist"
        Exit Sub
    End If

    If FSO.FolderExists(ToFolder) = False Then
        MsgBox ToFolder & " Check the destination folder name as it doesn't exist"
        Exit Sub
    End If

    FSO.CopyFile Source:=FromFolder & FileExt, Destination:=ToFolder
    MsgBox "CSV Files successfully copied", vbInformation, "Holley copy files"

End Sub

 

[Trevor G]

You will have to tweak this code but I hope it helps once you give it ago.

Option Explicit

Sub Copy_Folder()

    Dim fso As Object
    Dim targetFolder As Object

    Dim fromPath As String
    Dim intialPath As String
    Dim toPath As String

    ' Initialize the file system object
    Set fso = CreateObject("Scripting.FileSystemObject")

    fromPath = "C:\Users\Trev\Documents\CSV Files" 'Change this to the folder to select from
    intialPath = "C:\Users\Trev\Documents" 'Initial Folder Location

    ' Ask user for the destination folder
    Set targetFolder = Application.FileDialog(msoFileDialogFolderPicker)

    ' Define the target folder dialog box properties and result
    With targetFolder
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = intialPath
        If .Show <> -1 Then
            MsgBox "You didn't select anything"
            Exit Sub
        End If
        toPath = .SelectedItems(1)
    End With

    If Right(fromPath, 1) = "\" Then
        fromPath = Left(fromPath, Len(fromPath) - 1)
    End If

    If Right(toPath, 1) = "\" Then
        toPath = Left(toPath, Len(toPath) - 1)
    End If


    If fso.FolderExists(fromPath) = False Then
        MsgBox fromPath & " doesn't exist"
        Exit Sub
    End If

    fso.CopyFolder Source:=fromPath, Destination:=toPath

    MsgBox "You can find the files and subfolders from " & fromPath & " in " & toPath

End Sub

 

[Holley]

You will have to tweak this code but I hope it helps once you give it ago.

Option Explicit

Sub Copy_Folder()

    Dim fso As Object
    Dim targetFolder As Object

    Dim fromPath As String
    Dim intialPath As String
    Dim toPath As String

    ' Initialize the file system object
    Set fso = CreateObject("Scripting.FileSystemObject")

    fromPath = "C:\Users\Trev\Documents\CSV Files" 'Change this to the folder to select from
    intialPath = "C:\Users\Trev\Documents" 'Initial Folder Location

    ' Ask user for the destination folder
    Set targetFolder = Application.FileDialog(msoFileDialogFolderPicker)

    ' Define the target folder dialog box properties and result
    With targetFolder
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = intialPath
        If .Show <> -1 Then
            MsgBox "You didn't select anything"
            Exit Sub
        End If
        toPath = .SelectedItems(1)
    End With

    If Right(fromPath, 1) = "\" Then
        fromPath = Left(fromPath, Len(fromPath) - 1)
    End If

    If Right(toPath, 1) = "\" Then
        toPath = Left(toPath, Len(toPath) - 1)
    End If


    If fso.FolderExists(fromPath) = False Then
        MsgBox fromPath & " doesn't exist"
        Exit Sub
    End If

    fso.CopyFolder Source:=fromPath, Destination:=toPath

    MsgBox "You can find the files and subfolders from " & fromPath & " in " & toPath

End Sub

Thanks! Trying it now!!

 

[Holley]

Almost perfect! Is there a way to have the folders direct to H:\holley\Store and then be able to chose which subfolder I want to select the files in? Seems that it defaults to the last folder I used. Maybe asking for stars and moon too, but I would like for all the files to be copied within the file. Is there a way to do this thru vba without having to select each file when the 'picker' opens?

 

[Trevor G]

Have you looked to adjust the codecas it should be doing what you want with a slight adjustment to the folder location as you've mentioned. You also don't need to select the files as it is all files in the folder you select that is copied. Try selecting the folder and nothing else even if you see the list of files within the folder.

 

[Holley]

Have you looked to adjust the codecas it should be doing what you want with a slight adjustment to the folder location as you've mentioned. You also don't need to select the files as it is all files in the folder you select that is copied. Try selecting the folder and nothing else even if you see the list of files within the folder.

It asks me to open "my documents', not the H:\holley\Store folder... I'm not seeing the destination folder either. I must be overlooking-

 

[Sergius]

Replace it

initialPath = "C:\Users\Trev\Documents"

on

initialPath = "H:\holley\Store folder"