+ Reply to Thread
Results 1 to 3 of 3

macro to copy excel files only from main directory and sub directory to a specific folder

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,142

    macro to copy excel files only from main directory and sub directory to a specific folder

    I have a folder on c:\\backup2018.

    In this folder there are folders as e,g folder1, folder 2 and so on but however there may be subfolders folder1 or any any folders on c:\\backup2018..

    In all folders there are pdf ,zip files, excel files xls, xlsm, xlsx, xlsb, text files, word files.

    Looking a macro to copy all excel files xls, xlsm, xlsx, xlsb, of c:\\backup2018\folder 1 , folder2 with their subfolders to C:\Users\user\documents \excelfilesbackup2018

  2. #2
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,142

    Re: macro to copy excel files only from main directory and sub directory to a specific fo

    I have found a code while googling but works only in source folder only . It does not look in sub folders.

    c:\\test\\folder\folder1

    c:\\test\\folder\
    c:\\test\\folder\folder2/folder3

    Can any one assist to modify for code to search for all excel extensions xl*

    Sub CopyExcelFilesOnly()
    Dim MyFSO As FileSystemObject
    Dim MyFile As File
    Dim SourceFolder As String
    Dim DestinationFolder As String
    Dim MyFolder As Folder
    Dim MySubFolder As Folder
    
    SourceFolder = "C:\test"
    DestinationFolder = "C:\all"
    
    Set MyFSO = New Scripting.FileSystemObject
    Set MyFolder = MyFSO.GetFolder(SourceFolder)
    
    For Each MyFile In MyFolder.Files
        If MyFSO.GetExtensionName(MyFile) = "xlsx" Then
            MyFSO.CopyFile Source:=MyFSO.GetFile(MyFile), _
            Destination:=DestinationFolder & "\" & MyFile.Name, Overwritefiles:=False
        End If
    Next MyFile
    
    End Sub

  3. #3
    Forum Guru karedog's Avatar
    Join Date
    10-03-2014
    Location
    Indonesia
    MS-Off Ver
    2003
    Posts
    2,971

    Re: macro to copy excel files only from main directory and sub directory to a specific fo

    Maybe :
    Private Function CopyExcelFiles(SrcDir As String, TgtDir As String) As Long
      If Right$(SrcDir, 1) <> "\" Then SrcDir = SrcDir & "\"
      If Right$(TgtDir, 1) <> "\" Then TgtDir = TgtDir & "\"
      If Len(Dir(SrcDir, vbDirectory)) = 0 Then CopyExcelFiles = 0: Exit Function
      CopyExcelFiles = Shell("cmd /c xcopy """ & SrcDir & "*.xls*"" """ & TgtDir & """ /S /Y", vbHide)
    End Function
    Sub Test()
      If CopyExcelFiles("C:\backup2018", "C:\Users\user\documents\excelfilesbackup2018") > 0 Then Debug.Print "Success copying files" Else Debug.Print "Fail copying files"
    End Sub
    1. I care dog
    2. I am a loop maniac
    3. Forum rules link : Click here
    3.33. Don't forget to mark the thread as solved, this is important

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Folder directory index excel macro
    By andre1986 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-18-2015, 01:29 PM
  2. [SOLVED] Copy specific files from one directory to another
    By brazil in forum Excel General
    Replies: 2
    Last Post: 03-06-2013, 03:56 PM
  3. Macro to Move files from one directory to other directory
    By Shanthuday in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2013, 07:21 AM
  4. Replies: 0
    Last Post: 11-26-2012, 05:51 PM
  5. Reading in the files in a directory into an array, directory chosen by user
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-18-2011, 12:52 PM
  6. Copy selected files into a specific directory
    By Wizz in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-29-2009, 10:50 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1