How to move all files from subfolders to one folder by VBA code ?

Experts,

I have one Folders and it has some sub folders and Subfolder has files like excel , word etc... I would like to have one small program when i set source and destination folder and run the script to get all only excel files to Destination folder.

Please let me know if you need more clarification..

Thanks,

This works.

BrowseFolder is from http://www.robvanderwoude.com/wshexamples.php

Option Explicit

Dim sSrcDir, sTgtDir
Dim objFSO

sSrcDir = BrowseFolder( "", False )
sTgtDir = BrowseFolder( sSrcDir, False )

Set objFSO = CreateObject("Scripting.FileSystemObject")
objFSO.MoveFile sSrcDir & "\*.xls" , sTgtDir

' --------------------------------------------
' FUNCTIONS BELOW

Function BrowseFolder( myStartLocation, blnSimpleDialog )
' This function generates a Browse Folder dialog
' and returns the selected folder as a string.
'
' Arguments:
' myStartLocation   [string]  start folder for dialog, or "My Computer", or
'                             empty string to open in "Desktop\My Documents"
' blnSimpleDialog   [boolean] if False, an additional text field will be
'                             displayed where the folder can be selected
'                             by typing the fully qualified path
'
' Returns:          [string]  the fully qualified path to the selected folder
'
' Based on the Hey Scripting Guys article
' "How Can I Show Users a Dialog Box That Only Lets Them Select Folders?"
' http://www.microsoft.com/technet/scriptcenter/resources/qanda/jun05/hey0617.mspx
'
' Function written by Rob van der Woude
' http://www.robvanderwoude.com
	Const MY_COMPUTER   = &H11&
	Const WINDOW_HANDLE = 0 ' Must ALWAYS be 0

	Dim numOptions, objFolder, objFolderItem
	Dim objPath, objShell, strPath, strPrompt

	' Set the options for the dialog window
	strPrompt = "Select a folder:"
	If blnSimpleDialog = True Then
		numOptions = 0      ' Simple dialog
	Else
		numOptions = &H10&  ' Additional text field to type folder path
	End If
	
	' Create a Windows Shell object
	Set objShell = CreateObject( "Shell.Application" )

	' If specified, convert "My Computer" to a valid
	' path for the Windows Shell's BrowseFolder method
	If UCase( myStartLocation ) = "MY COMPUTER" Then
		Set objFolder = objShell.Namespace( MY_COMPUTER )
		Set objFolderItem = objFolder.Self
		strPath = objFolderItem.Path
	Else
		strPath = myStartLocation
	End If

	Set objFolder = objShell.BrowseForFolder( WINDOW_HANDLE, strPrompt, _
	                                          numOptions, strPath )

	' Quit if no folder was selected
	If objFolder Is Nothing Then
    	BrowseFolder = ""
    	Exit Function
	End If

	' Retrieve the path of the selected folder
	Set objFolderItem = objFolder.Self
	objPath = objFolderItem.Path

	' Return the path of the selected folder
	BrowseFolder = objPath
End Function

Open in new window

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 8

TemodyPickalbatros, IT ManagerCommented: 2015-10-07

you can use this command
also you can save it as a batch file to run in scheduled time

SET MoveDirSource=c:\source
SET MoveDirDestination=d:\destination
ROBOCOPY "%MoveDirSource%" "%MoveDirDestination%" /MOVE /E

LVL 27

NVITEnd-user supportCommented: 2015-10-07

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

sorry i don't see (only excel files)
please try this

SET MoveDirSource=c:\source
SET MoveDirDestination=d:\destnation
ROBOCOPY *.xls* "%MoveDirSource%" "%MoveDirDestination%" /MOVE /E

sharepoint0520Author Commented: 2015-10-07

Hi,

Can you please write codes in Module or any Excel form ? Is it possible to attach Excel file for you guys ? I am not clear to use Function.

sharepoint0520Author Commented: 2015-10-08

NVIT,

How to use this function in Module ? I would like to Button to click to move files. Any advice?

sharepoint0520Author Commented: 2015-10-19

I got it.

How to move all files from subfolders to one folder by VBA code ?

Who is Participating?